Presentation of ADMIN_MOVE_TABLE utility

50 %
50 %
Information about Presentation of ADMIN_MOVE_TABLE utility

Published on February 21, 2014

Author: pandeprasad1



I presented my experience of ADMIN_MOVE_TABLE to all DB2 enthusiast @db2nightshow.

Just to make it available for all those who want to refer it for their use.


PROBLEM STATEMENT Table with more than 100K records needs to be migrated from 8K tablespace to 16K with minimum downtime and minimum risk of data loss  POSSIBLE SOLUTIONS ◦ EXPORT AND LOAD METHOD  For versions Prior to DB2 9.7  Migration needs to be OFFLINE  Risk of Data Loss ◦ ONLINE MIGRATION USING ADMIN_MOVE_TABLE PROCEDURE

ONLINE MIGRATION USING ADMIN_MOVE_TABLE PROCEDURE  Drop MQTs, Referential Constraints and Views referencing the subject table.  Migrate the table to 16K tablespace using SYSPROC.ADMIN_MOVE_TABLE procedure.  Re-create the MQTs, Referential constraints and Views.

ADMIN_MOVE_TABLE: SYNTAX  db2 CALL SYSPROC.ADMIN_MOVE_TABLE (<schemaname>, <tablename>, <data_tbsp>, <index_tbsp>, <lob_tbsp>, <mdc_col_list>, <hash_key_list>,<range_part_key_list>, <columndefinitions>,<options-list>,’MOVE’)  db2 CALL SYSPROC.ADMIN_MOVE_TABLE (<schemaname>, <tablename>, <data_tbsp>, <index_tbsp>, <lob_tbsp>, NULL, NULL, NULL, NULL , <options-list>,’MOVE’)

HOW IT WORKS       INIT – Verify if table can be moved, also initializes the data needed during Migration. (target table, staging table, triggers) COPY – Copy the data from source table to target table. REPLAY – Copy the changes recorded in the staging table to the target table. SWAP – Rename the target table with that of the source table name. CLEANUP – Delete all triggers, staging table. VERIFY – Optionally verifies the contents of the source and target table.

ADVANTAGES  Very small OFFLINE window is required.  Data is ACCESSIBLE while Migration.  No Data Loss (Because of No EXPORT AND LOAD).  Excellent solution to perform data maintenance operations during online maintenance window.

LIMITATIONS  Online migration without unique indexes can be expensive or might result in deadlock.  Tables that have foreign key constraints, MQTs cannot be moved.  Tables that have only LONG, LOB and XML data cannot be moved.

TIPS AND TRICKS     All the parameters MUST be mentioned in UPPER CASE. Instead of MOVE operation, we can perform step by step operation INIT, COPY, REPLAY, SWAP ,VERIFY, CLEANUP to gain more control. VERIFY Operation is very costly. Use it only when required To check the current phase: ◦ select * from SYSTOOLS.ADMIN_MOVE_TABLE where KEY=‘STATUS’;

TIPS AND TRICKS (Cont.) Use of COPY_USE_LOAD option to will give high throughput in the COPY phase.  Performance depends on the throughput of COPY and REPLAY phases.  Table with many indexes can be troublesome.  Moving fully compressed tables will give better throughput than uncompressed or partially compressed tables. 


CONTACT INFORMATION Email:  LinkedIn:  Twitter: Follow me at @pandeprasad1  Blog: 

Add a comment

Related presentations

Related pages

Presentation of ADMIN_MOVE_TABLE utility - Technology

I presented my experience of ADMIN_MOVE_TABLE to all DB2 enthusiast @db2nightshow. Just to make it available for all those who want to refer it for their use.
Read more

Moving tables online using ADMIN MOVE TABLE

ADMIN_MOVE_TABLE Ergin Babani ... INFORMATION CONTAINED IN THIS PRESENTATION, ... To avoid any naming conflicts the utility will use the following method ...
Read more

Estimation of Utility Costs - Documents - Discover, share ...

Share Estimation of Utility Costs. ... Presentation of ADMIN_MOVE_TABLE utility I presented my experience of ADMIN_MOVE_TABLE to all DB2 enthusiast @ ...
Read more

36720: IBM CL463DE - DB2 10.5 for LUW Advanced Database ...

... the complexity of managing DB2 LUW databases - Describe the conditions that would impact selection of the INGEST utility ... ADMIN_MOVE_TABLE, ...
Read more

IDUG : Archive File Display Page

This presentation will give you an ... Import, Export, db2move, admin_copy_schema, admin_move_table ... DB2 LUW core engine data movement utility ...
Read more