SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

partitions that are not being used by other applications or that are being used with READ access.
WRITE access is prohibited.
MODIFY’s with-shared-access option supports online partition management of range-partitioned
tables and indexes in which the partitioning key is a prefix of the clustering key. This feature
provides full read and write access to a range-partitioned table or index for the duration of most
operations. Other processes can read and write the table or index while it is being repartitioned,
except during a short period at the end when file labels and metadata are updated.
Online partition management is not supported for these types of tables and indexes:
• hash-partitioned tables and indexes
• System-clustered tables
• range-partitioned tables and indexes where the partitioning key is not a prefix of the clustering
key
MODIFY and TMF
Many partition management requests require movement of massive amounts of data. Because these
operations can take longer than the default TMF time limit of two hours, operations involving data
movement are performed in multiple transactions.
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.
Using MODIFY to Manage Table and Index Partitions 183