Automated DB2 Reorganisation

This code has now been replaced with Version 2

At my workplace there is no time for the automated reorganisation through the automated maintenance processes, and there is a need for a little bit of intelligence when it comes to actually carrying out the reorgs. DB2 9.7 comes with an inbuilt command that will allow you to assess the tables and if they actually need reorganisation, more info can be found here but a brief overview of the command is below:

CALL SYSPROC.REORGCHK_TB_STATS(<S or T>, <Schema Name or Table Name>)

So calling this will assess a table or tables in a schema and used in combination with the sessions table can be combined to powerfully assess the tables that need reorganisation. The table that will be available in your session is below:

SELECT *
FROM SESSION.TB_STATS

The tables that will need reorganisation will be those where the column REORG has a * in it. The REORG column is a CHAR(3) and if it contains a * then the table has failed one of the assessments the stored procedure carries out. So using the following will identify them:

SELECT *
FROM SESSION.TB_STATS 
WHERE REORG LIKE '%*%'

The other thing that this command and table allows you to see is the partitions on the partition tables separately to the overall table this allows you to do clever things with running online and offline reorganisation. Equally by identifying the partitions that need reorging means that although you can only do these offline, it does mean there is a lot less to do and so should work quicker.

Below is the code that I have come up with and tested on my works database and is currently in the production system performing maintenance every Sunday. First up is a note to compile this code you are going to have to fool DB2 into thinking that the table SESSION.TB_STATS is actually available, to do this you will have to run the SP SYSPROC.REORGCHK_TB_STATS with viable values in. I have written the following code to be a wrapper and simple to use, but the elements into can be reused without the wrapper.

To accomplish the automated reorganisation I have designed a process that involves a table, a view and a number of stored procedures. It could be done with less but being cautious I like to record everything that goes on for auditing purposes. I am going to post the code in reverse order, first of all the wrapper SP that is called :

DROP PROCEDURE DB_MAIN.AUTOMATED_REORG(VARCHAR(1), VARCHAR(255))
GO
CREATE PROCEDURE DB_MAIN.AUTOMATED_REORG(IN IN_RUN_FLAG VARCHAR(1), IN IN_SCHEMA_TABLE VARCHAR(255))
DYNAMIC RESULT SETS 1
LANGUAGE 
SQLBEGIN    
----------------------------------------------------------------------------------------------    
--Description: For the supplied table or schema it will chose the appropriate action and assess    
--             if the table / schema need any reorging or not.    
--Parameters: IN_RUN_FLAG: T if you want a table assessing, S if you want a schema    
--            IN_SCHEMA_TABLE: A schema and table (e.g. CHIPS.TRANSACTIONS) or just a schema (e.g. CHIPS)    
--Invocation Examples: To run a schema: CALL DB_MAIN.REORG_TABLE_SCHEMA('S', 'CHIPS')    
--                     To run a table: CALL DB_MAIN.REORG_TABLE_SCHEMA('T', 'CHIPS.TRANSACTIONS')    
--Created by Philip Carrington for more info mail philip dot carrington at gmail dot com    
----------------------------------------------------------------------------------------------
 IF (IN_RUN_FLAG='S') THEN        
--Assess a Schema        
    CALL DB_MAIN.AUTOMATED_REORG_SCHEMA(IN_SCHEMA_TABLE);    
ELSE        
--Assess a Table        
    CALL DB_MAIN.AUTOMATED_REORG_TABLE(IN_SCHEMA_TABLE);    
END IF;
END

As you can see from the code the stored procedure then calls two further stored procedures, these do the identification of the tables or the partitions that need reorging, below is one of the Stored Procedures the other can be found in the downloadable file:

DROP PROCEDURE DB_MAIN.AUTOMATED_REORG_SCHEMA(VARCHAR(255))
GO
CREATE PROCEDURE DB_MAIN.AUTOMATED_REORG_SCHEMA(IN IN_SCHEMA_TABLE VARCHAR(255))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE MIN_REORG_ORDER INTEGER;
DECLARE MAX_REORG_ORDER INTEGER;
DECLARE REORG_TAB_NAME VARCHAR(200);
DECLARE REORG_TAB_SCHEMA VARCHAR(200);
DECLARE REORG_PART_NAME VARCHAR(200);
DECLARE PART_CHECK INTEGER;

--Check the schema or table that need checking
CALL SYSPROC.REORGCHK_TB_STATS('S',IN_SCHEMA_TABLE);
--Insert the data into the table.
--Dont bother looking at the volitile tables e.g. STAGE, TEMP, PUMP tables
INSERT INTO DB_MAIN.AUTOMATED_REORG_RECORD(
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_PARTITION,
    PARTITION_REASON,
    IDENTIFIED_TIMESTAMP
)
SELECT TABLE_SCHEMA,
    TABLE_NAME,
    DATAPARTITIONNAME,
    'S',
    CURRENT_TIMESTAMP
