Monthly Archives: February 2011

You are browsing the site archives by month.

DB2 Table maintenance automated

I have done several previous posts on this subject but I would like to bring them together here, and show you the process that I run to maintain my tables when I want too, not when DB2 decides is best. So I use my four processes that I have built so far from the articles Automated DB2 Table Reorganisation, Automated DB2 Index Reorganisation, Automated DB2 Runstats and Automated DB2 Stored procedure Rebinding and write a wrapper stored procedure to encapsulate them all.

The wrapper stored procedure is fairly simple:

CREATE PROCEDURE DB_MAIN.AUTOMATED_MAINTAIN_GLOBAL_SCHEMA()
LANGUAGE SQL
BEGIN
    CALL DB_MAIN.AUTOMATED_REORG('S','GLOBAL');
    CALL DB_MAIN.AUTOMATED_REORG_INDEX('S','GLOBAL');
    CALL DB_MAIN.AUTOMATED_RUNSTATS_TABLE('S', 'GLOBAL');
    CALL DB_MAIN.REBIND_PROCEDURES ('GLOBAL');
END

This is the stored procedure that runs and keeps in line the GLOBAL schema that we have, obviously you can change GLOBAL to anything that you like. The stored procedure can then be used to automate anyway you like, the only gotcha is that the user that runs the script has to have the authority to carry out the commands. This means you can run it from a query window in the likes of Control Centre or IBM Data Studio, a bash script or a file that is run by the db2 command.

As for the schedule that we employ this is still under some testing, but due to the fact that you can never tell how long a offline table reorganisation will take and that once it has started there is no way to pause or stop it, unlike the online table reorganisations I have the 4 R’s (Reorg Table, Reorg Index, Runstats and Rebind) process running on each schema every other week. The tables that are identified for processing should only be the ones that have been identified by the inbuilt assessment stored procedures SYSPROC.REORGCHK_TB_STATS and SYSPROC.REORGCHK_IX_STATS or the tables that have been identified in table in the runstats part of the process, this should shorted the time taken to process, but I have found in testing the timings the reorganisations are greatly affected by the other processes that are going on in the instance / box at the time.

DB2 Rebind Stored Procedures Automation

As part of the ongoing theme of automating the maintenance of my DB2 servers I needed some way of after Automatic Reorganisation of tables Automatic Reorganisation of indexes and Automatically running statistics of rebinding the stored procedures to pick up potentially new execution plans.

When you first run a stored procedure it picks up an execution plan in its package, after that it will not be reassesed until the stored procedure is rebound, dropped and recreated or there is a configuration parameter that can be set (SET CURRENT QUERY OPTIMIZATION). If you have reorganised and runstat’ed your tables then it is all for nothing if your stored procedures will not pick up on the fact that things have changed. Dynamic SQL will obviously pick up on the change.

The following code I know I found here and it allows you to rebind the stored procedures. It uses REBIND_ROUTINE_PACKAGE to carry out the rebinding with a full IBM write up can be found here:

CREATE PROCEDURE DB_MAIN.REBIND_PROCEDURES(IN IN_SCHEMA VARCHAR(128))
MODIFIES SQL DATA NO EXTERNAL ACTION DETERMINISTIC
BEGIN  FOR thisProc    AS
 SELECT SPECIFICNAME
 FROM SYSCAT.ROUTINES
 WHERE ROUTINESCHEMA = IN_SCHEMA
       AND   ROUTINETYPE = 'P'
       AND   SPECIFICNAME != 'REBIND_PROCEDURES'
       ORDER BY ROUTINENAME  
DO      
CALL SYSPROC.REBIND_ROUTINE_PACKAGE('SP', IN_SCHEMA || '.' || SPECIFICNAME,'CONSERVATIVE');  
END FOR;
END

In my next post after my holiday I will go through how I combine this post and my previous three posts to do weekly maintenance.

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.

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