Automated DB2 Index Reorganisation

In my last post I typed about reorganisation of tables DB2, in this post I am going to talk about the additional code I have developed that will reorganise indexes. Again DB2 9.7 comes with an in built command for assessing the indexes on a table or the tables in a schema. As per last post I am not going to go into detail that can be found here, and again a brief overview is below:

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

As per the REORGCHK_TB_STATS you can access the output of stored procedure by running the following query:

SELECT *
FROM SESSION.IX_STATS

In this table there are columns for identifying the tables, indexes and partitions the indexes reside on, the ones that have been identified for reorganisation will have one or more * in the REORG column found at the end of the table. I have done a little research (probably room for more) on the tables that I have available to me at work, and it seems that  on a partitioned tables it will identify all the indexes that cover all the partitions that need reorganising. It is possible to manually write a command and reorganise  just the indexes that reside on the partitions. As what I actually need to do is reorganise automatically then I opted for a method that was basically a carbon copy of the table reorganisation methods.

Using the following SQL it is possible to identify the indexes that have been identified by stored procedure that need processed:

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

Like in the table reorganisation code I will give you a light overview of what I have got working and include all the code at the end of the post. As per last time before you compile any of these stored procedures you will need to fool DB2 into thinking that the SESSION.IX_STATS table actually exists, to do this you will have too run the SYSPROC.REORGCHK_IX_STATS on viable criteria.

The other system object that I will be using is SYSIBMADM.SNAPTAB_REORG, the full IBM run-down on this view can be found here. A brief overview is that it will show the tables that are currently being reorganised. Why does this matter, well if you try to reorganise the index while the table reorganisation is still going on then it will result in errors, this mostly needs to be protected against when tables are having ONLINE reorganisations carried out on them as they are asynchronous. This view will show you what is going on reorg wise and be able to be monitored for them finishing. You can use SYSIBMADM.SNAPTAB_REORG columns REORG_STATUS and REORG_COMPLETION to assess if the index reorganisation should go ahead.

Using the SYSPROC.REORGCHK_IX_STATS stored procedure and the SESSION.IX_STATS table it is possible to identify the index’s that need reorganisation, then loop round the ones that need doing, I have used the following code to make sure that I don’t try to reorganise an index on a table that is currently being reorganised, this allows me to loop over the “busy” ones and carry on with other index reorganisation:

SELECT CASE WHEN ((REORG_STATUS = 'COMPLETED') AND (REORG_COMPLETION = 'SUCCESS')) THEN 1  --Table Reorg Success
            WHEN ((REORG_STATUS = 'COMPLETED') AND (REORG_COMPLETION = 'FAIL')) THEN 2 --Table Reorg Fail       
            ELSE 3 -- Still going
      END                                       
FROM SYSIBMADM.SNAPTAB_REORG                                       
WHERE (TABNAME, TABSCHEMA, REORG_START) IN (SELECT TABNAME,                                                                                            
                                                   TABSCHEMA,                                                                                            
                                                   MAX(REORG_START) MAX_REORG_START                                                                                   
                                            FROM SYSIBMADM.SNAPTAB_REORG                                                                                   
                                            GROUP BY TABNAME,                                                                                            
                                                     TABSCHEMA)                                            
       AND LTRIM(RTRIM(TABSCHEMA)) || '.' || LTRIM(RTRIM(TABNAME)) = REORG_TAB_SCHEMA || '.' || REORG_TAB_NAME);

I get the MAX(REORG_START) just in case there has been more than one in a day. If you download and view the code you will see how the above statement a table and a view allow me to cycle over and come back to busy indexes. I am not going to go into the code  in as much depth as the last article as it is very much the same with reusable stored procedures that can be used all together or separately.  So please download the code and take a look, and see if it can help you.

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 index’s can potentially dangerous. 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_Reorg_Indexes_SP_V_T_DCP