Hi,

How to check the how many index are created on a perticular table.

Regards

kanakaraju.Y

db2 describe indexes for <schema_name>.<table_name> show detail

or

you can check the how many index are created on a perticular table using the reorgchk command.

db2 reorgchk current statistics on table schemax.tablex

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: schemax.tablex
schemax    tablex            -     -     -     -      -        -   -   -   - ---
----------------------------------------------------------------------------------------

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: schemax.tablex
schemax   tablex~0         -     -     -     -     -     -      -   -   -   -   -   - -----
schemax tablex~1         -     -     -     -     -     -      -   -   -   -   -   - -----
-------------------------------------------------------------------------------------------------

The table schemax.tablex has two indexes

Previously wrote:Thanks

Regards

kanak

you can check the how many index are created on a perticular table using the reorgchk command.

db2 reorgchk current statistics on table schemax.tablex

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: schemax.tablex
schemax    tablex            -     -     -     -      -        -   -   -   - ---
----------------------------------------------------------------------------------------

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: schemax.tablex
schemax   tablex~0         -     -     -     -     -     -      -   -   -   -   -   - -----
schemax tablex~1         -     -     -     -     -     -      -   -   -   -   -   - -----
-------------------------------------------------------------------------------------------------

The table schemax.tablex has two indexes