RSS

Monthly Archives: October 2013

#DB2-2 ONLINE TABLESPACE MIGRATION USING SYSPROC.ADMIN_MOVE_TABLE

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

 

#DB2-1 PROCEDURE TO REDUCE THE SIZE OF DB2 DATABASE

Here is my attempt to write the stored procedure which will reduce the database size by reducing the size of all the Database Managed
Table-spaces. I purposely exclude the system managed table-space as well as USER managed table-space including
MEDIUM_TSD and MEDIUM_TSI (for my requirement).

I make the AUTORESIZE option ON and INCREASESIZE because in case if any after reducing the table-space size, it got full then it should automatically
increase the tablespace size by say 5 or 10 percent. (In my case it is 5%).
Actual logic for reducing the tablespace size:

‘ALTER TABLESPACE ‘ || v_TBSPACE || ‘ REDUCE (ALL ‘ || TRIM(CHAR(v_TBSP_FREE_PAGES – 100)) || ‘)’;

It will reduce the the tablespace size by reducing the number of free pages. It will keep only 100 free pages per tablesapce.
I make the use of the prepared statement to make it dynamic by selecting the tablespace name and the number of free pages (TBSP_FREE_PAGES)
from views syscat.tablespaces and SYSIBMADM.TBSP_UTILIZATION.

One can find the view details at the standard IBM links.

Following is the Stored Procedure:
— ———————————————————————————————————————————
CONNECT TO @DBNAME@
/

SET CURRENT SCHEMA = ‘@SCHEMANAME@’
/

SET CURRENT PATH = @SCHEMANAME@, SYSFUN, SYSIBM, SYSPROC, SYSIBMADM, @USERNAME@
/
DROP PROCEDURE REDUCE_DB_SIZE
/

CREATE PROCEDURE REDUCE_DB_SIZE
(
)
LANGUAGE SQL
SPECIFIC REDUCE_DB_SIZE
BEGIN

DECLARE SQLCODE INTEGER;
DECLARE v_TBSPACE VARCHAR(128);
DECLARE sql_stmt VARCHAR(500);
DECLARE sql_stmt1 VARCHAR(500);
DECLARE v_TBSP_FREE_PAGES BIGINT;
DECLARE v_notFound INT DEFAULT 0;
DECLARE c_notFound CONDITION FOR SQLSTATE ‘02000’;
DECLARE v_no_data SMALLINT DEFAULT 0;
DECLARE v_stmt1 VARCHAR(500);
DECLARE v_stmt2 VARCHAR(500);

DECLARE C1 CURSOR WITH HOLD FOR
SELECT a.TBSPACE, b.TBSP_FREE_PAGES FROM SYSCAT.TABLESPACES a, SYSIBMADM.TBSP_UTILIZATION b
WHERE a.TBSPACE = b.TBSP_NAME
AND a.TBSPACETYPE = ‘D’
AND TBSPACE NOT LIKE ‘%SYS%’
AND TBSPACE NOT LIKE ‘%USER%’
AND b.TBSP_FREE_PAGES > 100
AND a.TBSPACE NOT IN (‘MEDIUM_TSD’,’MEDIUM_TSI’);

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_no_data=1;

DECLARE CONTINUE HANDLER FOR c_notFound
SET v_notFound = 1;

OPEN C1;
FETCH C1 INTO v_TBSPACE,v_TBSP_FREE_PAGES;
WHILE ( v_notFound = 0 ) DO
SET sql_stmt = ‘ALTER TABLESPACE ‘ || v_TBSPACE || ‘ AUTORESIZE YES INCREASESIZE 5 PERCENT’;
SET sql_stmt1 = ‘ALTER TABLESPACE ‘ || v_TBSPACE || ‘ REDUCE (ALL ‘ || TRIM(CHAR(v_TBSP_FREE_PAGES – 100)) || ‘)’;
PREPARE v_stmt1 FROM sql_stmt;
EXECUTE v_stmt1;
COMMIT;
PREPARE v_stmt2 FROM sql_stmt1;
EXECUTE v_stmt2;
FETCH C1 INTO v_TBSPACE,v_TBSP_FREE_PAGES;
END WHILE;
CLOSE C1;
END
/

GRANT EXECUTE ON PROCEDURE REDUCE_DB_SIZE TO USER @USERNAME@
/

COMMIT
/

CONNECT RESET
/

— ———————————————————————————————————————————-
This procedure is not responsible for reducing the database backup size but it will also reduce the actual database size.
We can check the actual database size using the following command:

CALL GET_DBSIZE_INFO(<snapshot-timestamp>, <dbsize>, <dbcapacity>, <refresh-window>);
Where,
snapshot-timestamp – Output parameter of type TIMESTAMP that returns the time at which dbsize and dbcapacity were calculated.
dbsize – An output parameter of type BIGINT that returns the size of the database (in bytes)
dbcapacity –  An output parameter of type BIGINT that returns the database capacity (in bytes).
refresh-window – An input argument of type INTEGER that specifies the number of minutes until the cached values for database size and capacity are to be refreshed.
(-1 for default which is 30 minutes)

If there are any suggestions to improve it more. ALWAYS WELCOME. 🙂

 
Leave a comment

Posted by on October 15, 2013 in DB2

 

Tags: , ,