SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

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 complete, you can re-enable the triggers using the
ALTER TRIGGER ENABLE command.
The FASTCOPY command checks for the RI constraints that are defined on the target table. RI
constraint checking, like trigger actions, also has a negative performance impact. To remove
the RI and other droppable constraints that would interfere with a FASTCOPY command, use
the ALTER TABLE...DROP CONSTRAINT command. After the fastcopy operation is complete,
you can re-create such constraints.
Please note that constraint recreation may itself be a long-running operation because the
constraint condition must be verified against the data in the involved table(s).
• The FASTCOPY command requires source and target tables to be equivalent.
• If the INDEXES EXPLICIT option is specified with the FASTCOPY command, you must specify
the matching source and target indexes on the related FASTCOPY INDEX commands.
If the INDEXES IMPLICIT option is specified (or defaulted to) with the FASTCOPY command,
the system selects a matching source index for each involved target index.
• The FASTCOPY command requires select privileges on the source table, and select, insert,
update, and delete privileges on the target table.
• Use the RECOVER command to explicitly resume or cancel a failed fastcopy operation.
To recover a failed FASTCOPY TABLE...INDEXES EXPLICIT operation, use the RECOVER INDEX
command. To recover a failed FASTCOPY TABLE...INDEXES IMPLICIT operation, use the
RECOVER TABLE command.
If the CANCEL option is specified, the fastcopy operation is canceled. Any actively executing
FASTCOPY commands associated with the affected fastcopy operation are terminated.
If the RESUME option is specified, it affects only that target table, mentioned in the RECOVER
command.
Examples of Using FASTCOPY to Copy Tables
Consider a source table ST with indexes SI1 and SI2 and a target table TT with index TI1.
The following command copies all the rows from the source table to the target table and
automatically maintains index TI1 as part of that copy process.
FASTCOPY TABLE ST TO TT INDEXES IMPLICIT;
The following command copies all the rows from the source table to the target table. An explicit
FASTCOPY of source index SI1 to target index TI1 is required to complete the fastcopy operation.
Because the source index SI2 has no equivalent target index, it does not participate in the fastcopy
operation.
FASTCOPY TABLE ST TO TT INDEXES EXPLICIT;
The following command copies all the rows from source index SI1 to the target index TI1.
FASTCOPY INDEX SI1 to TI1;
The base table TT can be accessed only after completing the fastcopy operation (copying of indexes
SI1 to TI1). If you try to access the table without completing the fastcopy operation (by copying
the index explicitly), it returns the following error:
Using FASTCOPY to Copy Tables into Tables 209