DB2 FOR IM PROD / IM0062BOS / A10 - DB2

A SELECT statement with a predicate 'COL-C IN ( ITEM-1, ITEM-2, 
... ITEM-N )' might crash the DB2 server under the following 
conditions: 
 
1. There is a composite index on the table where the column 
COL-C is not in the first two columns of the index. 
2. There is another predicate in the query ( e.g. COL-A = 10 ) 
which is applied as a start-stop key by the index. 
3. The column COL-A is a leading column of the index such that 
there is a 'gap' between COL-A and COL-C 
i.e. the index is applying the IN predicate as a start-stop key 
by the jump-scan operation. 
 
For example: 
   create table TAB1 ( A int, B int, C int ); 
   create index IND1 on TAB1 ( A, B, C ); 
   select C from TAB1 where A = 10 and C in ( 10, 20, 30, 40 ); 
 
The trap will appear in the following stack: 
... 
sqlnoPrdFlagsType4test 
sqlno_index_scan 
sqlno_crule_index_scan 
sqlno_crule_access_root 
sqlno_crule_access 
sqlno_get_best_plan 
sqlno_crule_nljn_inner 
sqlno_crule_nljn 
sqlno_crule_join_choices 
sqlno_crule_join_order 
sqlno_crule_in2join_access 
...

**************************************************************** 
* USERS AFFECTED:                                              * 
* Version 10.1.FP2                                             * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to 10.1.fp3                                          * 
****************************************************************

Use the following DB2 registry as a workaround: db2set 
"DB2_REDUCED_OPTIMIZATION=JUMPSCAN OFF" and recycle the 
instance.

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
 

Fixed in version 10.1.fp3.

not known / see Local fix

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

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

21.05.2013
21.03.2014
21.03.2014