DB2 ADMIN_MOVE_TABLE
The ADMIN_MOVE_TABLE stored procedure moves the data in an active table into a new table object with the same name, while the data remains online and available for access.
This stored procedure creates a protocol table composed of rows that contain status information and configuration options related to the table to be moved.
The return set from this procedure is the set of rows from the protocol table related to the table to be moved.
This stored procedure uses the following terminology:
- Source table :
The original table name that is passed in as a parameter into the stored procedure. This is the table to be moved. - Target table:
A table created by the stored procedure using the table definition passed in through the stored procedure.
All of the data from the source table is copied into this table and then it is renamed to the same name as the source table. - Staging table:
A table created by the stored procedure.The staging table stores any update, delete or insert changes that occur on the source table during the execution of the table move.
This table is dropped when the move is complete.
- The table must have no reference constraints
- The table must exist in the same schema as the source table
- The table must be empty
- No typed tables, materialized query tables (MQT), staging tables, remote tables or clustered tables are permitted
Options:
Keep:
This option keeps a copy of the original source table under a different name.
The ADMIN_MOVE_TABLE will remove the source table after moving by default ?
You can retrieve the exact name of the source table in the returned protocol table, under the ORIGINAL key.
You may set this option at any point up to and including the SWAP phase.
COPY_USE_LOAD:
If you specify any load options for COPY_USE_LOAD, ADMIN_MOVE_TABLE uses an ADMIN_CMD load to copy the data from the source table to the target table.
If you do not specify any options for COPY_USE_LOAD, then the NONRECOVERABLE option the db2Load API is used to copy the data from the source table to the target table.
Messages on server:
Specifies that the message file created on the server by the LOAD command is to be retained in case of load failures.
The WARNINGS entry in the protocol table contain the message retrieval SQL statement that is required to retrieve all the warnings and error messages that occur during load, and the message removal SQL statement that is required to clean up the messages.
Note that with or without the clause, the fenced user ID must have the authority to create files under the directory indicated by the DB2_UTIL_MSGPATH registry variable.
COPY YES
Specifies that a copy of the loaded data will be saved. This option is invalid if forward recovery is disabled.
Use TSM:
Specifies that the copy will be stored using Tivoli® Storage Manager (TSM).
Open num Sessions:
Default is 1 one session.
NON RECOVERABLE:
Specifies that the load transaction is to be marked as nonrecoverable and that it will not be possible to recover it by a subsequent roll forward action.
If COPY YES is not used, NONRECOVERABLE is the default.
COPY_WITH_INDEXES:
This option creates indexes before copying the source table.
The default is to create the indexes after copying the source table.
Advantage of COPY_WITH_INDEXS:
The advantages of this option are that index creation after copying requires a whole table scan per index and that the index creation is a transaction that requires active log space.
Disadvantages:
If the logindexbuild database configuration parameter is on, significant log space is required for building the indexes in a short time frame.
Another disadvantage of this option is that copy performance is reduced because indexes need to be maintained on the target table.
Also, the resulting indexes many contain pseudo-deleted keys, and the indexes are not as well balanced as if the indexes were created after the copy.
You may set the COPY_WITH_INDEXES option at any point up to and including the COPY phase.
FORCE:
If the force option is set, the SWAP phase does not check to see if the source table has changed its table definition.
You may set this option at any point up to and including the SWAP phase.
NO_STATS:
This option does not start RUNSTATS or any statistic copying on the target table.
If you use the AUTO_RUNSTATS or AUTO_STMT_STATS database configuration parameters, DB2 will automatically create new statistics afterwards.
You may set the NO_STATS option at any point up to and including the SWAP phase.
COPY_STATS:
This option copies the statistics from the source table to the target table before performing the swap.
Advantages:
Setting this option saves computing time as RUNSTATS is not called to compute new statistics.
Also, the optimizer may choose the same access plans, because the statistics are the same.
Disadvantages:
This may cause inaccurate physical statistics, especially if the page size is changed.
Also, the optimizer may choose the same access plans, because the statistics are the same.
You may set the STATS_COPY option at any point up to and including the SWAP phase.
NO_AUTO_REVAL:
This option prevents automatic revalidation on the table, and instead, re-creates all triggers and views.
The NO_AUTO_REVAL option can be set only in the INIT phase.
REORG:
This option sets up an extra offline REORG on the target table before performing the swap.
If you require an optimal XML compression dictionary, then REORG is the only method.
You may set the REORG option at any point up to and including the SWAP phase.
NO_TARGET_LOCKSIZE_TABLE:
This option does not keep the LOCKSIZE table option on the target table during the COPY and SWAP phases.
The default is to use the LOCKSIZE table option on the target table to prevent locking overhead, when no unique index is specified on the source table.
CLUSTER:
This option reads the data from the source table with an ORDER BY clause when a copy index has been specified using ADMIN_MOVE_TABLE_UTIL, a clustering index exists on the source table or a unique index or primary key is defined in the source table.
NON CLUSTER:
This option reads the data from the source table without an ORDER BY clause regardless of whether a copy index has been specified, a clustering index exists on the source table, or a unique index or primary key has been defined in the source table.
Note: When neither CLUSTER or NON_CLUSTER options are specified, ADMIN_MOVE_TABLE will read the data from the source table with an ORDER BY clause only when a clustering index exists on the source table.
LOAD_MSGPATH:
This option can be used to define the load message file path.
If the LOAD_MSGPATH option is not specified, then the default path is determined by the DB2_UTIL_MSGPATH registry variable.
LOAD_TEMPPATH:
Use this option to define the load temporary file path.
ALLOW_RLBAC:
Use this option to move a table that has row-level label-based access control (LBAC) security label specified. Before you use this option, ensure that you have adequate privilege; if you lack the privilege to access all the table row entries, data loss can occur.
HOW THIS UTILITY WORKS:
This input parameter specifies which operation the stored procedure is to execute.
There are two ways of calling the stored procedure:
Using the MOVE command to execute all the operations at one time;
By using the individual commands to execute the table move one step at a time.
Advantage of second method:
The main advantage of this second method is that you control when the SWAP phase actually occurs, thereby determining when the table is briefly taken offline.
This allows you to make the move during a period of low system activity.
If you use the individual commands, they must be called in the following order:
Default is 1 one session.
NON RECOVERABLE:
Specifies that the load transaction is to be marked as nonrecoverable and that it will not be possible to recover it by a subsequent roll forward action.
If COPY YES is not used, NONRECOVERABLE is the default.
COPY_WITH_INDEXES:
This option creates indexes before copying the source table.
The default is to create the indexes after copying the source table.
Advantage of COPY_WITH_INDEXS:
The advantages of this option are that index creation after copying requires a whole table scan per index and that the index creation is a transaction that requires active log space.
Disadvantages:
If the logindexbuild database configuration parameter is on, significant log space is required for building the indexes in a short time frame.
Another disadvantage of this option is that copy performance is reduced because indexes need to be maintained on the target table.
Also, the resulting indexes many contain pseudo-deleted keys, and the indexes are not as well balanced as if the indexes were created after the copy.
You may set the COPY_WITH_INDEXES option at any point up to and including the COPY phase.
FORCE:
If the force option is set, the SWAP phase does not check to see if the source table has changed its table definition.
You may set this option at any point up to and including the SWAP phase.
NO_STATS:
This option does not start RUNSTATS or any statistic copying on the target table.
If you use the AUTO_RUNSTATS or AUTO_STMT_STATS database configuration parameters, DB2 will automatically create new statistics afterwards.
You may set the NO_STATS option at any point up to and including the SWAP phase.
COPY_STATS:
This option copies the statistics from the source table to the target table before performing the swap.
Advantages:
Setting this option saves computing time as RUNSTATS is not called to compute new statistics.
Also, the optimizer may choose the same access plans, because the statistics are the same.
Disadvantages:
This may cause inaccurate physical statistics, especially if the page size is changed.
Also, the optimizer may choose the same access plans, because the statistics are the same.
You may set the STATS_COPY option at any point up to and including the SWAP phase.
NO_AUTO_REVAL:
This option prevents automatic revalidation on the table, and instead, re-creates all triggers and views.
The NO_AUTO_REVAL option can be set only in the INIT phase.
REORG:
This option sets up an extra offline REORG on the target table before performing the swap.
If you require an optimal XML compression dictionary, then REORG is the only method.
You may set the REORG option at any point up to and including the SWAP phase.
NO_TARGET_LOCKSIZE_TABLE:
This option does not keep the LOCKSIZE table option on the target table during the COPY and SWAP phases.
The default is to use the LOCKSIZE table option on the target table to prevent locking overhead, when no unique index is specified on the source table.
CLUSTER:
This option reads the data from the source table with an ORDER BY clause when a copy index has been specified using ADMIN_MOVE_TABLE_UTIL, a clustering index exists on the source table or a unique index or primary key is defined in the source table.
NON CLUSTER:
This option reads the data from the source table without an ORDER BY clause regardless of whether a copy index has been specified, a clustering index exists on the source table, or a unique index or primary key has been defined in the source table.
Note: When neither CLUSTER or NON_CLUSTER options are specified, ADMIN_MOVE_TABLE will read the data from the source table with an ORDER BY clause only when a clustering index exists on the source table.
LOAD_MSGPATH:
This option can be used to define the load message file path.
If the LOAD_MSGPATH option is not specified, then the default path is determined by the DB2_UTIL_MSGPATH registry variable.
LOAD_TEMPPATH:
Use this option to define the load temporary file path.
ALLOW_RLBAC:
Use this option to move a table that has row-level label-based access control (LBAC) security label specified. Before you use this option, ensure that you have adequate privilege; if you lack the privilege to access all the table row entries, data loss can occur.
HOW THIS UTILITY WORKS:
This input parameter specifies which operation the stored procedure is to execute.
There are two ways of calling the stored procedure:
Using the MOVE command to execute all the operations at one time;
By using the individual commands to execute the table move one step at a time.
Advantage of second method:
The main advantage of this second method is that you control when the SWAP phase actually occurs, thereby determining when the table is briefly taken offline.
This allows you to make the move during a period of low system activity.
If you use the individual commands, they must be called in the following order:
- INIT
- COPY
- REPLAY
- VERIFY(optional)
- SWAP
- MOVE: Performs the entire table move (INIT, COPY, REPLAY, and SWAP operations) in one step.
- INIT: Verifies that a table move can take place, and initializes all of the data needed during the table move process (the target table, staging table, and the triggers on the source table).
- COPY: Copies the content from the source table to the target table.
Any updates, deletes, or inserts occurring on the source table during this time are captured and stored in the staging table.
New indexes are created at the end of the COPY phase, unless the COPY_WITH_INDEXES option is selected.
Also, if needed, secondary indexes are created on the source and target tables to improve performance during the REPLAY phase.
COPY can be used only after the INIT phase has completed. - REDIRECT: Forwards changes directly to the target table instead of capturing the changes in the staging table.
- REVERT: Reverts to the original behavior wherein the staging table captures the changes.
- REPLAY: Copies into the target table any rows that have changed in the source table since the COPY phase began. REPLAY can be used only after the COPY phase has completed.
- VERIFY: Optionally checks if the table contents of the source and target tables are identical. VERIFY can be used only after the COPY or REPLAY phases have completed.
- SWAP: Executes the REPLAY phase until the number of changes applied during the last scan of the staging table is less than the REPLAY_THRESHOLD value stored in the protocol table. The source table is then taken offline briefly to finish the final REPLAY, and then this command swaps the source table with target table and brings the table back online. SWAP can be used after the COPY phase has completed, but ideally after the REPLAY phase has been called.
- CLEANUP: Drops the staging table, any non-unique indexes or triggers created on the source table by the stored procedure, and the source table if the KEEP option has not been set. CLEANUP can be called if the command failed during the SWAP phase.
- CANCEL: Cancels a multi-step table move while between phases, or cancels a failed table move operation. Executing this command requires that the operation status is not in COMPLETED or CLEANUP state. CANCEL clears up all intermediate data (the indexes, the staging table, the target table, and the triggers on the source table).
AUTHORIZATION:
You must have SQLADM or DBADM authority to invoke the ADMIN_MOVE_TABLE stored procedure.
You must also have the appropriate object creation authorities, including authorities to issue the SELECT statement on the source table, and to issue the INSERT statement on the target table.
Default PUBLIC Privilege:
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.
