Runstats Utility

rendezvous

analysing the catalog tables in db2 is there any parameter/parameters by which we can know that the statistics for teh optimizer have to be collected.

eg: an application developer is doing huge insertions and deletions on the table. the dba team do not know this or the appln team also does not convey this.
By monitoring the catalog or is there a way to query the catalog and know that massive changes have been done to the table and statistics have to be gathered for the optimizer to be efficient.

thanks in advance

docdiesel

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

Expand|Select|Wrap|Line Numbers

 
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.

rendezvous

i work on db2 on mainframes. V8

Runstats Utility

Post your reply

Similar topics