SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

• 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 “Querying SQL/MX Metadata” (page 104)for instructions on how to access
information from the DDL_LOCKS table.
For more information, 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;
Using FASTCOPY to Copy Tables into Tables
FASTCOPY is a syntax-based utility that can be executed from MXCI and from a program using
dynamic SQL. It enables you to copy a source table and its indexes into an existing equivalent
target table and indexes. A fastcopy operation includes the execution of one or more FASTCOPY
commands.
For more information, see the SQL/MX Reference Manual.
Guidelines for Using FASTCOPY
• The FASTCOPY command is useful for copying rows when the source and target tables have
different but equivalent layouts. For example, if all of the following are different: column names,
column data types (but compatible), and partitions. The INSERT...SELECT command can also
be used when the source and target tables have different layouts. The FASTCOPY command
has the following advantages:
◦ It does not generate audit for the target table and its indexes. Copying a large table
could, otherwise, generate huge amount of audit.
◦ It is faster than the INSERT...SELECT command, when the operation involves indexes.
• The FASTCOPY command enables a table and its indexes to be copied in individual operations;
there is no restriction on the sequence of those operations; they can be executed concurrently
or serially.
For example, if a table has indexes, issue the FASTCOPY TABLE...INDEXES EXPLICIT command
to copy the rows for the table, and in concurrent individual sessions, issue a FASTCOPY INDEX
for each of the indexes. You can also issue the FASTCOPY TABLE...INDEXES EXPLICIT command
and let it run to completion, wait for a convenient time, and then issue the FASTCOPY INDEX
Using FASTCOPY to Copy Tables into Tables 205