SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-6
Other Operational Considerations
Because the locks used in Step 1 and Step 3 of the operation are exclusive, they have
no special priority over other locks that can also be issued on the objects. Therefore, to
enable the exclusive locks required by these functions, you might need to manage the
application activity as follows:
1. During Step 1, do not compile programs that would require the catalogs involved
for update or that refer to the affected objects.
2. During Step 2, you can resume the activity.
3. During Step 3, quiesce the application transaction activity so that locks are not in
contention.
These situations can arise during the operation of long-running DDL functions:
•
For large tables, audit trail space can be exceeded during the course of the
operation, resulting in termination of the operation and backout by the TMF
subsystem. This condition is minimized if you allow SQL/MP to manage TMF
transactions. HP recommends that you do not initiate a user-defined TMF
transaction for long-running DDL and utility operations.
•
If the operation cannot acquire the exclusive lock when required, SQL/MP
terminates the operation abnormally after a predetermined period of time.
Remember that the operation requires the simultaneous availability of all file labels
that must be changed in Step 3 of the operation, as described previously. The lock
timeout value is currently 60 seconds and cannot be changed.
In a similar way, certain other statements or commands present concurrency issues
that can affect the result of the operation. When you are duplicating, backing up, or
moving data from one object to another, these functions do not require sustained
exclusive access to the source objects; the only exclusive access involved is similar to
that required in Step 3 at the end of the function. If you are duplicating a table,
however, and you want the target table to contain consistent data, you must consider
the implications of concurrency.
You cannot achieve a consistent target table if you refer, in a DUP command, to a
source table that is being updated while the duplication is in progress. In such cases,
you need to consider stopping transaction activity on the affected tables during the
data movement. SQL/MP does not enforce this condition by using locks. So, if you do
not procedurally enforce a stable source object, the data in the target object might be
inconsistent.