DB2 FOR LUW / DB2FORLUW / 970 - DB2

The optimizer may choose a sub-optimal access plan for an SQL 
statement which uses an IN and a place holder variable as part 
of the FROM clause. 
 
e.g. 
select ... from tab1, tab2 where tab1.COLA=tab2.COLA and 
tab1.COLB IN (?,?,?,...); 
 
If there is an index with t1.COLB as the leading column in the 
key, the optimizer can consider an in-to-join transformation of 
the IN predicate shown in the WHERE clause. 
If the access of tab1 is on the inner of a NLJN, the optimizer 
might consider a plan with a TEMP over the inner access that 
might be beneficial for the in-to-join access on the inner.  In 
general, this is a good option to consider but when there are 
statement place holders, the cardinality estimates cannot always 
be accurate.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All Platforms                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* The optimizer may choose a sub-optimal access plan for an    * 
* SQL                                                          * 
* statement which uses an IN and a place holder variable as    * 
* part                                                         * 
* of the FROM clause.                                          * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V9.7 Fix Pack 5.                              * 
****************************************************************

dbset DB2_EXTENDED_IN2JOIN=NLJN_TEMP_OFF

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 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
 

This problem was first fixed in DB2 V9.7 Fix Pack 5.

not known / see Local fix

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

17.01.2011
27.12.2011
27.12.2011

9.7.FP5