DB2 10.1 DBA for Linux, UNIX, and Windows certification exam 611 prep, Part 5: DB2 utilities

ADMIN_MOVE_TABLE procedure

You can move tables both online and offline using ADMIN_MOVE_TABLE. In fact, this procedure can be used to move the data in a table to a new table object of the same name (but with possibly different storage characteristics, such as different tablespace) while the data remains online and available for access. You can also generate a new optimal compression dictionary when a table is moved.

The ADMIN_MOVE_TABLE stored procedure creates a protocol table composed of rows containing status information and configuration options related to the table to be moved. The return set from this procedure is the rows from that protocol table related to the table to be moved.

You can invoke the ADMIN_MOVE_TABLE by two methods.

Method 1 — Modify only certain parts of the table definition for the target table (if you just want to modify the tablespaces for the table objects, for exmaple). All you need to do is to fill out the data_tbsp, index_tbsp, and lob_tbsp parameters while calling the procedure, leaving the other optional parameters blank.

Example

This example calls the stored procedure using the first method, where the target table is defined within the procedure, to move a table named T1, which is located in the schema titled SCHEMA1. Additionally, the column definitions of the target table are passed to the procedure.

 
CALL SYSPROC.ADMIN_MOVE_TABLE(
'SCHEMA1',
'T1',
'ACCOUNTING',
'ACCOUNT_IDX',
'ACCOUNT_LONG',
'',
'',
'',
'CUSTOMER VARCHAR(80), REGION CHAR(5), YEAR INTEGER, CONTENTS CLOB',
'',
'MOVE')
                

the above example moves the table SCHEMA1.T1 to a new table with the same name that has the columns definitions: CUSTOMER VARCHAR(80), REGION CHAR(5), YEAR INTEGER, CONTENTS CLOB and resides in tablespace ACCOUNTING with its indices tablespace ACCOUNT_IDX and its LOBs tablespace ACCOUNT_LONG.

Method 2 — Create the target table and provide its name to the procedure. This provides you with more control and flexibility by allowing you to create the target table beforehand, rather than having the stored procedure create it.

Example

This example is equivalent to the previous one, but it calls the stored procedure using the second method, where the target table is created outside the procedure and is then named within the target_tabname parameter, to move the same table as in the previous example.

 
CREATE TABLE SCHEMA1.T1_TGT (
CUSTOMER VARCHAR(80), 
REGION CHAR(5), 
YEAR INTEGER, 
CONTENTS CLOB) 
IN ACCOUNTING 
INDEX IN ACCOUNT_IDX 
LONG IN ACCOUNT_LONG'
                

 
CALL SYSPROC.ADMIN_MOVE_TABLE(
'SCHEMA',
'T1',
'T1_TGT',
'',
'MOVE')
                

For online data movement:

1. The procedure creates a shadow table to which the data are copied.
2. Any changes to the source table during the copy phase are captured using triggers and placed in a staging table.
3. After the copy phase is completed, the changes captured in the staging table are replayed to the shadow copy.
4. 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 indices.
5. 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.

Obviously, the online operation costs more server resources (disk space and processing power), so make sure you only use it if you value availability more than cost, space, move performance, and transaction overhead. Additionally, avoid performing online moves for tables without indices, particularly unique indices as it might result in deadlocks and complex or expensive replay.

Back to top

Handling online move failure

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 COPY.
   • If the status of the procedure is REPLAY, use REPLAY or SWAP.
   • If the status of the procedure is CLEANUP, use CLEANUP.

If the status of an online table move is not COMPLETED or CLEANUP, you can cancel the move by specifying CANCEL for the stored procedure.

Back to top

ADMIN_MOVE_TABLE procedure and temporal tables

There are some limitations when using the ADMIN_MOVE_TABLE stored procedure to move data in an active system-period temporal table into a new table with the same name. The following actions are blocked:

• Alter table operations that change the definition of the system-period temporal table or the associated history table are blocked during online move operations.
• The KEEP option of ADMIN_MOVE_TABLE is unavailable for system-period temporal tables.

Additionally, the online-table-move operation is not supported for history tables.

9 of 15 | Previous | Next

Comments

Back to top

Table of contents

• Before you start
• Data movement utilities
• DB2 EXPORT utility
• DB2 IMPORT utility
• DB2 LOAD utility
• DB2 INGEST utility
• IMPORT, LOAD and INGEST comparison
• Data movement utilities and hidden columns
• ADMIN_MOVE_TABLE procedure
• Other DB2 data movement utilities
• DB2 maintenance utilities
• DB2 Advisors
• Conclusion
• Resources
• About the author

---

9 of 15 | Previous | Next

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers