Published on February 21, 2014
ONLINE TABLESPACE MIGRATION USING ADMIN_MOVE_TABLE DB2’s GOT TALENT PRESENTER: PRASAD PANDE
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: firstname.lastname@example.org LinkedIn: in.linkedin.com/pub/prasadpande/24/292/a23/ Twitter: Follow me at @pandeprasad1 Blog: http://prasadspande.wordpress.com/
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.
ADMIN_MOVE_TABLE Ergin Babani ... INFORMATION CONTAINED IN THIS PRESENTATION, ... To avoid any naming conflicts the utility will use the following method ...
Share Estimation of Utility Costs. ... Presentation of ADMIN_MOVE_TABLE utility I presented my experience of ADMIN_MOVE_TABLE to all DB2 enthusiast @ ...
... the complexity of managing DB2 LUW databases - Describe the conditions that would impact selection of the INGEST utility ... ADMIN_MOVE_TABLE, ...
This presentation will give you an ... Import, Export, db2move, admin_copy_schema, admin_move_table ... DB2 LUW core engine data movement utility ...