Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC61900 Status: Closed

ENABLE GENERATION OF IMPLIED IN-LIST PREDICATES FOR OR PREDICATE WHEN QUERY
ON A SINGLE TABLE.

product:
DB2 FOR LUW / DB2FORLUW / 910 - DB2
Problem description:
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.
Problem Summary:
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.
Local Fix:
Workaround: 
You may manually rewritten the query with the implied IN 
predicate as shown in the example. 
 
defect: wsdbu00554342
available fix packs:
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

Solution
First Fixed in DB2 LUW v9.1 Fixpak 9
Workaround
not known / see Local fix
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC61901 IC61902 IC61909 IC62132 IT02159 IT02166 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
08.07.2009
13.04.2010
13.04.2010
Problem solved at the following versions (IBM BugInfos)
9.0.,
9.1.
Problem solved according to the fixlist(s) of the following version(s)
9.1.0.9 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.