IBM Support

IC69509: Inaccurate NLEAF statistic for partitioned indexes of a partitioned table may lead to non-optimal access plans.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Inaccurate NLEAF statistic for partitioned indexes of a
    partitioned table may lead to non-optimal access plans.
    
    With DB2 V9,7 indexes for a partitioned table can be created as
    partitioned or non-partitioned.  For partitioned indexes, the
    NLEAF statistic may be inaccurate. For example, a table with the
    following per-partition statistics:
    
    select datapartitionid, numrids, nleaf from
    syscat.indexpartitions where tabschema='IC69509' and
    tabname='T1'
    
    DATAPARTITIONID NUMRIDS              NLEAF
    --------------- -------------------- --------------------
                  0                 1000                  334
                  1                 2000                  667
                  2                 3000                 1000
                  3                 4000                 1334
                  4                    0                    1
                  5                    0                    1
    
    
    may have an NLEAF of 1:
    
    select nleaf from syscat.indexes where tabschema='IC69509' and
    tabname='T1'
    
    NLEAF
    --------------------
                     1
    
    An inaccurate NLEAF statistics can affect query performance from
    non-optimal access plans.
    

Local fix

  • Manually update NLEAF of affected partitioned indexes to a more
    accurate value.  In the example above, a more accurate NLEAF is
    the average of the non empty partitions:
    
    UPDATE SYSCAT.INDEXES SET NLEAF=834 where tabschema='IC69509'
    and tabname='T1';
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users are affected                                       *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Inaccurate NLEAF statistic for partitioned indexes of a      *
    *                                                              *
    * partitioned table may lead to non-optimal access plans.      *
    *                                                              *
    *                                                              *
    *                                                              *
    * With DB2 V9,7 indexes for a partitioned table can be created *
    * as                                                           *
    * partitioned or non-partitioned.  For partitioned indexes,    *
    * the                                                          *
    * NLEAF statistic may be inaccurate. For example, a table with *
    * the                                                          *
    * following per-partition statistics:                          *
    *                                                              *
    *                                                              *
    *                                                              *
    * select datapartitionid, numrids, nleaf from                  *
    *                                                              *
    * syscat.indexpartitions where tabschema='IC69509' and         *
    *                                                              *
    * tabname='T1'                                                 *
    *                                                              *
    *                                                              *
    *                                                              *
    * DATAPARTITIONID NUMRIDS              NLEAF                   *
    *                                                              *
    * --------------- -------------------- --------------------    *
    *                                                              *
    * 0                1000                  334                   *
    *                                                              *
    * 1                2000                  667                   *
    *                                                              *
    * 2                3000                1000                    *
    * 3                4000                1334                    *
    * 4                    0                    1                  *
    *                                                              *
    * 5                    0                    1                  *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    * may have an NLEAF of 1:                                      *
    *                                                              *
    *                                                              *
    *                                                              *
    * select nleaf from syscat.indexes where tabschema='IC69509'   *
    * and                                                          *
    * tabname='T1'                                                 *
    *                                                              *
    *                                                              *
    *                                                              *
    * NLEAF                                                        *
    *                                                              *
    * --------------------                                         *
    *                                                              *
    * 1                                                            *
    *                                                              *
    *                                                              *
    *                                                              *
    * An inaccurate NLEAF statistics can affect query performance  *
    * from                                                         *
    * non-optimal access plans.                                    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 9.7 FP3.                              *
    ****************************************************************
    

Problem conclusion

  • Defect has been fixed in DB2 v9.7 FP3.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC69509

  • 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

    2010-06-29

  • Closed date

    2010-09-27

  • Last modified date

    2010-09-27

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

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

    IC69696

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
27 September 2010