DB2 Automated Table Runstats

DB2 already offers a method of doing this but in my experience (short but quite varied), and on the recommendations of an outside consultant with much expertise  we decided that using the DB2 Health Monitor to monitor the tables and decide itself when to carry out the runstats on tables was not turning out to be the best idea. This was especially evident when one table with XML data in it was always being runstat’ed constantly for some reason.

When you turn on AUTO_RUNSTATS and AUTO_MAINT DB2 uses the DB2HMON process to monitor and decide which tables to runstat, the gotcha here is that even if you turn the health monitor off DB2 will still spawn the process. I have seen the DB2HMON process taking up too 50% of the RAM on some of my DB2 instances. The other upshot is that while this is taking place it can lead to queries or other processes being locked out.

To this end I have designed a method that will work with my previous postings on Automated Table ReorganisationAutomated Table Index Reorganisation and as a process on its own. I use the SYSIBM.SYSTABLES table because it contains a column that will tell you when the last time the table had its statistics run on it STATS_TIME. Using this and a meta table it is possible to create a much more controllable process.

First of all you need a table to hold information on how often you would like a tables statistics to be run, not when DB2 decides.  So something like this will do :

CREATE TABLE DB_MAIN.AUTOMATED_RUNSTATS(
    TABLE_SCHEMA        VARCHAR(255) NOT NULL,
    TABLE_NAME          VARCHAR(255) NOT NULL,
    NO_DAYS             INTEGER NOT NULL,
    NO_HOURS            INTEGER NOT NULL,
    CONSTRAINT PK_DMAR_RUNSTATSID PRIMARY KEY(TABLE_SCHEMA, TABLE_NAME)
) IN DB_MAIN_TS

This enables you to identify the tables that you want to run the stats on and then set the frequency that you which them to run at. You can then use the this table in combination with SYSIBM.SYSTABLES in a view to work out what needs to be done at any given time something like this:

CREATE VIEW DB_MAIN.AUTOMATED_RUNSTATS_ORDER AS (
    SELECT ROW_NUMBER() OVER (order by IDENTIFIED_TIMESTAMP, TABLE_SCHEMA
) as RUNSTATS_ORDER,
            TABLE_SCHEMA,
            TABLE_NAME,
            IDENTIFIED_TIMESTAMP    
      FROM (SELECT A.TABLE_SCHEMA,
                   A.TABLE_NAME,
                   CURRENT_TIMESTAMP AS IDENTIFIED_TIMESTAMP
            FROM DB_MAIN.AUTOMATED_RUNSTATS A INNER JOIN SYSIBM.SYSTABLES B ON A.TABLE_SCHEMA = B.CREATOR
                                                                                    AND A.TABLE_NAME = B.NAME
            WHERE B.STATS_TIME + A.NO_DAYS DAYS + A.NO_HOURS HOURS <= CURRENT_TIMESTAMP
            UNION
             SELECT DISTINCT TABLE_SCHEMA,
                             TABLE_NAME,
                            CURRENT_TIMESTAMP AS IDENTIFIED_TIMESTAMP
            FROM DB_MAIN.AUTOMATED_REORG_RECORD
            WHERE REORG_COMMAND IS NOT NULL
                        AND DATE(IDENTIFIED_TIMESTAMP) = CURRENT_DATE
            UNION
            SELECT DISTINCT TABLE_SCHEMA,
                             TABLE_NAME,
                            CURRENT_TIMESTAMP AS IDENTIFIED_TIMESTAMP
            FROM DB_MAIN.AUTOMATED_REORG_INDEX_RECORD
            WHERE REORG_COMMAND IS NOT NULL
                        AND DATE(IDENTIFIED_TIMESTAMP) = CURRENT_DATE
        )
)

As stated before this process is designed to work with the Automated Table Reorganisation and Automated Table Index Reorganisation from my previous posts. There is no point reorganising a table and its indexes with out running the stats as DB2 will not have the correct information to work from. As I am only using the tables that I know have had the reorgs done on them I have no need to check that the reorgs have finished, but an improvement for the future would be to have some way to monitor this. I would have done with this incarnation but I found no record of the index reorganisations apart from in the <instance>.nfy file.

The following stored procedure uses SYSPROC.ADMIN_CMD to run the runstats command and execute the main part of this task as well as recording the running, I like having an audit trail.

CREATE PROCEDURE DB_MAIN.RUNSTATS  (IN IN_TABLESCHEMA VARCHAR(100), IN IN_TABLENAME VARCHAR(100), IN IN_TIMESTAMP TIMESTAMP, IN IN_RECORD CHAR(1))
LANGUAGE 
SQLBEGIN    
DECLARE REORGSTRING VARCHAR(255);

SET REORGSTRING = 'RUNSTATS ON TABLE ' || LTRIM(RTRIM(IN_TABLESCHEMA)) || '.' || LTRIM(RTRIM(IN_TABLENAME)) || ' ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS';

 IF(IN_RECORD = 'R') THEN
         UPDATE DB_MAIN.AUTOMATED_RUNSTATS_RECORD
         SET RUNSTATS_COMMAND = REORGSTRING
         WHERE TABLE_SCHEMA = IN_TABLESCHEMA
            AND TABLE_NAME = IN_TABLENAME
            AND IDENTIFIED_TIME = IN_TIMESTAMP;
    END IF;        
CALL SYSPROC.ADMIN_CMD(REORGSTRING);

 IF(IN_RECORD = 'R') THEN
         UPDATE DB_MAIN.AUTOMATED_RUNSTATS_RECORD
         SET FINISHED_TIME = CURRENT_TIMESTAMP
         WHERE TABLE_SCHEMA = IN_TABLESCHEMA
            AND TABLE_NAME = IN_TABLENAME
            AND IDENTIFIED_TIME = IN_TIMESTAMP;
    END IF;
 COMMIT;
END

As you can read from the code above the runstats is done “online”, and will allow data to be selected, inserted, update or deleted this will not aid the process though. This now allows me to control when the runstats happen on the server, not when DB2 decides and impeding the users experience.

The rest of the code that runs this process is very similar to that used in the other parts of my automation processes so there is very little point in going into it here. As per normal the code can be found in the file at the bottom of the post.

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. The file is a .doc only as that’s the only way I could get it uploaded onto wordpress, it should open fine like that, or knock the .doc off and it will open in your favourite text editor.

FILE WITH CODE IN:DB2_Automated_Runstats_SP_V_T_DCP

Leave a Reply

Post Navigation