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
FROM SESSION.IX_STATS WHERE REORG LIKE '%*%';
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 *----
So I run this.
INSERT INTO MAINT.INDEXSTATS_COMMANDS (SQL_COMMAND)
'REORG INDEXES ALL FOR TABLE '
|| RTRIM(SUBSTR(TABLE_SCHEMA,1,11)) || '.' || RTRIM(SUBSTR(TABLE_NAME,1,31))
|| ' ALLOW WRITE ACCESS '
WHEN SUBSTR(REORG_FLG,4,1) = '*'
THEN 'CLEANUP ONLY'
WHEN SUBSTR(REORG_FLG,5,1) = '*'
THEN 'CLEANUP ONLY PAGES'
ELSE '' END
WHERE INDCARD > 10 AND NLEAF > 10
To create and run this command.
REORG INDEXES ALL FOR TABLE SCHEMA1.SESSION_TOKEN ALLOW WRITE ACCESS ;
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!!!