473,247 Members | 2,862 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,247 software developers and data experts.

reorgchk reorg interpretation

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
1 4732
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

27
by: Raquel | last post by:
This question if for UDB on LUW. Suppose I take regular database backups of my database Monday: database backup <ts1> Tuesday: Tablespace reorged and imagecopied <ts2> Friday: database...
0
by: Amit | last post by:
Hi: I'm trying to manually compute the ISIZE in REORGCHK for indexes in DB2 v8 (fp 4). Based on the documentation in Administrative Guide for v8, for non lob fields ISIZE should be the...
5
by: Jari Korkiakoski | last post by:
Hello, What kind of difference does it make to update statistics via 'REORGCHK UPDATE STATISTICS ON TABLE ALL' or by running directly 'runstats on table xxx.yyy with distribution and detailed...
2
by: EpsilonAurigae | last post by:
does anyone have an idea as to why the reorgchk utility says that a particular table needs to be reorged even after running the reorg command on that particular table. answer is appreciated.
9
by: mike_dba | last post by:
I am using DMS raw. I have a tablespace which is defined with 5 containers each allocated at 1.5 GB for a total of about 7.5 GB. I did a major deletion followed by a reorg and a runstats. I am...
4
by: santhosh.david | last post by:
Hi, I have a query in reorgchk. When I was examining the output of reorgchk I found a column TSIZE in Table Statistics & ISIZE in Index Statistics. Can any one let me know the formula for TSIZE...
2
by: Okonita | last post by:
Hi all, How can I implement using REORGCHK to tell REORG what DB2 UDB v8 tables, etc to perform REORG on? Any example script will be highly appreciated. Okonita
0
by: bwmiller16 | last post by:
Hi All - OSName: AIX NodeName: dr_aixdb01 Version: 5 Release: 3 dr_aixdb01$db2level DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL08027"
7
by: Okonita via DBMonster.com | last post by:
Hi all, I am very surprised to see that after doing a Reorgchk followed by reorg of selected tables and concluding with a runstats of the reorged tables, all of the tables continue to be...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.