DB2 FOR LUW / DB2FORLUW / 970 - DB2

A new feature in Version 9.1 enables the DB2 query optimizer to 
consider join predicates, that equate the null value, as index 
start and stop keys. These join predicates have the following 
form: 
 
T1.A = T2.A OR (T1.A IS NULL AND T2.A IS NULL) 
 
If the data in the join columns is significantly skewed, then 
the optimizer may underestimate the cardinality of the join, and 
choose a less-than-optimal access plan.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* A new feature in Version 9.1 enables the DB2 query optimizer * 
* to                                                           * 
* consider join predicates, that equate the null value, as     * 
* index                                                        * 
* start and stop keys. These join predicates have the          * 
* following                                                    * 
* form:                                                        * 
*                                                              * 
*                                                              * 
*                                                              * 
* T1.A = T2.A OR (T1.A IS NULL AND T2.A IS NULL)               * 
*                                                              * 
*                                                              * 
*                                                              * 
* If the data in the join columns is significantly skewed,     * 
* then                                                         * 
* the optimizer may underestimate the cardinality of the join, * 
* and                                                          * 
* choose a less-than-optimal access plan.                      * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7 Fix Pack 2 and enable the         * 
* improvement if needed.                                       * 
****************************************************************

Defining a statistical view on the join will provide the 
required statistics to allow the optimizer to choose an optimal 
access plan.  The following is an example of such a view 
definition: 
 
CREATE VIEW S.SVIEW1 AS 
SELECT * 
FROM T1,T2 
WHERE T1.A1=T2.A2 OR (T1.A1 IS NULL AND T2.A2 IS NULL); 
 
ALTER VIEW S.SVIEW1 ENABLE QUERY OPTIMIZATION; 
 
RUNSTATS ON TABLE S.SVIEW1 WITH DISTRIBUTION;

DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 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 10 for Linux, UNIX, and Windows
 

Problem was first fixed in Version 9.7 Fix Pack 2. 
 
This fix addressed scenarios where a statistical view cannot be 
used.  To enable this improvement, the user must set the 
following registry variable: 
DB2_SELECTIVITY=JOIN_SKEW 
 
If the variable is already set, then multiple settings can be 
provided, separated by a comma.  For example: 
DB2_SELECTIVITY=YES,JOIN_SKEW

see LOCAL FIX

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

20.01.2010
18.05.2010
18.05.2010

9.7.FP2