You can use data in the SYSIBM.SYSINDEXSPACESTATS table to determine when to reorganize
an index.
Procedure
To investigate whether to reorganize indexes:
-
Reorganize the indexes if any of the following conditions are true in the
SYSIBM.SYSINDEXSPACESTATS catalog table:
- REORGPSEUDODELETES/FLOAT(TOTALENTRIES) > 10% in a non-data sharing
environment, or REORGPSEUDODELETES/TOTALENTRIES > 5% in a data sharing environment.
- REORGINSERTS/FLOAT(TOTALENTRIES) > 25%
- REORGDELETES/FLOAT(TOTALENTRIES) > 25%
- REORGAPPENDINSERT/FLOAT(TOTALENTRIES) > 20%
- EXTENTS > 254
-
Reorganize the indexes if any of the following conditions are true:
- Advisory REORG-pending state (AREO*) as a result of an ALTER statement.
- Advisory REBUILD-pending state (ARBDP) as a result an ALTER statement.