DB2 FOR LUW / DB2FORLUW / 970 - DB2

A query that contains a large IN predicate applied to the same 
column as an equality join may show a RIDSCAN consuming many 
IXSCAN inputs instead of using a nested loop join to probe the 
index with the key values. For example the following query could 
suffer from this issue when both T1.c1 and T2.c1 have indexes 
defined. 
 
SELECT * 
FROM T1, T2 
WHERE T1.c1 = T2.c1 
  AND T1.c1 in (?, ?, ?, ?, ?, ?, ?, 
        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
?, ?, ?, ?, 
        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
?, ?, ?, ?, 
        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
?, ?, ?, ?, 
        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
 
Upon examination of the access plan you may see a NLJOIN between 
T1 and a GENROW operator followed by a subsequent join to T2 
that is accessed using RIDSCAN with many IXSCAN input plans.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix pack 7.                       * 
****************************************************************

Create and apply an optimization profile that forces the IN to 
join plan using the following guideline 
 
<OPTGUIDELINES><INLIST2JOIN/></OPTGUIDELINES>

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 DB2 Version 9.7 Fix Pack 7.

not known / see Local fix

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

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

05.06.2012
14.12.2012
14.12.2012

9.7.FP7