Hi,
don't know what platform you're working on, but on Linux/Unix/Window$ you'll find the command "reorgchk" which gives you some overview about the consistence of the tables and their depending indexes.
Regards,
Bernd
-
Example:
-
-
shell $ db2 reorgchk on table db2inst1.test
-
-
Doing RUNSTATS ....
-
-
-
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: DB2SVC03.TEST
-
DB2SVC03 TEST 2 0 1 1 - 48 0 - 100 ---
-
----------------------------------------------------------------------------------------
-
-
Index statistics:
-
-
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
-
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS -1) * (INDEXPAGESIZE - 96) > MIN(50, (100- PCTFREE))
-
F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (indexPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
-
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
-
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20
-
-
SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-
-------------------------------------------------------------------------------------------------
-
(here would be the list of indexes)
-
-------------------------------------------------------------------------------------------------
-
-
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.
-
