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

DB2 MAY RETURN INCORRECT RESULT FROM SELECT DISTINCT LEFT OUTER JOIN UNION
ALL VIEW

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
An SQL statement to perform a left outer join between tables or 
views along with a UNION ALL view may return incorrect results. 
The UNION ALL view must be the right table or null-producing 
table of the left outer join. In addition, the SELECT clause 
must be specified with DISTINCT for the tables or views. 
 
 
The above mentioned scenario will likely cause DB2 to return 
more rows from the left outer joining process, than the optimum 
value.
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All users                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* An SQL statement to perform a left outer join between tables * 
* or                                                           * 
* views along with a UNION ALL view may return incorrect       * 
* results.                                                     * 
* The UNION ALL view must be the right table or null-producing * 
*                                                              * 
* table of the left outer join. In addition, the SELECT clause * 
*                                                              * 
* must be specified with DISTINCT for the tables or views.     * 
*                                                              * 
*                                                              * 
*                                                              * 
*                                                              * 
*                                                              * 
* The above mentioned scenario will likely cause DB2 to return * 
*                                                              * 
* more rows from the left outer joining process, than the      * 
* optimum                                                      * 
* value.                                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to the latest fix pack or utilize workaround.        * 
****************************************************************
Local Fix:
Run the following commands to stop DB2 instance, set the 
registry variable and restart DB2: 
 
db2stop force 
db2set DB2_UNION_OPTIMIZATION=DISABLE_OJPPD_FOR_NP 
db2start 
 
This will avoid optimization that can lead to incorrect results.
available fix packs:
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

Solution
Workaround
Run the following commands to stop DB2 instance, set the 
 
registry variable and restart DB2: 
 
db2stop force 
db2set DB2_UNION_OPTIMIZATION=DISABLE_OJPPD_FOR_NP 
db2start 
 
This will avoid optimization that can lead to incorrect results.
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
06.01.2011
02.05.2011
02.05.2011
Problem solved at the following versions (IBM BugInfos)
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.4 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.