DB2 FOR LUW / DB2FORLUW / 970 - DB2

In version 9.1, an expression of the form 
"x = y or (x is null and y is null)" 
is treated as an equality join predicate that evaluates NULL 
values as equal.  The DB2 SQL query optimizer will compute a 
filter factor estimate for this predicate when computing the 
optimal query access plan for a statement that includes 
this predicate.  When the appropriate statistics are collected, 
in most cases, the filter factor estimate is accurate; however, 
under certain conditions, the optimizer may underestimate the 
filter factor for this predicate which may lead to a sub-optimal 
query access plan to be chosen. 
 
This problem only applies to some scenarios where a local 
predicate is also applied on one of the columns in the join 
predicate, x or y. For example, in the query below: 
 
SELECT * FROM t1,t2 
WHERE (t1.a=t2.a OR t1.a is null AND t2.a is null) AND t2.a=?; 
 
the expressions in the WHERE clause will be written into the 
following: 
 
(Q1.A = :?) AND ((Q2.A = :?) OR (Q2.A IS NULL AND :? IS NULL )) 
 
If a constant value is used in place of the parameter marker, 
this problem does not occur.

In version 9.1, an expression of the form 
   "x = y or (x is null and y is null)" 
is treated as an equality join predicate that evaluates NULL 
values as equal.  The DB2 SQL query optimizer will compute a 
filter factor estimate for this predicate when computing the 
optimal query access plan for a statement that includes 
this predicate.  When the appropriate statistics are collected, 
in most cases, the filter factor estimate is accurate; however, 
under certain conditions, the optimizer may underestimate the 
filter factor for this predicate which may lead to a sub-optimal 
query access plan to be chosen. 
 
This problem only applies to some scenarios where a local 
predicate is also applied on one of the columns in the join 
predicate, x or y. For example, in the query below: 
 
SELECT * FROM t1,t2 
WHERE (t1.a=t2.a OR t1.a is null AND t2.a is null) AND t2.a=?; 
 
the expressions in the WHERE clause will be written into the 
following: 
 
(Q1.A = :?) AND ((Q2.A = :?) OR (Q2.A IS NULL AND :? IS NULL )) 
 
If a constant value is used in place of the parameter marker, 
this problem does not occur.

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 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 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 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
 

Defect   -> wsdbu00552854 
Fixed in -> Version 9.7 + FP 1 (s091114) 
Module   -> engn_sqno

not known / see Local fix

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

30.09.2009
31.12.2009
31.12.2009

9.7.