ADMIN_MOVE_TABLE

Have you ever had to move a table from one tablespace to another ?  If you haven’t then you haven’t been working with lot of large tables. Ask a dba who supports SAP, Siebel or PeopleSoft database.  Traditionally when a DBA has to move a table from one tablespace to another there are several steps he or she needs to take, like unloading the table, reverse engineer the objects and dependencies  , drop and recreate the objects and dependencies, load the data. While you do all that the table needs to be offline.  But what if its a 24×7 table and you cannot afford any downtime.  You might say there are other options and  yes there are and one such option (easy) is using the stored procedure SYSPROC.ADMIN_MOVE_TABLE.  This stored procedure came with DB2 V9.7 and can move a table from one tablespace to another while the table is online . It can also handle moving a range partition to a different tablespaces, change column type while you copy,  remove columns, create MDC tables and so on.  The key point here is , the table is ONLINE while you do all this and its completely recoverable.
Now this comes with a caveat, since its recoverable and online, all the activities are logged, the data is moved using a insert..select , so the transaction logs are used.  There is a LOAD option you can specify, but then the table might be locked for a  period of time.

ADMIN_MOVE_TABLE has 5 stages :

1. INIT : During this stage, db2 checks to see if the table move can take place and if the user has the proper authority. If the user passes a target table, db2 creates the table if it doesn’t exist. DB2 creates a staging table to capture any DML that happens during the copy phase. DB2 also creates 4 triggers to capture any data change activity.

2. COPY :  In this phase, DB2 copies the data from source table to the target table. Cursor Stability is used when DB2 reads the data from the source table.  By default, DB2 inserts the data to the target table using regular INSERT, so transaction logs are used to make the process recoverable.  You can optionally use can load the data using a cursor by specifying NONRECOVERABLE LOAD. But then the table might be locked for a period of time and the process us not recoverable. Its advised to take a backup if a load is used. DB2 created all the indexes and other objects

3. REPLAY :  While the COPY phase is in progress, DB2 records changes to source table to a staging table using the triggers it created in the INIT phase.  Once the COPY phase is complete, DB2 replays or merges all the rows in the staging table to the target table. There might be instances that few rows might still be left pending in the staging table.

4. SWAP : On this phase, DB2 swaps the name of the target table with that of the source table.  In other words the source table is given a new name and the target tables is renamed to the actual table name.  DB2 takes an exclusive lock for a brief period on both the source and target table to ensure that no new data change happens while it completes any rows that is still there on the staging table. Once this is complete,  the old source table is then removed or kept according to the option specified.

5. CLEANUP: DB2 now cleans up all the temporary objects it created for this process. It drops the 4 triggers and the staging table and completes the transaction.

At the end of the process, it writes all the details on the systools.ADMIN_MOVE_TABLE.

SYNTAX:  There are essentially two methods to use ADMIN_MOVE_TABLE.  The first method allows you to change certain parts of the table definition for the target table. For instance, if you had a table DDL that is quite large , and all you want to do is modify the table spaces for the table, you can do so without having to determine the entire CREATE TABLE statement needed to re-create the source table. All you need to do is to fill out the data_tbsp, index_tbsp, and lob_tbsp parameters, leaving the other optional parameters blank. You can also use this method to add MDC columns, change column types etc.

The second method provides you with more control and flexibility by allowing you to create the target table beforehand, rather than having the stored procedure create the target table. This enables you to create a target table that would not be possible using the first method.

Method # 1:

>>-ADMIN_MOVE_TABLE--(--tabschema--,--tabname--,---------------->

>--data_tbsp--,--index_tbsp--,--lob_tbsp--,--mdc_cols--,-------->

                                             .-,-------.      
                                             V         |      
>--partkey_cols--,--data_part--,--coldef--,----options-+--,----->

>--operation--)------------------------------------------------><

Method # 2:

>>-ADMIN_MOVE_TABLE--(--tabschema--,--tabname--,---------------->

                      .-,-------.                    
                      V         |                    
>--target_tabname--,----options-+--,--operation--)-------------><

Here is an example that I used to move ORDERS table to a new tablespaces:

CALL SYSPROC.ADMIN_MOVE_TABLE(
‘RAJU’,
‘ORDERS’,
‘ORDERSTBSP’,
‘ORDERSIDXSP,
‘ORDERSLOBSP’,
”,
”,
”,
”,
”,
‘MOVE’)

Here is a short video I attempted to show  how it works. In this video, I will demonstrate  how DB2 ADMIN_MOVE_TABLE moves the ORDERS table from a tablespace IBMDB2SAMPLEREL tablespace to USERSPACE1 while a batch insert is running.

What I am mentioned above is solely my opinion, please do your due diligence before you try this on your system.  Please send me any comments or suggestions on this.