DB2 FOR LUW / DB2FORLUW / 910 - DB2

We are enabling generatiuon of implied IN-list predicates for 
the OR predicate set when query on a single table.  The OR 
predicate is restricted to have only equal (=) and inlist (IN) 
predicate subterm therefore only implied IN-list predicate would 
be generated.  With the new IN-list generated, we have new 
opportunity to carry out IN-TO-JOIN transformation to enhance 
query performance. 
 
e.g. 
select a1,a2,a3 
from a 
where (a1 = 1 and a3 in ('aa','bb','cc')) or (a1 = 1 and a3 in 
('dd','ee')) or (a1 = 2 and a3 in ('ff')); 
 
Optimized query: 
SELECT ALL Q3.A1, Q3.A2, Q3.A3 
FROM A Q3 
WHERE SYSIBM.SINLIST(Q3.A3, 'aa', 'bb', 'cc', 'dd', 'ee', 'ff') 
AND 
SYSIBM.SINLIST(Q3.A1, 1, 2) AND 
(((Q3.A1 = 1) AND SYSIBM.SINLIST(Q3.A3, 'aa', 'bb', 'cc')) OR 
((Q3.A1 = 1) AND SYSIBM.SINLIST(Q3.A3, 'dd', 'ee'))) OR 
((Q3.A1 = 2) AND (Q3.A3 = 'ff')); 
 
Note that there are existing condition to carry IN-TO-JOIN 
transfromation, the new IN predicate is expected to comply to 
those existing condition for have IN-TO-JOIN resulted.

We are enabling generatiuon of implied IN-list predicates 
for 
the OR predicate set when query on a single table.  The OR 
 
predicate is restricted to have only equal (=) and inlist 
(IN) 
predicate subterm therefore only implied IN-list predicate 
would 
be generated.  With the new IN-list generated, we have new 
 
opportunity to carry out IN-TO-JOIN transformation to 
enhance 
query performance. 
 
 
 
e.g. 
 
select a1,a2,a3 
 
from a 
 
where (a1 = 1 and a3 in ('aa','bb','cc')) or (a1 = 1 and a3 
in 
('dd','ee')) or (a1 = 2 and a3 in ('ff')); 
 
 
 
Optimized query: 
 
SELECT ALL Q3.A1, Q3.A2, Q3.A3 
 
FROM A Q3 
 
WHERE SYSIBM.SINLIST(Q3.A3, 'aa', 'bb', 'cc', 'dd', 'ee', 
'ff') 
AND 
 
SYSIBM.SINLIST(Q3.A1, 1, 2) AND 
 
(((Q3.A1 = 1) AND SYSIBM.SINLIST(Q3.A3, 'aa', 'bb', 'cc')) 
OR 
((Q3.A1 = 1) AND SYSIBM.SINLIST(Q3.A3, 'dd', 'ee'))) OR 
 
((Q3.A1 = 2) AND (Q3.A3 = 'ff')); 
 
 
 
Note that there are existing condition to carry IN-TO-JOIN 
 
transfromation, the new IN predicate is expected to comply 
to 
those existing condition for have IN-TO-JOIN resulted.

Workaround: 
You may manually rewritten the query with the implied IN 
predicate as shown in the example. 
 
defect: wsdbu00554342

DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
 DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
 DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
 DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows
 

First Fixed in DB2 LUW v9.1 Fixpak 9

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC61901 IC61902 IC61909 IC62132 IT02159 IT02166 
follow-up : 

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

08.07.2009
13.04.2010
13.04.2010

9.0.,
9.1.