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

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—544536-007
10-41
Examples of Using DUP to Copy Tables
DUP operation fails before the data is successfully copied, specify RECOVER with
the CANCEL option to roll back the DUP operation. You can access this status
information by reading the DDL_LOCK metadata table. If you specify the wrong
RECOVER operation, RECOVER reports this so that you can use the correct
operation, possibly with RECOVER...CANCEL. For more information about the
RECOVER command, see the SQL/MX Reference Manual.
•
During the DUP operation, the target table is marked as corrupt to prevent another
process from viewing the data until the operation completes successfully.
•
All utility operations have the potential to run for hours, especially those that
involve a great deal of data movement. To manage systems effectively, you need
to know how far the operation has proceeded and how much longer it needs to run.
Utilities provide reports that indicate what step is in progress. Utility operations
periodically place operation progress reports in the metadata tables through the
DDL lock mechanism. You can examine metadata to get the latest information. The
DUP operation has the option to log these progress reports to an OSS text file.
See Section 8, Querying SQL/MX Metadata for instructions on how to access
information from the DDL_LOCKS table.
For more information about DDL lock considerations for the DUP utility, see the
SQL/MX Reference Manual.
Examples of Using DUP to Copy Tables
This example copies the partitions of the source table (using a different catalog and
schema) to the same locations:
DUP mycat.myschema.mytable1 TO mycat1.myschema1.*;
This example copies the partitions of the source table on $data1 and $data2 to the
partitions of the target table on $data2 and $data3 respectively. If there is no PART
clause for a specific volume and source partitions exist on that volume, the target
partitions are created on the same volume as the source partitions.
DUP mycat.myschema1.mytable TO *.myschema2.*
LOCATION (PART $data1 TO $data2, PART $data2 TO $data3);
This example copies the partitions of the source table to the same locations. The target
table, if it exists, is dropped, and a new one is created:
DUP mycat1.myschema.mytable TO mycat2.*.*,TARGET PURGE;
Note. RENAME function is not supported using DUP in SQL/MX.