|
Problem IC62733
|
Status: Closed |
DB2 MAY RETURN INCORRECT RESULTS FOR RECURSIVE QUERIES UNDER CERTAIN CONDITIONS. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The query plan of a recursive query consists of:
a. a CSE TEMP operator (call it CSETEMP1) above a UNION operator
at the top of the plan
b. a reference (call it TBSCAN1) to CSETEMP1 in the UNION
sub-plan, forming a recursive path.
Operators between the CSETEMP1 (above) and TBSCAN1 (below) in
the UNION sub-plan are in a "recursive path".
DB2 may return incorrect results for a recursive query that
satisfies all of the following conditions:
1. The query plan has a non-CSE TEMP (call it TEMP2) operator
along the recursive path.
2. The TBSCAN (call it TBSCAN2) operator above TEMP2 indicates a
"reverse" scan direction.
3. TBSCAN2 feeds the outer of a nested loop join operator
4. The inner of the nested loop join operator scans the index
entries (call the index IDX1) of an inner table in the reverse
order.
In short, TEMP2 is injected to facilitate a reverse scan to
match the order of entries retrived from index IDX1. |
| Problem Summary: |
DB2 MAY RETURN INCORRECT RESULTS FOR RECURSIVE QUERIES UNDER
CERTAIN CONDITIONS. |
| Local Fix: |
For DB2 V9+:
Drop and recreate the index IDX1 with the following properties:
- store the index entries in the reverse order of the column
values. For example, change ASC to DESC, or vice versa.
- specify the DISALLOW REVERSE SCANS clause. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
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 9a 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 10 for Linux, UNIX, and Windows
|
| Solution |
It is fixed in db2 V9.7 FP2 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 26.08.2009
06.07.2010
06.07.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
