The results of the REORGCHK calculations show what type of table or index reorganization might be needed. The goal is to resolve any out of bounds indicators using the least system resources.

If any of the table formulas are out of bounds, these are the F1, F2 and F3, then a full table and index reorganization would be required. This process consumes the most resource but will resolve all possible out of bounds conditions. The reorganized table should be stored in
a more efficient manner.

If the results of the calculation for F4 exceed the bounds set by the formula for a Cluster Index then the table and indexes should be reorganized. It is not uncommon for several non-clustered indexes to be flagged for the F4 calculation. These can be ignored.

If the results of the calculations for F1, F2, F3 and F4 do not exceed the bounds set by the formula and the results of the calculations for F5 or F6 do exceed the bounds set, then index reorganization is recommended. The reorganized indexes should be stored in a moreefficient manner.

If only the results of the calculation for F7 exceeds the bounds set, but the results of F1, F2, F3, F4, F5 and F6 are within the set bounds, then it is recommended that a cleanup of the indexes be done using the CLEANUP ONLY option of reorg indexes.

If the only calculation result to exceed the set bounds is that of F8, it is recommended that a cleanup of the pseudo empty pages of the indexes be done using the CLEANUP ONLY PAGES option of reorg indexes.

Example:

db2 reorgchk current statistic on table db2inst1.project

Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA.NAME                     CARD     OV     NP     FP ACTBLK    TSIZE  F1  F2  F3 REORG
----------------------------------------------------------------------------------------
Table: DB2INST1.PROJECT
                                  20      0      1      1      -     1340   0   - 100 ---
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20

SCHEMA.NAME                 INDCARD  LEAF ELEAF LVLS  NDEL    KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD  F4  F5  F6  F7  F8 REORG 
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: DB2INST1.PROJECT
Index: DB2INST1.PK_PROJECT
                                 20     1     0    1     0      20            6             6               1174                1174 100   -   -   0   0 -----
Index: DB2INST1.XPROJ2
                                 20     1     0    1     0      17            6             6               1174                1174 100   -   -   0   0 ---*-
--------------------------------------------------------------------------------------------------------------------------------------------------------------

CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG.  Specify the most important index for REORG sequencing.

Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table.