|
Problem IC98114
|
Status: Closed |
IN DB2 DPF ENVIRONMENTS ONLY, A SPECIFIC TYPE OF QUERY AND RESULTING ACCESS PLAN MIGHT RETURN WRONG RESULTS |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
In DB2 DPF environments only, a specific type of query and
resulting access might return wrong results.
The following conditions will cause this issue to occur:
- The query must have joins with join predicates using an
explicit expression or an implicit data type conversion. An
example of an explicit expression is "coalesce(col1,col2) =
coljoin".
- The access plan must have a UNION with a Directed TQ (DTQ)
above it. This is most likely to happen in a query that does a
full outer join.
- The access plan must also have a Hash Join (HSJOIN) chosen
with the expression above the UNION.
You can review access plans through either the explain utility
and the db2exfmt command or using Optim Visual Explain.
The problem scenario can only happen if you have all of these in
the plan:
- UNION
- HSJN above UNION with the JOIN predicate using the result of
expression (for example: COALESCE(Q9.$C3, Q9.$C1) =
Q10.column_name))
- CMPEXP used in HSJN is above the UNION
- DTQ (Directed TQ) is above the same UNION and above CMPEXP is
used to direct results.
The following is a simplified example of this scenario. There
is a Compute Expression (CMPEXP) in the plan below the HSJN but
above the UNION:
HSJOIN
/---+----\
DTQ TBSCAN
| |
PIPE TABLE: TAB_NAME
|
CMPEXP
|
UNION
/------------+------------\
HSJN TA |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2LUW v10.1 *
****************************************************************
* PROBLEM DESCRIPTION: *
* In DB2 DPF environments only, a specific type of query and *
* resulting access might return wrong results. *
* *
* The following conditions will cause this issue to occur: *
* - The query must have joins with join predicates using an *
* explicit expression or an implicit data type conversion. An *
* example of an explicit expression is "coalesce(col1,col2) = *
* coljoin". *
* - The access plan must have a UNION with a Directed TQ *
* (DTQ) *
* above it. This is most likely to happen in a query that *
* does a *
* full outer join. *
* - The access plan must also have a Hash Join (HSJOIN) chosen *
* with the expression above the UNION. *
* *
* You can review access plans through either the explain *
* utility *
* and the db2exfmt command or using Optim Visual Explain. *
* *
* The problem scenario can only happen if you have all of *
* these in *
* the plan: *
* - UNION *
* - HSJN above UNION with the JOIN predicate using the result *
* of *
* expression (for example: COALESCE(Q9.$C3, Q9.$C1) = *
* Q10.column_name)) *
* - CMPEXP used in HSJN is above the UNION *
* - DTQ (Directed TQ) is above the same UNION and above CMPEXP *
* is *
* used to direct results. *
* *
* The following is a simplified example of this scenario. *
* There *
* is a Compute Expression (CMPEXP) in the plan below the HSJN *
* but *
* above the UNION: *
* *
* HSJOIN *
* /---+----\ *
* DTQ TBSCAN *
* | | *
* PIPE TABLE: TAB_NAME *
* | *
* CMPEXP *
* | *
* UNION *
* /------------+------------\ *
* HSJN TA *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 v10.1 FP4 *
**************************************************************** |
| Local Fix: |
To avoid hitting this APAR, execute the following and then
recycle the instance:
db2set DB2_TCG_DEFAULT_OPTIONS="set multi_stream_pushdown off" |
| available fix packs: |
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 04.12.2013
21.03.2014
26.05.2014 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.4
|
 |
