DB2 FOR LUW / DB2FORLUW / 950 - DB2

DB2 might be encountering this problem if the  SQL statement is 
not performing optimally, one or more joins in the query are on 
columns with significant skew in one or both sides of the join, 
and neither column referenced in the join is unique, or close to 
unique.  Under this condition, the DB2 Optimizer might 
underestimate the cardinality which might result in a less than 
optimal query access plan. 
To solve this problem, in most cases, a statistical view can be 
created on the join to provide the optimizer the appropriate 
information to address for the skew.  This fix will improve the 
optimizer's cardinality estimation to make use of single table 
column distribution statistics to estimate the skew in the join, 
for each individual equality join predicate.  The following is 
an example of a query with an equality join predicate that is 
eligible under these conditions: 
SELECT ... 
FROM ... T1, T2, ... 
WHERE ... T1.X=T2.X 
 
A statistical view will provide more accurate information as 
well as more flexibility in cases such as the following that 
will not be addressed with this improvement: 
1.  There are multiple columns involved in the join, and the 
    columns are statistically correlated; 
2.  Joins involving non-equality predicates such as "T1.DATE >= 
    T2.START_DATE AND T1.DATE <= T2.END_DATE"; 
3.  Local predicates are applied on either side of the join that 
    correlates to the data in the join column such as "T1.X IN 
    (x,y,z) AND T1.Y=T2.Y"

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* DB2 might be encountering this problem if the  SQL statement * 
* is                                                           * 
* not performing optimally, one or more joins in the query are * 
* on                                                           * 
* columns with significant skew in one or both sides of the    * 
* join,                                                        * 
* and neither column referenced in the join is unique, or      * 
* close to                                                     * 
* unique.  Under this condition, the DB2 Optimizer might       * 
* underestimate the cardinality which might result in a less   * 
* than                                                         * 
* optimal query access plan.                                   * 
* To solve this problem, in most cases, a statistical view can * 
* be                                                           * 
* created on the join to provide the optimizer the appropriate * 
* information to address for the skew.  This fix will improve  * 
* the                                                          * 
* optimizer's cardinality estimation to make use of single     * 
* table                                                        * 
* column distribution statistics to estimate the skew in the   * 
* join,                                                        * 
* for each individual equality join predicate.  The following  * 
* is                                                           * 
* an example of a query with an equality join predicate that   * 
* is                                                           * 
* eligible under these conditions:                             * 
* SELECT ...                                                   * 
* FROM ... T1, T2, ...                                         * 
* WHERE ... T1.X=T2.X                                          * 
*                                                              * 
* A statistical view will provide more accurate information as * 
* well as more flexibility in cases such as the following that * 
* will not be addressed with this improvement:                 * 
* 1.  There are multiple columns involved in the join, and the * 
*     columns are statistically correlated;                    * 
* 2.  Joins involving non-equality predicates such as "T1.DATE * 
* >=                                                           * 
*     T2.START_DATE AND T1.DATE <= T2.END_DATE";               * 
* 3.  Local predicates are applied on either side of the join  * 
* that                                                         * 
*     correlates to the data in the join column such as "T1.X  * 
* IN                                                           * 
*     (x,y,z) AND T1.Y=T2.Y"                                   * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.5 Fix Pack 9                        * 
****************************************************************

Create a statistical view for the affected join(s) to provide 
the optimizer the relevant information to account for the data 
skew.

DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in version 9.5 Fix Pack 9

see LOCAL FIX

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

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

16.09.2010
08.03.2012
08.03.2012

9.5.FP9