SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—523723-004
10-11
MODIFY and Table Reloading
Specifying the Number of Rows Per Transaction
You can specify the number of rows to be copied in a transaction with the CONTROL
QUERY DEFAULT statement or by inserting an entry into the SYSTEM_DEFAULTS
table. For offline partition operations, use the
PM_OFFLINE_TRANSACTION_GRANULARITY attribute. For online partition
operations, use the PM_ONLINE_TRANSACTION_GRANULARITY attribute.
The setting in the SYSTEM_DEFAULTS table applies to all partition operations in the
current node unless you override it by using a CONTROL QUERY DEFAULT
statement. The statement can be issued from MXCI, and the setting from this
statement applies only to subsequent requests within the same MXCI.
If the attribute neither appears in the SYSTEM_DEFAULTS table nor is specified using
a CONTROL QUERY DEFAULT statement, MODIFY uses the value 5000 for offline
partition operations and 400 for online partition operations.
Default Value for Offline Partition Operations
For offline partition operations, MODIFY locks the entire source and target partitions so
DP2 lock escalation is not an issue. In general, MODIFY runs more efficiently when
you specify a larger value. You should choose this value with care. If it is too large, the
transaction might abort because of the two-hour TMF time limit or the size of the audit
trails. You will also need to take the row size into consideration when choosing the
number of rows because the product of the row size and num-of-rows gives the
amount of data to be copied in each transaction.
You can temporarily increase the TMF time limit and the size of the audit trail to allow
operations to complete with a larger num-of-rows. However, increasing these TMF
limits degrades system performance and increases disk space usage for the audit trail.
Default Value for Online Partition Operations
For online partition operations, MODIFY does not lock source partitions or target
partitions from the start. Source rows are read without locks. Row locks are obtained
on target rows. Avoid choosing a default value greater than 500, because DP2
escalates locking from selected rows to the entire partition if the partition has more
than 511 row and file locks.
MODIFY and Table Reloading
Some MODIFY command options start the online reload process ORSERV, which runs
in the background. Until the ORSERV process completes, you cannot perform DDL
operations and utility operations—including another MODIFY command—on the
affected table or index. ORSERV keeps the file open and does not terminate for five
minutes after the reload operation completes.
For range-partitioned tables and indexes, the MODIFY command starts one or more
ORSERV processes when: