DB2 FOR LUW / DB2FORLUW / 970 - 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 product v9.7 Fix Pack 6.                          * 
****************************************************************

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

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

First fixed in Version 9.7 Fix Pack 6.

not known / see Local fix

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

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

11.05.2011
19.06.2012
19.06.2012

9.7.FP6