DB2 REORG INDEX Question

DB2 10.5 on Windows

I have written a stored proc that checks the condition of all my indexes and if they need to be REORGed then I do it.  Here is how I check the indexes per schema.

     
      CALL REORGCHK_IX_STATS('S', 'Schema1');

     INSERT INTO MAINT.INDEXSTATS_TABLE( 
          TABLE_SCHEMA,TABLE_NAME,INDEX_SCHEMA,INDEX_NAME,INDCARD, NLEAF,REORG_FLG)
     SELECT 
          TABLE_SCHEMA,TABLE_NAME,INDEX_SCHEMA,INDEX_NAME,INDCARD, NLEAF, REORG
     FROM SESSION.IX_STATS WHERE REORG LIKE '%*%';

Open in new window


When I look at the index stats table I created, I see this.

     
ID       Schema       Table                       INDEX                                INDCARD   NLEAF     REORG
534    SCHEMA1   SESSION_TOKEN	SESSION_TOKEN__IDX     530320	4073	     *----

Open in new window


So I run this.

     INSERT INTO MAINT.INDEXSTATS_COMMANDS (SQL_COMMAND)
     SELECT DISTINCT
               'REORG INDEXES ALL FOR TABLE ' 
     		|| RTRIM(SUBSTR(TABLE_SCHEMA,1,11)) || '.' || RTRIM(SUBSTR(TABLE_NAME,1,31)) 
     		|| ' ALLOW WRITE ACCESS ' 
     		|| CASE 
     			WHEN SUBSTR(REORG_FLG,4,1) = '*' 
     				THEN 'CLEANUP ONLY' 
     			WHEN SUBSTR(REORG_FLG,5,1) = '*' 
     				THEN 'CLEANUP ONLY PAGES' 
     			ELSE '' END 
     		|| ';' 
     FROM  MAINT.INDEXSTATS_TABLE 
     WHERE INDCARD > 10 AND  NLEAF > 10
     WITH UR;

Open in new window


To create and run this command.

REORG INDEXES ALL FOR TABLE SCHEMA1.SESSION_TOKEN ALLOW WRITE ACCESS ;

Open in new window


It runs and seems to work, but when you check the exact same index using REORGCHK_IX_STATS five seconds later,  it has not changed at all.  All the stats are the same.  If an index has a REORG status of *----- does it not need to be REORGed?  I am somewhat confused.

Due to this, the same 50 tables are being REORGed every night.

Any help would be great.

Any Merry Christmas and Hapy Holidays to everyone!!!

Jim
Jim YoumansSr Database AdministratorAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
How long does RUNSTATS run?  If it doesn't interfere with anything, it doesn't hurt to run it.  Once a week should be fine.  Once a month is probably overkill if AUTO_MAINT is set

  https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0011479.html
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Jim,

Run RUNSTATS after the reorg.

Also, is AUTO_MAINT turned on for the database?

Kent
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
Just found this at IBM.COM

Output from the REORGCHK command suggests that performance can be improved by reorganizing a table or its indexes
In some cases, the reorgchk utility might recommend table reorganization, even after a table reorg operation is performed.


How can I tell when an index really needs to be reorged?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Unless the data is wildly dynamic (LOTS of inserts, deleted, and/or updates) it would take a really bad design for a reorg to be needed daily.  I'd set a regular schedule for updating the statistics (RUNSTATS) and a less frequent schedule for a REORG.

DB2 didn't used to automatically update statistics and DBA intervention was required to do so.  That changed some years back and most modestly size databases benefit from AUTO_MAINT being set to YES.  With normal use, running RUNSTATS would be fairly uncommon.  The frequency for a REORG would be dependent upon several things, but they should be relatively rare.
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
Thanks for the info.  I still get the same thing after a reorg and run stats so I think it is just a design flaw.  I will have to find the tables that always show up and then put them on a separate weekly or bi-weekly job I guess.

I am doing run stats once a week right now.  There are only a dozen or two that get changed a lot.  Maybe I should just do those once a week?

Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.