FROM SESSION.TB_STATS
WHERE REORG LIKE '%*%'
   AND TABLE_NAME NOT LIKE '%_STAGE%'
   AND TABLE_NAME NOT LIKE '%_PUMP%'
   AND TABLE_NAME NOT LIKE '%_TEMP%';

--Start processing the data 
--Get the minimum and maximum to loop through. 
SET MIN_REORG_ORDER = (SELECT MIN(REORG_ORDER) 
                       FROM DB_MAIN.AUTOMATED_REORG_VIEW 
                       WHERE TABLE_SCHEMA = IN_SCHEMA_TABLE
                            AND IDENTIFIED_TIMESTAMP BETWEEN CURRENT_TIMESTAMP - 12 HOURS AND CURRENT_TIMESTAMP);
SET MAX_REORG_ORDER = (SELECT MAX(REORG_ORDER)
                       FROM DB_MAIN.AUTOMATED_REORG_VIEW
                       WHERE TABLE_SCHEMA = IN_SCHEMA_TABLE
                            AND IDENTIFIED_TIMESTAMP BETWEEN CURRENT_TIMESTAMP - 12 HOURS AND CURRENT_TIMESTAMP);

WHILE (MIN_REORG_ORDER <= MAX_REORG_ORDER) DO
--Get the values to populate vars to run the reoorgs        
SET REORG_TAB_SCHEMA = (SELECT TABLE_SCHEMA
                        FROM DB_MAIN.AUTOMATED_REORG_VIEW
                        WHERE TABLE_SCHEMA = IN_SCHEMA_TABLE
                             AND IDENTIFIED_TIMESTAMP BETWEEN CURRENT_TIMESTAMP - 12 HOURS AND CURRENT_TIMESTAMP
                                    AND REORG_ORDER = MIN_REORG_ORDER);
SET REORG_TAB_NAME = (SELECT TABLE_NAME
                       FROM DB_MAIN.AUTOMATED_REORG_VIEW
                       WHERE TABLE_SCHEMA = IN_SCHEMA_TABLE
                             AND IDENTIFIED_TIMESTAMP BETWEEN CURRENT_TIMESTAMP - 12 HOURS AND CURRENT_TIMESTAMP
                                    AND REORG_ORDER = MIN_REORG_ORDER);
SET REORG_PART_NAME = (SELECT TABLE_PARTITION
                        FROM DB_MAIN.AUTOMATED_REORG_VIEW
                        WHERE TABLE_SCHEMA = IN_SCHEMA_TABLE
                             AND IDENTIFIED_TIMESTAMP BETWEEN CURRENT_TIMESTAMP - 12 HOURS AND CURRENT_TIMESTAMP
                                    AND REORG_ORDER = MIN_REORG_ORDER);
--Make sure that the table identified isnt a partitioned         
IF (LTRIM(RTRIM(REORG_PART_NAME)) = '') THEN            
--Check the table is not patitioned
            SET PART_CHECK = (SELECT COALESCE(SUM(CASE WHEN (DATAPARTITIONNAME IS NOT NULL) THEN 1 ELSE 0 END), 0)                                  FROM SYSIBM.SYSDATAPARTITIONS                                  WHERE DATAPARTITIONID <> 0                                    AND TABSCHEMA = REORG_TAB_SCHEMA                                    AND TABNAME = REORG_TAB_NAME);
            IF (PART_CHECK = 0) THEN
                     --Table is not partitioned - online reorg
                                            CALL DB_MAIN.AUTOMATED_REORG_TABLE_ONLINE(REORG_TAB_SCHEMA, REORG_TAB_NAME, 'R');
             END IF;
ELSE
            --Table is partitioned
                CALL DB_MAIN.AUTOMATED_REORG_TABLE_PARTITION_OFFLINE(REORG_TAB_SCHEMA, REORG_TAB_NAME,REORG_PART_NAME, 'R');        
END IF;
--Dont forget to increment            
            SET MIN_REORG_ORDER = MIN_REORG_ORDER + 1;
            SET REORG_TAB_SCHEMA = '';
            SET REORG_TAB_NAME = ''; 
           SET REORG_PART_NAME = '';
  END WHILE;
END
GO

You need to make sure that the table that you are trying to reorg is not partitioned hence the section counting the partitions, this is because partitioned tables will report one row as the table and schema and give no hint that is is partitioned. As I like to have stored procedures that are repeatable then the following will do the actual reorganisation. Theses are then reusable if you don’t supply the “record” flag. This is only the offline reorganisation for the partitioned tables but the code for the other stored procedure can be found in the file:

