DB2 FOR LUW / DB2FORLUW / A10 - DB2

During planning the DB2 optimizer can make adjustments to the 
estimated cardinality of the plan to account for highly 
correlated values between columns using in multiple predicate of 
the query. When there are multiple IN predicate and multiple 
join predicate on the same columns, the adjustments made can 
cause the the optimizer to overestimate the cardinality of the 
plan. This overestimation can lead to poor plan choices and high 
cost estimates. 
 
For example the following query show an example of a query that 
may be affected. 
 
SELECT * 
  FROM T1, T2 
 WHERE T2.a = T1.a 
   and T2.b = T1.b 
   and T1.a IN (1, 2, 3, 4, 5) 
   and T1.b IN (1, 2, 3, 4, 5) 
 
In this query there are two join predicates on columns A and B 
between tables T1 and T2, in addition there are IN predicates on 
both columns A and B. If the data in the table shows a high 
degree of correlation between the values in columns A and B then 
this query may be subject to the cardinality overestimation. 
 
Using db2exfmt for suspected queries you can verify if this is 
the case by looking for the predicate pattern noted above and 
also noticing that the estimated cardinality is drastically 
increasing at the actual NLJOIN, HSJOIN or MSJOIN operation 
between tables T1 and T2.

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 V10                                                      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.1 Fix Pack 1                       * 
****************************************************************

DB2 Version 10.1 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

Problem Fixed in DB2 Version 10.1 Fix Pack 1

not known / see Local fix

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

14.06.2012
07.11.2012
07.11.2012