DB2 10.5 for Linux, UNIX, and Windows

Moving tables online by using the ADMIN_MOVE_TABLE procedure

Using the ADMIN_MOVE_TABLE procedure, you can move tables by using an online or offline move. Use an online table move instead of an offline table move if you value availability more than cost, space, move performance, and transaction overhead.

Before you begin

Ensure there is sufficient disk space to accommodate the copies of the table and index, the staging table, and the additional log entries.

About this task

You can move a table online by calling the stored procedure once or multiple times, one call for each operation performed by the procedure. Using multiple calls provides you with additional options, such as cancelling the move or controlling when the target table is taken offline to be updated.

When you call the SYSPROC.ADMIN_MOVE_TABLE procedure, a shadow copy of the source table is created. During the copy phase, changes to the source table (updates, insertions, or deletions) are captured using triggers and placed in a staging table. After the copy phase is completed, the changes captured in the staging table are replayed to the shadow copy. Following that, the stored procedure briefly takes the source table offline and assigns the source table name and index names to the shadow copy and its indexes. The shadow table is then brought online, replacing the source table. By default, the source table is dropped, but you can use the KEEP option to retain it under a different name.

Avoid performing online moves for tables without indexes, particularly unique indexes. Performing a online move for a table without a unique index might result in deadlocks and complex or expensive replay.

Procedure

To move a table online:

Call the ADMIN_MOVE_TABLE procedure in one of the following ways:
- Call the ADMIN_MOVE_TABLE procedure once, specifying at least the schema name of the source table, the source table name, and an operation type of MOVE. For example, use the following syntax to move the data to an existing table within the same table space:
```
CALL SYSPROC.ADMIN_MOVE_TABLE (
'schema name',
'source table',
'',
'',
'',
'',
'',
'',
'',
'',
'MOVE')
```
- Call the ADMIN_MOVE_TABLE procedure multiple times, once for each operation, specifying at least the schema name of the source table, the source table name, and an operation name. For example, use the following syntax to move the data to a new table within the same table space:
```
CALL SYSPROC.ADMIN_MOVE_TABLE (
'schema name',
'source table',
'',
'',
'',
'',
'',
'',
'',
'',
'operation name')
```
  where operation name is one of the following values: INIT, COPY, REPLAY, VERIFY, or SWAP. You must call the procedure based on this order of operations, for example, you must specify INIT as the operation name in the first call.
  Note: The VERIFY operation is costly; perform this operation only if you require it for your table move.
If the online move fails, rerun it:
1. Fix the problem that caused the table move to fail.
2. Determine the stage that was in progress when the table move failed by querying the SYSTOOLS.ADMIN_MOVE_TABLE protocol table for the status.
3. Call the stored procedure again, specifying the applicable option:
  - If the status of the procedure is INIT, use the INIT option.
  - If the status of the procedure is COPY, use the COPY option.
  - If the status of the procedure is REPLAY, use the REPLAY or SWAP option.
  - If the status of the procedure is CLEANUP, use the CLEANUP option.
If the status of an online table move is not COMPLETED or CLEANUP, you can cancel the move by specifying the CANCEL option for the stored procedure.

Examples

Example 1: Move the T1 table from schema SVALENTI, to the ACCOUNTING table space without taking T1 offline. Specify the DATA, INDEX, and LONG table spaces to move the table into a new table space.

CALL SYSPROC.ADMIN_MOVE_TABLE( 
'SVALENTI', 
'T1', 
'ACCOUNTING',
'ACCOUNTING',
'ACCOUNTING',
'', 
'', 
'', 
'',
'',
'MOVE')

Example 2: Move the T1 table from schema EBABANI to the ACCOUNTING table space without taking T1 offline, and keep a copy of the original table after the move. Use the COPY_USE_LOAD and LOAD_MSGPATH options to set the load message file path. Specify the DATA, INDEX, and LONG table spaces to move the table into a new table space. The original table will maintain a name similar to 'EBABANI'.'T1AAAAVxo'.

CALL SYSPROC.ADMIN_MOVE_TABLE( 
'EBABANI', 
'T1', 
'ACCOUNTING',
'ACCOUNTING',
'ACCOUNTING',
'', 
'', 
'', 
'',
'KEEP, COPY_USE_LOAD,LOAD_MSGPATH "/home/ebabani"',
'MOVE')

Example 3: Move the T1 table within the same table space. Change the C1 column within T1, which uses the deprecated datatype LONG VARCHAR to use a compatible data type.

CALL SYSPROC.ADMIN_MOVE_TABLE(
'SVALENTI',
'T1',
'',
'',
'',
'',
'',
'',
'C1 VARCHAR(1000), C2 INT(5), C3 CHAR(5), C4 CLOB',
'',
'MOVE')

Note: You cannot change the column name during this operation.

Example 4: You have the T1 table created by the following statement:

CREATE TABLE T1(C1 BIGINT,C2 BIGINT,C3 CHAR(20),C4 DEC(10,2),C5 TIMESTAMP,C6 BIGINT 
	GENERATED ALWAYS AS (C1+c2),C7 GRAPHIC(10),C8 VARGRAPHIC(20),C9 XML

Move the table within the same table space and drop columns C5 and C6:

CALL SYSPROC.ADMIN_MOVE_TABLE(
'SVALENTI', 
'T1', 
'',
'',
'',
'', 
'', 
'', 
'c1 BIGINT,c2 BIGINT ,c3 CHAR(20),c4 DEC(10,2),c7 GRAPHIC(10),c8 VARGRAPHIC(20),c9 XML',
'', 
'MOVE')

Example 5: You have a range partitioned table with two ranges defined in tablespaces TS1 and TS2. Move the table to tablespace TS3, but leave the first range in TS1.

CREATE TABLE "EBABANI "."T1"  (
	"I1" INTEGER , 
	"I2" INTEGER )   
 	DISTRIBUTE BY HASH("I1")   
	PARTITION BY RANGE("I1") 
	(PART "PART0" STARTING(0) ENDING(100) IN "TS1", 
	PART "PART1" STARTING(101) ENDING(MAXVALUE) IN "TS2");

Move the T1 table from schema EBABANI to the TS3 table space. Specify the partition definitions.

DB2 "CALL SYSPROC.ADMIN_MOVE_TABLE 
('EBABANI',
'T1',
'TS3',
'TS3',
'TS3',
'',
'',
'(I1) (STARTING 0 ENDING 100 IN TS1 INDEX IN TS1 LONG IN TS1, 
	STARTING 101 ENDING MAXVALUE IN TS3 INDEX IN TS3 LONG IN TS3)',
'',
'',
'MOVE')"