DB2 FOR LUW / DB2FORLUW / 970 - DB2

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.

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users who use ranged partitioned table and when its density  * 
* is very low                                                  * 
**************************************************************** 
* PROBLEM 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.        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to DB2 version 9.7 fixpack 8                          * 
****************************************************************

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.

DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

1. Use optimization guidelines to direct the query optimizer to 
different access plans.

forerunner  : APAR is sysrouted TO one or more of the following: IC91223 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

22.11.2012
02.04.2013
02.04.2013

9.7.FP8