Welcome the ONLINE_TABLE_MOVE stored procedure. With a few restrictions it works wonderfully to move tables while the system is up and running. It can even be used to deep compress tables without having to run an offline REORG on them. This is most useful if you have tables spanning hundreds of gigabytes and can’t afford the maintenance window to do the offline compression.
Prerequisites
- You need to be on DB2 v8.2.2 or higher to use this stored procedure
- The DB2 registry DB2_SKIPDELETED must not be set to on
- The SYSTOOLSPACE tablespace must be accessible to PUBLIC
- The DB6CONV program in SAP should be v4.0 or higher
- Online table moves using the online stored procedure may be considerably slower than offline moves.
- This procedure does not work accurately is some special cases. For example, it is not recommended to be used on tables that are truncated from time to time (BI temporary tables).
- It is not recommended to move tables which do not have a unique indeed as it may cause excessive locks and deadlocks in the database.
- It can be used to run multiple table moves in parallel.
- For more information refer to SAP Note 1039544.
The ONLINE_TABLE_MOVE stored procedure creates a shadow copy of the table. Triggers are created on the source table to capture any updates, deletes and inserts. The changes to the source table while the move is going on are populated into a temporary staging table. These changes which are captured in the staging table are later replayed into the shadow (target) table to make the source and target tables in sync.
The phase where data is copied from the source table to the shadow table is called the COPY phase. The phase where the changes in the staging table are applied to the shadow is called REPLAY. When the COPY and the REPLAY are finished and the source and shadow table are in sync, the source table is locked in exclusive mode and a small OFFLINE phase is initiated. The source and the shadow (target) table names are swapped during this small OFFLINE phase.
Once the table is moved successfully, the CLEANUP phase cleans up all the temporary tables and drops the triggers.
Here is a flow chart that represents the process:
The stored procedure provides the flexibility of choosing to do one phase of the move at a time or all of them together.
Here are the lists of options which can be used by the Stored Procedure:
CANCEL Cancels Move Operation
INIT Initiates the table move. Creates target table, staging tables and the triggers
COPY Copies table content and creates indexes
REPLAY Replays staging table information
SWAP Swaps the source and the target table
KEEP Keeps the source table
MOVE Moves table. This is INIT, COPY and SWAP all in one.
CLEANUP If the cleanup fails during the table swap, this can be initiated later to cleanup.
TRACE Generates a debug trace
STATS_NO Does not perform RUNSTATS after move
STATS_COPY Copies old stats instead of collecting new ones
REORG Performs an offline REORG on target table before SWAP. Useful for deep compression.
COPY WITH INDEXES COPIES after creating indexes on the target table. Default is to copy the data first and then create indexes on target.
COPY_USE_LOAD Uses LOAD instead of the default INSERTs. Will require a backup after the move.
The table SAPTOOLS.ONLINE_TABLE_MOVE can be queried to get the status of table moves going on with the ONLINE_TABLE_MOVE stored procedure.
Installation
For DB2 v9.1
The stored procedure files have to be downloaded from SAP.
- Download the stored procedure file from SAP based on your operating system.
- Extract the files and copy the online_table_move_sp into the <INSTHOME>/sqllib/function (UNIX and LINUX) and <INSTHOME>\sqllib\function (Windows).
- Connect to the database and issue the following:
For DB2 v9.5 (Viper II)
The stored procedure is a part of DB2 v9.5.
To start using the stored procedure in DB2 v9.5, issue the following:
db2 invoke db2sap
Table move Procedure
- Make sure that the stored procedure is installed in <INSTHOME>/sqllib/function or <INSTHOME>\sqllib\function
- Invoke the stored procedure
OR
db2 invoke db2sap (v9.5)
- Make sure the user has the proper privileges
- Give the following command to start a table move
db2 "CALL SAPTOOLS.ONLINE_TABLE_MOVE( '<Table Schema>', '<Table Name>', '<Target tablespace for tables>','Target tablespace for indexes >', '', '', '', 'MOVE' )"
for example to move a table SCH1.TAB1 from tablespace TBSP1 to TBSP2:
db2 "CALL SAPTOOLS.ONLINE_TABLE_MOVE( 'SCH1', 'TAB1', 'TBSP1','TBSP2', '', '', '', 'MOVE' )"
- To perform the table move one step at a time, use the following sequence of commands:
db2 "CALL SAPTOOLS.ONLINE_TABLE_MOVE( '<Table Schema>', '<Table Name>', '<Target tablespace for tables>','Target tablespace for indexes >', '', '', '', 'INIT' )"
db2 "CALL SAPTOOLS.ONLINE_TABLE_MOVE( '<Table Schema>', '<Table Name>', '<Target tablespace for tables>','Target tablespace for indexes >', '', '', '', 'COPY' )"
db2 "CALL SAPTOOLS.ONLINE_TABLE_MOVE( '<Table Schema>', '<Table Name>', '<Target tablespace for tables>','Target tablespace for indexes >', '', '', '', 'SWAP' )"
The advantage in doing this is that after your COPY phase is complete, you can run the SWAP phase during a time when the load on the system is low. This would have less impact on your online users/application while the offline SWAP phase takes place.
- If the COPY or the SWAP phase fails, you can CANCEL the move by invoking the following command:
db2 "CALL SAPTOOLS.ONLINE_TABLE_MOVE( '<Table Schema>', '<Table Name>', '<Target tablespace for tables>','Target tablespace for indexes >', '', '', '', 'CANCEL' )"
- To check the status of the table move at any time, you can query the table SAPTOOLS.ONLINE_TABLE_MOVE
Select STATUS from SAPTOOLS.ONLINE_TABLE_MOVE where STAGING like ‘%<source_table>%’
For example if moving the table DFKKOP:
Select STATUS from SAPTOOLS.ONLINE_TABLE_MOVE where STAGING like ‘%DFKKOP%’
Note: You may run into many errors when you run it first time. Refer to the Sap Note 1039544 for detailed instructions on how to troubleshoot the errors.
