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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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 OlsenDBACommented:
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
Kent OlsenDBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
index

From novice to tech pro — start learning today.