1. I did a reorgchk, and found that F2 on table and F4 on index
indicates a reorg is necessary.
2. Did a reorg.
3. Again I do a reorgchk, the report is the same as in Step 1
4. I do a runstats on all columns and key columns with distribution and
indexes all
5. reorgchk
6. reorg
7. reorgchk - no improvement!! Still the stars are glittering!!
Please give your interpretation and steps. Thanks for your help!!!
------------------------------------------------------------------
reorgchk update statistics on table claims.THEADER
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
----------------------------------------------------------------------------------------
CLAIMS THEADER 525358 0 3e+005 3e+005 -
7.24e+008 0 68 99 -*-
----------------------------------------------------------------------------------------
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM
EMPTY LEAFS) * INDEXPAGESIZE) > 50
F6: (100 - PCTFREE) * ((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
-------------------------------------------------------------------------------------------------
Table: CLAIMS.THEADER
CLAIMS IND1 525358 994 0 3 15 0 43374
60 84 16 0 0 *----
CLAIMS IND2 525358 2566 0 3 17 0 357758
68 96 5 0 0 *----
CLAIMS IND3 525358 807 0 3 9 0 3
98 79 28 0 0 -----
CLAIMS IND4 525358 807 0 3 9 0 3
98 79 28 0 0 -----
CLAIMS IND5 525358 829 0 3 15 0 4748 92 80
21 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.
SQLCODE: 0
---------------------------------- Script
-----------------------------------
Untitled1
-----------------------------------------------------------------------------
reorg table claims.THEADER allow no access
DB20000I The REORG command completed successfully.
SQLCODE: 0
---------------------------------- Script
-----------------------------------
Untitled1
-----------------------------------------------------------------------------
reorgchk update statistics on table THEADER
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
----------------------------------------------------------------------------------------
CLAIMS THEADER 525358 0 3e+005 3e+005 -
7.24e+008 0 68 100 -*-
----------------------------------------------------------------------------------------
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM
EMPTY LEAFS) * INDEXPAGESIZE) > 50
F6: (100 - PCTFREE) * ((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
-------------------------------------------------------------------------------------------------
Table: CLAIMS.THEADER
CLAIMS IND1 525358 994 0 3 15 0 43374
60 84 16 0 0 *----
CLAIMS IND2 525358 2566 0 3 17 0 357758
68 96 5 0 0 *----
CLAIMS IND3 525358 806 0 3 9 0 3
98 79 28 0 0 -----
CLAIMS IND4 525358 806 0 3 9 0 3
98 79 28 0 0 -----
CLAIMS IND5 525358 829 0 3 15 0 4748
92 80 21 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.
SQLCODE: 0
