By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,558 Members | 1,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,558 IT Pros & Developers. It's quick & easy.

reorgchk reorg interpretation

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Ian
hi****@gmail.com wrote:
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


Did you bother to *read* the output from REORGCHK:

"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."

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.