SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Utilities
HP NonStop SQL/MX Reference Manual—540440-003
5-9
Considerations for DUP
To serialize these utility operations, NonStop SQL/MX has the concept of a
DDL lock. This is a lock that prevents database structure changes from
occurring while a utility request is executing. A utility request informs SQL that
it is running, perform commands in as many transactions as necessary, then
informs SQL that the operation has completed. While the utility request is
running, no conflicting DDL or utility operation can occur. That is, you can
make no database structural change that would affect the utility.
DDL lock information is persistent across transaction boundaries. If the utility
operation fails unexpectedly such as a process failure, you must run the
RECOVER utility to remove the lock and clean up the operation. When you run
the RECOVER utility, DDL lock information is retrieved and the correct clean
up operation is performed. If you run the RECOVER operation with the
incorrect option, RECOVER displays an error message so you can rerun it with
the correct option.
While the operation is proceeding, you can select state information from
metadata tables to determine the utility progress. If the operation terminates
unexpectedly, you can also select this information to determine where the
operation failed.
•
DUP records operation progress steps in the DDL_LOCKS metadata table. You
can query this table to determine the DUP operation’s progress:
•
An error is returned if a user transaction exists.
•
An error is returned if a DUP operation is attempted on an SQL/MX metadata table
(histogram, system defaults, or MXCS tables).
DUP
Operation
Step Step Progress Status
Step 1 DDL lock has been created.
Step 2 Target table has been created.
Step 3 Source table is open.
Step 4 All source objects are open.
Step 5 Target table is open.
Step 6 All target objects are open.
Step 7 All table partitions are copied.
Step 8 All index partitions are copied.
Step 9 All objects for catalog.schema.table have been copied.*
Step 10 Target object is now available (corrupt attribute is turned off, audit attribute
is turned on).
Step 11 DDL lock is removed.
*
Any process, CPU, or system failure that occurs before this point causes the operation to be rolled back.
Any failure after this point can be resumed.