|
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
|
 |
