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.
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.
Ensure there is sufficient disk space to accommodate the copies of the table and index, the staging table, and the additional log entries.
To move a table online:
CALL SYSPROC.ADMIN_MOVE_TABLE(
'SVALENTI',
'T1',
'ACCOUNTING',
'ACCOUNTING',
'ACCOUNTING',
'',
'',
'',
'',
'',
'MOVE')
CALL SYSPROC.ADMIN_MOVE_TABLE(
'EBABANI',
'T1',
'ACCOUNTING',
'ACCOUNTING',
'ACCOUNTING',
'',
'',
'',
'',
'KEEP, COPY_USE_LOAD,LOAD_MSGPATH "/home/ebabani"',
'MOVE')
CALL SYSPROC.ADMIN_MOVE_TABLE(
'SVALENTI',
'T1',
'',
'',
'',
'',
'',
'',
'C1 VARCHAR(1000), C2 INT(5), C3 CHAR(5), C4 CLOB',
'',
'MOVE')
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')
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')"