IBM Support

IC74823: THE OUTPUT OF REORGCHK AND REORGCHK_IX_STATS ARE NOT MATCHING

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • # create a table with indexes
    # load lots of data
    # delete some of the data
    # runstats on the table and indexes all
    
    Compare the results of output
    db2 reorgchk update statistic on table <tb name>
    db2 call reorgchk_ix_stats('T','schema.tablename')
    
    
    
    Wrong * reported for index :  GINAULAK.GI1_TP2 AND
    GINAULAK.GI2_TP2
    
    hotellnx95:/home/hotellnx95/ginaulak/PCR059456/NORMALTAB> db2
    "reorgchk update statistics on table ginaulak.tp2"
    
    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: GINAULAK.TP2
                                    1283      0    150    298      -
      292524   0  25  51 -**
    Table: GINAULAK.TP2
    Data Partition: PART0
                                       0      0      0    104      -
           0   0   0   0 -**
    Table: GINAULAK.TP2
    Data Partition: PART1
                                     517      0     61    104      -
      117876   0  30  63 -**
    Table: GINAULAK.TP2
    Data Partition: PART2
                                     766      0     89     90      -
      174648   0  53 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  PCT_PAGES_SAVED  F4  F5  F6  F7  F8 REORG
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    -----------------------------------------------
    Table: GINAULAK.TP2
    Index: GINAULAK.GI1_TP2
                                   1283   312   154    4  1238
    1283          208           208                132
      132                0  99  45 285  49  49 -****
    Index: GINAULAK.GI2_TP2
                                   1283   244   121    3  1246
    1125          205           205                132
      132                0  13  50  23  49  49 **-**
    Index: GINAULAK.LI3_TP2
    Data Partition: PART0
                                      -     -     -    -     -
    -            -             -                  -
     -                -   -   -   -   -   - -----
    Index: GINAULAK.LI3_TP2
    Data Partition: PART1
                                    517   110    45    2   515
    517          208             8                132
     566                0 100  44   -  49  40 -*-**
    Index: GINAULAK.LI3_TP2
    Data Partition: PART2
                                    766    94     0    2   734
    766          208             8                132
     566                0 100  45   -  48   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.
    
    hotellnx95:/home/hotellnx95/ginaulak/PCR059456/NORMALTAB> db2
    "call reorgchk_ix_stats('T','GINAULAK.TP2')"
    
    
      Result set 1
      --------------
    
      TABLE_SCHEMA
    
     TABLE_NAME
    
    INDEX_SCHEMA
    
    INDEX_NAME
    
    DATAPARTITIONNAME
    
    INDCARD              NLEAF                NUM_EMPTY_LEAFS
    NLEVELS     NUMRIDS_DELETED      FULLKEYCARD
    LEAF_RECSIZE         NONLEAF_RECSIZE      LEAF_PAGE_OVERHEAD
    NONLEAF_PAGE_OVERHEAD PCT_PAGES_SAVED F4          F5          F6
             F7          F8          REORG
    
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    ----------------------------------------------------------------
    -------------------- -------------------- --------------------
    ----------- -------------------- --------------------
    -------------------- -------------------- --------------------
    --------------------- --------------- ----------- -----------
    ----------- ----------- ----------- -----
      GINAULAK
    
     TP2
    
    GINAULAK
    
    GI1_TP2
    
    
    
                  1283                  312                  154
          4                 1238                 1283
      208                  208                  822
     822               0          99          54         151
     49          49 --***
      GINAULAK
    
     TP2
    
    GINAULAK
    
    GI2_TP2
    
    
    
                  1283                  244                  121
          3                 1246                 1125
      204                  204                  822
     822               0          13          60          15
     49          49 *--**
      GINAULAK
    
     TP2
    
    GINAULAK
    
    LI3_TP2
    
    PART0
    
                      0                  110                  110
           2                    0                    0
       208                    8                   -1
       -1              -1         100          60          -1
       0         100 ----*
      GINAULAK
    
     TP2
    
    GINAULAK
    
    LI3_TP2
    
    PART1
    
                    517                  110                   45
           2                  515                  517
       208                    8                   -1
       -1              -1         100          44          -1
      49          40 -*-**
      GINAULAK
    
     TP2
    
    GINAULAK
    
    LI3_TP2
    
    PART2
    
                    766                   94                    0
           2                  734                  766
       208                    8                   -1
       -1              -1         100          45          -1
      48           0 -*-*-
    
      5 record(s) selected.
    
      Return Status = 0
    
    
    Next do a reorg on the table:
    db2 "reorg table ginaulak.tp2"
    and again check it.. sometime it will show mismatch for some
    index; it does not always happen though.
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * THE OUTPUT OF REORGCHK AND REORGCHK_IX_STATS ARE NOT         *
    * MATCHING                                                     *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 LUW Version 9.7 Fix Pack 5                    *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Version 9.7 Fix Pack 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC74823

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-03-03

  • Closed date

    2012-01-17

  • Last modified date

    2012-01-17

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC74823

Modified date: 17 January 2012