IBM Support

IC91223: INACCURATE DENSITY AND SEQUENTIAL_PAGES STATISTICS FOR PARTITIONED INDEXES MIGHT LEAD TO NON-OPTIMAL ACCESS PLANS

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • DENSITY for partitioned indexes might be inaccurate when the
    partitioned table has a significant number of empty partitions.
    
    SEQUENTIAL_PAGES for partitioned indexes might be inaccurate and
    inconsistent with NLEAF when the partitioned table has a
    significant number of empty or near empty partitions.
    

Local fix

  • 1. Use optimization guidelines to direct the query optimizer to
    different access plans.
    
    2. Alternately, provide the query optimizer with more accurate
    statistics via manually updating the relevant SYSSTAT.INDEXES
    rows.  To determine what statistical values to use, one approach
    is to examine the per-partition index statistics in
    SYSCAT.INDEXESPARTITIONS and use a value representative from the
    majority of partitions with data.  You can use the NUMRIDS
    statistics to assess if a partition is empty or near empty.
    When updating SEQUENTIAL_PAGES, a further consideration is its
    relationship to NLEAF.  For example, if the majority of data
    holding partitions have
    SYSCAT.INDEXESPARTITIONS.SEQUENTIAL_PAGES near
    SYSCAT.INDEXESPARTITIONS.NLEAF, then update
    SYSTAT.INDEXES.SEQUENTIAL_PAGES so that it is near
    SYSTAT.INDEXES.NLEAF.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 10.1 Fixpack 3.                       *
    ****************************************************************
    

Problem conclusion

  • Fixed in Version 10.1 Fixpack 3.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC91223

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-04-02

  • Closed date

    2013-10-10

  • Last modified date

    2013-10-10

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

    IC88502

  • 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

  • RA10 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 10.1

Reference #: IC91223

Modified date: 10 October 2013