SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Utilities
HP NonStop SQL/MX Reference Manual—544517-008
5-85
Considerations for MODIFY
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.
MODIFY and Table Reloading
Some of MODIFY’s options start a FUP RELOAD process that runs in the background.
Until this process completes, you cannot do DDL or utility operations on the file. You
can monitor the reload process’s progress with this command:
FUP STATUS physical-file-name
If FUP STATUS returns a RELOAD COMPLETED message and the physical file is not
being opened by another process, you can start the next MODIFY operation. Note that