|
Problem IT17556
|
Status: Closed |
INCORRECT RESULTS ARE POSSIBLE WHEN JOIN AGAINST CDE TABLES IS DONE AND AN UNDOCUMENTED JOIN SUPPORT REGISTRY VARIABLE SET |
| product: |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 |
| Problem description: |
If an undocumented join support registry variable is set and
the plan has a left early out join with residual predicate,
then sql might loose rows if:
1. Join between 3 or more column organized tables, using
equality predicates
2. One of the tables has a primary key constraint that is
referenced in the join
3. The table with the primary key constraint joins 2 or more
other tables on the primary key constraint, but not the same
columns; e.g. if primary key constraint is on T1 (a,b), then T1
joins T2 on column a, and T1 joins T3 on column b. It can
likely occur with single column primary key as well; e.g using
the same example, but change primary key to (a). As long as the
residual predicate completes the qualification of the primary
key, then we should hit this problem.
Example of exfmt:
3) HSJOIN: (Hash Join)
Cumulative Total Cost: 26173.5
Cumulative CPU Cost: 2.31262e+10
Cumulative I/O Cost: 5252
Cumulative Re-Total Cost: 26173.5
Cumulative Re-CPU Cost: 2.31262e+10
Cumulative Re-I/O Cost: 5252
Cumulative First Row Cost: 26173.5
Estimated Bufferpool Buffers: 4
Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT <<<<<<<<<<<<<<<<
SEMIJOIN: (Semi-join flag)
FALSE
Predicates:
----------
2) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1
Predicate Text:
--------------
(Q1.OBJVERS = Q3.OBJVERS)
3) Residual Predicate,
<<<<<<<<<<<<<<<<<<<<
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0833333
Predicate Text:
--------------
(Q2.SID_ZLOG_MOD = Q1.SID). |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 11.1 Mod1 Fix Pack 1 *
**************************************************************** |
| Local Fix: |
UNSET AN UNDOCUMENTED JOIN SUPPORT REGISTRY VARIABLE AND
RESTART INSTANCE |
| available fix packs: |
DB2 Version 11.1 Mod1 Fix Pack1 iFix001 for Linux, UNIX, and Windows
DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
|
| Solution |
First fixed in DB2 11.1 Mod1 Fix Pack 1 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 18.10.2016
11.05.2017
11.05.2017 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 11.1.1.1
|
 |
