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 IC67741 Status: Closed

POSSIBLE INCORRECT RESULTS OPTIMIZING A <COLUMN> = ? PREDICATE ON A
UNION ALL VIEW USED AS A COMMON SUB-EXPRESSION

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
Incorrect results may occur during the optimization of a Union 
All view (UA) that is used as a common sub-expression (CSE) and 
has predicate(s) applied on it. 
 
The conditions for such a scenario are: 
1) Query contains a UA (UA1) over another UA (UA2) and UA2 is 
used in both legs of UA1 (making it a CSE) 
2) The legs of UA2 are simple selects over base-tables (BT) 
3) The legs of UA2 have no predicates or simple equi-join or 
range-join local predicates 
4) Each BT used in UA2 has equi-join check constraints defined 
on it 
5) Either leg of UA1 has one or more <column> = ? predicate 
(PARAMPRD) 
6) The column referenced in PARAMPRD is an output column of UA2 
7) All column(s) referenced in PARAMPRD(s) source directly from 
BTs and have equi-join check constraints defined on them 
8) The parameter marker referenced in PARAMPRD is bound to a 
value VAL1. 
9) VAL1 is different from the check constraint value of the 
column from the largest BT under UA2 that is referenced in 
PARAMPRD
Problem Summary:
Users Affected: Specific to a union all view used as a common 
sub-expression. 
Problem Description: POSSIBLE INCORRECT RESULTS OPTIMIZING A 
<COLUMN> = ? PREDICATE ON A UNION ALL VIEW USED AS A COMMON 
SUB-EXPRESSION 
Problem Summary: See above.
Local Fix:
Lower the query optimization class to a value less than 5. This 
can be done either by altering the CURRENT QUERY OPTIMIZATION 
special register or by altering the DFT_QUERYOPT database 
configuration parameter.
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 FixPak 6
Workaround
Lower the query optimization class to a value less than 5. This 
can be done either by altering the CURRENT QUERY OPTIMIZATION 
special register or by altering the DFT_QUERYOPT database 
configuration parameter.
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC68260 IC68261 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
08.04.2010
07.10.2010
07.10.2010
Problem solved at the following versions (IBM BugInfos)
9.5.
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.