|
Problem IT00933
|
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 / A50 - 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: *
* Database Partitioning Feature *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 10.5.0.4. *
**************************************************************** |
| 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 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
|
| Solution |
The problem is first fixed in DB2 version 10.5.0.4. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 09.04.2014
09.04.2014
07.04.2015 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.4
|
 |