DROP PROCEDURE DB_MAIN.AUTOMATED_REORG_TABLE_PARTITION_OFFLINE(VARCHAR(255), VARCHAR(255), VARCHAR(255), CHAR(1))
GO
CREATE PROCEDURE DB_MAIN.AUTOMATED_REORG_TABLE_PARTITION_OFFLINE(IN IN_TABLE_SCHEMA VARCHAR(255), IN IN_TABLE_NAME VARCHAR(255), IN IN_PARTITION_NAME VARCHAR(255), IN IN_RECORD CHAR(1))
LANGUAGE SQL
BEGIN   
DECLARE REORG_STRING VARCHAR(1000);

SET REORG_STRING = 'REORG TABLE ' || LTRIM(RTRIM(IN_TABLE_SCHEMA)) || '.' || IN_TABLE_NAME || ' ALLOW NO ACCESS ON DATA PARTITION ' || IN_PARTITION_NAME;

IF (IN_RECORD = 'R') THEN         
   UPDATE DB_MAIN.AUTOMATED_REORG_RECORD        
   SET REORG_TYPE = 'OFFLINE',            
       REORG_COMMAND = REORG_STRING,            
       REORG_TIMESTAMP = CURRENT_TIMESTAMP        
   WHERE TABLE_SCHEMA = IN_TABLE_SCHEMA            
     AND TABLE_NAME = IN_TABLE_NAME            
     AND TABLE_PARTITION = IN_PARTITION_NAME            
     AND IDENTIFIED_TIMESTAMP BETWEEN CURRENT_TIMESTAMP - 12 HOURS AND CURRENT_TIMESTAMP;                
COMMIT;   
END IF;

CALL SYSPROC.ADMIN_CMD(REORG_STRING);
END
GO

One of the most important parts of this whole operation is the view and table where the auditing is stored and tables to process are identified. The reason that I use the identified time in the last 12 hours is because there should not really be any need to (hopefully) run this more than once a week, maybe your shop is different but at mine it will currently do.

DROP TABLE DB_MAIN.AUTOMATED_REORG_RECORD
GO
CREATE TABLE DB_MAIN.AUTOMATED_REORG_RECORD(    
TABLE_SCHEMA            VARCHAR(255) NOT NULL,
    TABLE_NAME              VARCHAR(255) NOT NULL,
    TABLE_PARTITION         VARCHAR(255) NOT NULL,
    PARTITION_REASON        CHAR(1) NOT NULL,
     IDENTIFIED_TIMESTAMP    TIMESTAMP NOT NULL,
    REORG_TYPE              VARCHAR(7),
    REORG_COMMAND           VARCHAR(1000),
    REORG_TIMESTAMP         TIMESTAMP,
	CONSTRAINT PK_DMRR_TABLESCHEMANAMEDATETIME PRIMARY KEY(TABLE_SCHEMA,TABLE_NAME,TABLE_PARTITION,IDENTIFIED_TIMESTAMP)
)IN DB_MAIN_TS
COMPRESS YES
GO
DROP VIEW DB_MAIN.AUTOMATED_REORG_VIEW
GO
CREATE VIEW DB_MAIN.AUTOMATED_REORG_VIEW AS (   
SELECT ROW_NUMBER() OVER (order by IDENTIFIED_TIMESTAMP, TABLE_PARTITION ASC) as REORG_ORDER,
           A.TABLE_SCHEMA,
           A.TABLE_NAME,
           A.TABLE_PARTITION,
           A.IDENTIFIED_TIMESTAMP
    FROM DB_MAIN.AUTOMATED_REORG_RECORD A    
)
GO

I hope that between the code snippets here and the code in the file supplied and if you want to get in contact or leave a comment please feel free too. I have had too load the file on here as a .doc as that is the only test file type that it would let me upload

DISCLAIMER: As stated at the top of the blog use this code in your production systems at your own peril. I have tested and know it works on my systems, please test and check it works on yours properly as reorganising tables can potentially dangerous.

FILE WITH CODE IN: DB2_Automated_Reorg_SP_V_T_DCP

4 Thoughts on “Automated DB2 Reorganisation

  1. Amazing article, lots of intersting things to digest. Very informative

  2. Thiru on May 6, 2011 at 1:07 am said:

    Really fantastic.

  3. richard crider on October 18, 2011 at 4:11 pm said:

    this is very good. one question. Have you run into a situation where you reorg a table and run runstats on the table and then run REORGCHK_TB_STATS again and it still shows that the table needs to be reorged?

    • Hi,
      So I have been experimenting and researching this a lot as I can’t seem to find a straight answer on developer works, forums and documentation and what I am finding is that you need to make sure that the table is runstat’ed correctly after the reorg as the REORGCHK_TB_STATS stored procedure uses the statistics and histograms from the runstats to identify what needs doing.

      Hope this helps would love to know your experiences of it

      Phil

Leave a Reply

Post Navigation