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-42
Using FASTCOPY to Copy Tables into Tables
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 about the FASTCOPY utility, 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
operations. The latter approach is only viable when the source table contents are
not updated in between operations.
Note that if you want to copy the indexes explicitly, you can also start the explicit
fastcopy operation with the FASTCOPY INDEX command (not necessarily with the
FASTCOPY TABLE command) followed by the required number of additional
FASTCOPY INDEX commands and one FASTCOPY TABLE...INDEXES EXPLICIT
command, in any order.
•
There is an administrative overhead associated with using the FASTCOPY
command as compared to the INSERT...SELECT command. For large tables, the
overhead is negligible. However, for tables containing less data, the overhead
might be significant.
•
The FASTCOPY command invokes insert triggers that have the target table as the
subject table; this has a negative performance impact. Therefore, to prevent such
triggered actions for the target table, use the ALTER TRIGGER DISABLE
command before the fastcopy operation is initiated. After the fastcopy operation is