Dear all,

i am presently workin on db2 9.1. on AIX, i am trying to reorg one table, reorg command is succefully exicuted ,but reorgchk command again showing *(need for reorg), how can i solve this problem.
The commands used by me are,


db2 "RUNSTATS ON TABLE DB2INST1.ADMINISTRATION WITH DISTRIBUTION AND INDEXES ALL"

DB2 "REORG TABLE DB2INST1.ADMINISTRATION"

after this two commands exicuton, Reorgchk output is

$ db2 "reorgchk update statistics on table DB2INST1.ADMINISTRATION"

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: DB2INST1.ADMINISTRATION
608194 0 27879 27879 - 8.98e+08 0 98 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.ADMINISTRATION
Index: DB2INST1.ADMINISTRATION1
608194 801 0 3 0 608194 26 26 3534 3534 100 91 85 0 0 -----
Index: DB2INST1.ADMINISTRATION2
608194 170 0 2 0 932 15 15 6036 6036 38 94 - 0 0 *----
Index: DB2INST1.I_OBJSEARCHCURRENT
608194 782 0 3 0 608194 25 25 3938 3938 0 91 87 0 0 *----
Index: DB2INST1.I_SECURITY_POLICY
608194 169 0 2 0 2 3 3 6036 6036 100 94 - 0 0 -----
Index: DB2INST1.UI_U40_PUBLICATION
608194 169 0 2 0 1 3 3 6036 6036 100 94 - 0 0 -----
Index: DB2INST1.UI_U41_OWNERDOCUME
608194 169 0 2 0 1 3 3 6036 6036 100 94 - 0 0 -----
Index: SYSIBM.SQL090826165345690
608194 661 0 2 0 608194 18 18 6036 6036 0 93 - 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.

pls anybody can advice me, wats the mistake i am coming across,
Thnaks in advance.

The "*" indicate that those indexes are not clustering indexes for the table. Since the rows in a table can be ordered only by 1 criteria and not by 2 different ones, you will always have a "*" showing on those indexes by which the rows are not ordered. The only exception is if there is a functional dependency between the clustering index and the other indexes.

thnx, for ur reply stolze, pls can u give me clear explanation, is my reorg is successful???,

You didn't get an error message or warning, so your reorg was successful.

As for a more detailed explanation, I suggest you familiarize yourself with clustering and sorting of rows. It is simply that if the rows are physically sorted according to one criteria (index), it is not possible to sort them - at the same time - to a different criteria (index). You have to choose one sort order and accept that you cannot satisfy all others.

thanku so much stolze for ur reply, i wil work out on clustering and sorting .............

can anybody tell wat are the best syntexes for runsats and reorg..........

offline reorg and
reorg table tabname
for runstats as marcus informed
db2 "runstats on table db2inst1.books with distribution on key columns and detailed indexes all"
regds
Paul

The question is which statistics you really need. Collecting everything that is possible (all columns, all indexes, with distribution, etc.) is good but it also takes time to collect the stats. If you don't have that time, you can't use all those options. So the answer is as usual: it depends.