SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-292
Considerations for MODIFY
•
All specified locations must belong to the table, index or sequence generator. If the
pattern form of rename-spec is used, the pattern must match at least one of the
partitions of the table, index or sequence generator.
•
The target Guardian locations must not exist.
•
For all partitions of the target object, file labels must be available.
•
For a Guardian rename of an index, file labels for all partitions of base table of the
index must be available.
MODIFY and Indexes
If there are no indexes on a table, the reuse form of MODIFY purges data from the
partition. If there are existing indexes, MODIFY performs a DELETE operation to
remove the index data, which can take some time to complete.
MODIFY and TMF
Many partition management requests require movement of massive amounts of data.
Because these operations might take longer than the set TMF time limit whose default
is two hours, operations involving data movement are performed in multiple
transactions.
Specifying the Number of Rows per Transaction
To specify the number of rows to be copied in a transaction, use the CONTROL
QUERY DEFAULT statement or insert an entry to 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. You can issue the statement 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 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 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.