RSS

#DB2-2 ONLINE TABLESPACE MIGRATION USING SYSPROC.ADMIN_MOVE_TABLE

23 Oct

Recently I faced couple of issues while migrating the table from one table space to another table space.

As per my knowledge the way that is commonly followed everywhere is:
1) export the data
2) Drop constraints and indexes
2) drop the table
3) Create table in new tablespace
4) Re-create the constraints
5) Import the data

In this way the bottleneck might be:

1) We need to push the table down i.e. the migration need to be performed OFFLINE.
2) Export and import operation might be costly and time consuming in case of big tables.
While doing POC on this I came across this new method to move the table ONLINE in few steps.
This procedure (SYSPROC.ADMIN_MOVE_TABLE) help us to move the table online in more better way.

This stuff is very cool 🙂
SYSPROC.ADMIN_MOVE_TABLE:

In this while copying the intermediate table called staging table is used to record any changes those are occurring during the migration. 4 triggers are used to record these changes.

There are 4 important stages in this procedure:

1) INIT STAGE – Check if the table is in the state to perofrm the given operation
2) COPY STAGE – Copy data from source table to target table. In this there is access to the source table while copy is in progress
3) REPLAY STAGE – We scan staging table and replay these changes in the target table
4) SWAP STAGE – Rename the target table to source table and the original table is removed.

STEPS FOR THE MIGRATION OF THE TABLE USING SYSPROC.ADMIN_MOVE_TABLE:

1) Drop all unique indexes and foreign key constraints on the table.
2) Drop Primary Key constraint as well as unique index on the primary key
3) Create a temporary table in the new tablespace
4) db2 “CALL SYSPROC.ADMIN_MOVE_TABLE(‘<schema-name>’,'<source-table>’,'<target-table>’,”,’INIT’)”
5) db2 “CALL SYSPROC.ADMIN_MOVE_TABLE(‘<schema-name>’,'<source-table>’,'<target-table>’,”,’COPY’)”
6) db2 “CALL SYSPROC.ADMIN_MOVE_TABLE(‘<schema-name>’,'<source-table>’,'<target-table>’,”,’REPLAY’)”
7) db2 “CALL SYSPROC.ADMIN_MOVE_TABLE(‘<schema-name>’,'<source-table>’,'<target-table>’,”,’SWAP’)”
8) Re-create all the constraints and indexes.

NOTE:

1. Instead of performing the INIT, COPY, REPLAY and SWAP stage individually, we can perform this all at once in single step as follows:

db2 “CALL SYSPROC.ADMIN_MOVE_TABLE(‘<schema-name>’,'<source-table>’,'<target-table>’,”,’MOVE’)”

2. While testing this in few cases, I faced the following issue:
SQL2104N The ADMIN_MOVE_TABLE procedure could not be completed at this time
by this user. Reason code: “4”. SQLSTATE=5UA0M

3. I had written the following Stored Procedure to take care of Error#2. This will by pass this error by using the flag.

This error occurs if the table is not in the state to get migrated or if the table is in any intermediate state.

CREATE PROCEDURE ADMIN_MOVE_TABLE_SP
(
IN pSchemaName VARCHAR(50),
IN pTableName VARCHAR(50),
IN pTargetDataTbspace VARCHAR(100),
IN pTargetIndTbspace VARCHAR(100),
IN pTargetLOBTbspace VARCHAR(100)
)
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0000;
DECLARE c_duplicate INTEGER DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘5UA0M’
SET c_duplicate = 1;

CALL SYSPROC.ADMIN_MOVE_TABLE(pSchemaName,pTableName,pTargetDataTbspace,

pTargetIndTbspace,pTargetLOBTbspace,NULL,NULL,NULL,NULL,NULL,’MOVE’);

IF (c_duplicate = 1) THEN
CALL SYSPROC.ADMIN_MOVE_TABLE(pSchemaName,pTableName,pTargetDataTbspace,pTargetIndTbspace,pTargetLOBTbspace,NULL,NULL,NULL,NULL,NULL,’CANCEL’);

CALL SYSPROC.ADMIN_MOVE_TABLE(pSchemaName,pTableName,pTargetDataTbspace,pTargetIndTbspace,pTargetLOBTbspace,NULL,NULL,NULL,NULL,NULL,’MOVE’);
SET c_duplicate =0;
END IF;

END

4. This procedure is introduce in DB2 9.7 so will be applicable in all the versions after DB2 9.7.

5. I follow the attached document and the following blog which are very helpful:

http://blog.joachim-selke.de/2009/12/how-to-move-a-table-to-a-different-tablespace-in-db2/

6. To understand the thorough working of this procedure following pdf  I feel is handy:

http://www.dbisoftware.com/media/IBM-Ergin-Babani-DB2LUW-Online-Table-Moves.pdf

Limitations:
One of the limitation I found while studying this is that, All the parameters we are passing to the stored procedure need to
be in the Capital letters. I am not sure why it is so but if we mentioned it in small case or mixed case it will not work.

Thanks

Advertisements
 
3 Comments

Posted by on October 23, 2013 in DB2

 

3 responses to “#DB2-2 ONLINE TABLESPACE MIGRATION USING SYSPROC.ADMIN_MOVE_TABLE

  1. Raul

    February 21, 2014 at 7:15 am

    Hi, we are facing the problem of having to move tables from 8KB TBSP into new 4KB TBSP. The problem is Referential Integrity, which many tables have. In this case, is there a particular order tables should be moved? does the database need to be quiesced? What is your suggestion?
    Thanks in advance

     
    • prasadpande1990

      February 26, 2014 at 2:23 am

      Hi Rahul,

      I hope you got your answer there is no need to quiesced the database otherwise it would not server the purpose of the ONLINE tablespace migration.
      We need to drop all the referential constraints on the subject tables because those can not be moved.
      But if you are using the DB2 10 FP1 and onward there is no need for this, since foreign keys can also be moved using ADMIN_MOVE_TABLE utility.

       
  2. Raul

    February 21, 2014 at 7:28 am

    Sorry, you described it pretty well. It’s the problems of hurry-reading.
    I just wonder if Data Studio version 4 is able to construct all the steps required to achieve it. I will see…

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: