This article is based on the “Online Tablespace Migration Using ADMIN_MOVE_TABLE Procedure” presentation for the The DB2Night Show #125: DB2’s GOT TALENT Contestant Search #2 held on Friday, 14th February 2014. I made it through the qualifying round but frankly speaking it was heavy competition out there in the Round 1 itself. Total 7 ideas presented and all of them had done their job extremely well. 3 of us – Michael, Raja and myself got a chance to present into the finals and rest of the contestants got chance to present again on 28th February 2014. Here are few outlines of my presentation in Round 1.
Table with more than 100K records needs to be migrated from 8K tablespace to 16K with minimum downtime and minimum risk of data loss.
I found out that to resolve this problem we have two possible solutions
1. EXPORT AND LOAD METHOD
In this we need to export all the data and then drop the table and re-create the table in new tablespace and re-create the constraints,indexes, views and MQTs referring to that table. And for the DB2 versions prior to DB2 9.7, probably this is the only solution we have.
But here are few Bottlenecks:
a. Migration needs to be OFFLINE.
b. There is always a risk of data loss.
Regarding data loss, I have this experience on our UAT environment. Since we have constraints on our disk space on database server. Developer executed the script by this method and due to disk space only half of the records got exported and those many only imported into the new table which cause a real trouble for me at work. 😦
So to avoid such mishaps, there is another way to all those who are using DB2 versions 9.7 and higher.
2. ONLINE MIGRATION USING ADMIN_MOVE_TABLE PROCEDURE
Here are the steps how this will work.
a. Drop MQTs, referential constraints and Views referencing the subject table.
b. Migrate the table to 16K tablespace using SYSPROC.ADMIN_MOVE_TABLE procedure.
c. Re-create the MQTs, referential constraints and Views.
Schema for this routine is SYSPROC.
db2 “CALL SYSPROC.ADMIN_MOVE_TABLE (<schemaname>, <tablename> , <data_tbsp>, <index_tbsp>,
<lob_tbsp>, <mdc_col_list>, <hash_key_list>,<range_part_key_list>, <column-definitions>,<options-list>,’MOVE’)”
For online tablespace migration we can ignore the few input parameters and pass them as NULL. Here is sample example: