SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Utilities
HP NonStop SQL/MX Reference Manual—523725-004
5-56
Considerations for MODIFY
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.
You can temporarily increase the TMF time limit and the size of the audit trail to allow
the operations to complete with a larger num-of-rows. However, increasing TMF
limits degrades system performance and increases disk space usage for the audit trail.
Default Value for Online Partition Operations
For online partition operations, avoid choosing a 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.
Concurrency and Timeout Considerations
When you use MODIFY, avoid long-running concurrent transactions on the same
object. Concurrency issues arise in two phases: during the data movement phase and
during the commit phase.
During the data movement phase, if MODIFY is writing to an existing partition,
MODIFY obtains row locks on data as it is written. If a concurrent application is also
writing to the same partition, contention can occur. Either MODIFY or the application
might experience timeouts if they each seek to access a row the other has locked. This
situation is especially true if the application holds so many locks that DP2 attempts to
escalate to a file lock or if the application transaction is long-running. If MODIFY times
out, the command is terminated.
During the commit phase, MODIFY attempts to obtain exclusive locks on all partitions
to update file labels. Again, if concurrent applications hold locks for long durations,
MODIFY times out in its attempt, and the MODIFY command fails.