REORG CHECK 1

[cristi22]

Hello all -

I reorg all indexes & runstats..

Could someone please explain what does the following means??
Any help is appriciated!!!
why CONT_D_WK * other indexes have "*" ??

:db2 reorgchk current statistics on table basys.CONT_DETAIL

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: BASYS.CONT_DETAIL
BASYS     CONT_DETAIL         366530     0 20225 20225      - 79903536   0  98 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  
-------------------------------------------------------------------------------------------------
Table: BASYS.CONT_DETAIL
BASYS    COND_GR            366530   614     0     3     5     0     33  99  74  41   0   0 ----- 
BASYS    CONT_D_DEP_TYPE    366530   613     0     3     5     0     21  96  74  41   0   0 ----- 
BASYS    CONT_D_WK          366530   614     0     3     9     0    172  73  74  33   0   0 *---- 
BASYS    CONTD_EMPL_NO      366530   633     0     3    10     0   4573  48  75  30   0   0 *---- 
SYSIBM   SQL060214174242890 366530  1890     0     3     8     0 366530  64  82  10   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.

thanks,
cristi

 

[blom0344]

Uh, clusterratio is beneath 80, so this results in the asterisk. This is not that strange if the table has been formerly reorganized using either COND_GR or COND_D_DEP_TYPE.

SQL060214174242890 being the primary key index,these will be badly oragnized if you have taken a higher level like department to organize your table by..

Ties Blom

 

[cristi22]

Ties Blom Thank you for your time !
It's all very new to me.
But I don't understand how to make these things in sync to improve the performance?!
Why will it be badly oragnized? Why department is higer?

thanks again,
cristi

 

[blom0344]

Well, when reorganizing a table you would typically take a certain index. If you have a high level group like department in the table (and you expect that lots of queries will be aimed at that level) this would be the proper index. If the primary key of the table is the employee-id, then (unless there is a relation between dept and employee-id)the primary keys will be rather 'shuffled'

Ties Blom