Latest versionsfixlist
11.1.0.6 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
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC61901 Status: Closed

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

product:
DB2 FOR LUW / DB2FORLUW / 950 - 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:
ENABLE GENERATION OF IMPLIED IN-LIST PREDICATES FOR OR PREDICATE 
WHEN QUERY ON A SINGLE TABLE.
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.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Solution
Problem was first fixed in Version 9.5 Fix Pack 6
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
08.07.2009
17.05.2010
17.05.2010
Problem solved at the following versions (IBM BugInfos)
9.5.FP6
Problem solved according to the fixlist(s) of the following version(s)
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.