SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Enhancing SQLMX Database Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
16-6
Other Operational Considerations
You can also get this SQL/MX error message:
For detailed information about this and other SQL/MX error messages, see the
SQL/MX Messages Manual.
Other Operational Considerations
In general, SQL/MX utility operations follow a three-step process:
1. Lock the metadata for the object being acted upon. Also acquire a DDL lock to
prevent metadata or label changes from occurring until the utility operation
completes. The DDL lock encompasses several transactions and persists until the
utility operation is complete.
2. Perform the utility operation. This step encompasses many transactions.
3. Update metadata to reflect changes caused by the utility operation.
4. Remove the DDL lock.
Because the locks used in Step 1 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 require access to the metadata
involved for update or that refer to affected objects.
2. During Step 2, you can resume application transaction activity.
3. During Step 3, quiesce application transaction activity so that locks are not in
contention.
These situations can arise during the operation of long-running utility 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 NonStop SQL/MX to manage
TMF transactions.
•
If the operation cannot acquire the exclusive lock when required, NonStop SQL/MX
terminates the operation abnormally after a predetermined period of time.
Remember that the operation requires the simultaneous availability of all file labels
1134 A concurrent utility or DDL is being performed on object
object-name, its parent, or one of its dependencies. That
operation must complete before the requested operation can
run.
Note. HP recommends that you do not initiate a user-defined TMF transaction for
long-running DDL operations. Utility operations are not supported in user-defined TMF
transactions.