|
Problem IT08456
|
Status: Closed |
INCORRECT QUERY RESULTS OR SQL0901N REASON "BAD OUTER COMPARE" POSSIBLE IN DPF WHEN SQL PLAN HAS MDTQ AND MERGE JOIN LOLEPOPS |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
Due to a timing issue in DPF environments, a statement may fail
with this error :
SQL0901N The SQL statement failed because of a non-severe system
error. Subsequent SQL statements can be processed. (Reason "bad
outer compare".)
The same conditions can however also lead to incorrect results
being returned.
This issue may occur only if the statement access plan involves
a merge-join. Specifically, if the access plan has a MDTQ
operator ( Merging Directed Table Queue ) on the outer (left)
side of the merge-join (MSJOIN).
In the FODC_AppErr that is generated, the stack file of the
failing EDU
has "sqlri_mj" before failure.
There is a chance that other types of SQL0901 errors may occur
because of this problem. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Update to DB2 v10.5 Fix Pack 7 *
**************************************************************** |
| Local Fix: |
The following setting disables a performance feature for MDTQ
and avoids the issue, but it may cause some queries to run
slower:
db2set DB2_SPILL=NO_HINTS
This change requires an instance recycle to take effect.
Another option is to set the following registry value :
db2set DB2_SORT_AFTER_TQ=YES
this can also be supplied specific to the statement using an
optimizer profile :
<OPTGUIDELINES><REGISTRY><OPTION NAME='DB2_SORT_AFTER_TQ'
VALUE='YES'/></REGISTRY></OPTGUIDELINES> |
| Solution |
Fixed in DB2 v10.5 Fix Pack 7 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.04.2015
20.01.2016
02.09.2016 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.7
|
 |
