DB2 FOR LUW / DB2FORLUW / A10 - DB2

The optimizer might choose a sub-optimal nested loop join 
(NLJOIN) access plan for an SQL statement containing range 
predicates with place holder variables, where the inner input of 
the NLJOIN is an index-anding (IXAND) access of the base table; 
e.g. 
. 
select ... from tab1, tab2 where tab1.COLA=tab2.COLA and 
tab1.COLB >=? and tab1.COLC < ?; 
You can collect an EXPLAIN of the statement to verify the access 
plan chosen by the optimizer shows an NLJOIN with an IXAND over 
a single base table access on the inner (right) input where the 
index scans below the IXAND apply the join and range predicates. 
If the inner input includes a TEMP, and the statement includes 
an IN predicate with place holder variables in the values list 
such as "tab1.COLD IN (?,?,?,?)" then APAR IC73877 is a likely 
candidate. 
. 
You can use the REOPT ONCE/ALWAYS option to solve this issue. 
The sub-optimal access plan can be chosen as a result of errors 
in the cardinality estimate due to the unknown values to be used 
in the range predicates such as tab1.COLB >=? and tab1.COLC < ?.

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

You can use the REOPT ONCE/ALWAYS option to solve this issue.

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
 

First fixed in Version 10.1 Fix Pack 1.

not known / see Local fix

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

14.06.2012
31.10.2012
31.10.2012