RSS

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

15 Oct

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. 🙂

Advertisements
 
Leave a comment

Posted by on October 15, 2013 in DB2

 

Tags: , ,

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: