• About Dangerous DBA
  • Table of Contents
Dangerous DBA A blog for those DBA's who live on the edge

Automated DB2 Index Reorganisation

February 2, 2011 8:57 pm / 4 Comments / dangerousDBA

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

Posted in: DB2, DB2 Administration, DB2 built in Views, DB2 Built-in Stored Procedures, DB2 Maintenance, IBM, Reorg Index, Reorganise Index, SYSIBMADM.SNAPTAB_REORG

4 Thoughts on “Automated DB2 Index Reorganisation”

  1. Steve on February 18, 2011 at 9:50 pm said:

    Hi Philip,

    Good article. I think you could use TRIM(TABSCHEMA) rather than LTRIM(RTRIM(TABSCHEMA)), it works fine in my v9.7 LUW.

    Steve

    Reply↓
    • philipcarrington on February 19, 2011 at 8:57 pm said:

      True, sometimes I am a little old school though, brought up doing ltrim rtrim so it kind of sticks.

      Reply↓
  2. Nick Brennan on May 9, 2011 at 3:25 pm said:

    Interesting stuff. I’ve just come across SNAPTAB_REORG myself, having a play around with it. Thanks for sharing your code 🙂

    One question – while SNAPTAB_REORG can be used to verify whether a reorg is still running on a table and it’s status, and ideas where to check the status of a reorg on an index?

    Reply↓
    • philipcarrington on May 10, 2011 at 5:50 am said:

      Hi,
      Thank you for your question. I don’t think that there is unfortunately, when I was looking into this before when writing the original code I could not find anything.Though you can see in the db2diag log and the .nfy file entries when it is starting to and which index is being reorganised. I would happy to be proved wrong, but I don’t think there is.

      Cheers Phil

      Reply↓

Leave a Reply Cancel reply

Post Navigation

← Previous Post
Next Post →

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 757 other subscribers

Recent Posts

  • Self generating Simple SQL procedures – MySQL
  • Google Cloud Management – My Idea – My White Whale?
  • Position Tracker – The Stub – Pandas:
  • Position Tracker – The Stub
  • Position Tracker – In the beginning
  • Whats been going on in the world of the Dangerous DBA:
  • QCon London Day 1
  • Testing Amazon Redshift: Distribution keys and styles
  • Back to dangerous blogging
  • DB2 10.1 LUW Certification 611 notes 1 : Physical Design

Dangerous Topics

added functionality ADMIN_EST_INLINE_LENGTH Amazon Bootcamp colum convert data types db2 DB2 DB2 Administration DB2 Development db2advis db2licm Decompose XML Exam EXPORT Google IBM IBM DB2 LUW idug information centre infosphere LOAD merry christmas and a happy new year Recursive Query Recursive SQL Redshift Reorganisation Reorganise Reorganise Indexes Reorganise Tables Runstats sqlcode sql statement Stored Procedures SYSPROC.ADMIN_CMD Time UDF User Defined Functions V9.7 V10.1 Varchar XML XML PATH XMLTABLE

DangerousDBA Links

  • DB2 for WebSphere Commerce
  • My Personal Blog

Disclaimer:

The posts here represent my personal views and not those of my employer. Any technical advice or instructions are based on my own personal knowledge and experience, and should only be followed by an expert after a careful analysis. Please test any actions before performing them in a critical or nonrecoverable environment. Any actions taken based on my experiences should be done with extreme caution. I am not responsible for any adverse results. DB2 is a trademark of IBM. I am not an employee or representative of IBM.

Advertising

© Copyright 2021 - Dangerous DBA
Infinity Theme by DesignCoral / WordPress