DB2 - Problem description
Problem IT23341 | Status: Closed |
WRONG RESULTS ARE POSSIBLE WHEN EXECUTING A SQL STATEMENT ELIGIBLE FOR ZIGZAG JOIN ON A NON-PARTITIONED INDEX | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
Wrong results are possible when executing a SQL statement that fits the required criteria for a zigzag join on a data partitioned fact table, where the index used is not partitioned, and partition elimination is eligible through a join predicate between one or more dimension tables and the fact table. Under these conditions, if you collect an EXPLAIN of the SQL statement, you will see a ZZJOIN operator and at the fact table index access you will see the following in the plan operator details: DPESTFLG: (Number of data partitions accessed are Estimated) TRUE DPNUMPRT: (Number of data partitions accessed) 1 DP Elim Predicates: ------------------ Range 1) Start Predicate: (Q1.DIM_ID = Q2.DIM_ID) Stop Predicate: (Q1.DIM_ID = Q2.DIM_ID) Where DIM_ID can be any column joined between the dimension and fact table and it doesn't need to exist in the fact table index as long as there is at least one zigzag eligible join with that dimension on one of the fact table's index key columns. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Please, see error Description and install this fix. * **************************************************************** | |
Local Fix: | |
You can avoid the problem by setting DB2_REDUCED_OPTIMIZATION="ZZJN OFF". This can be applied using db2set or as a REGISTRY guideline. If using db2set, then static sections should be recompiled. In addition, if using the -immediate option of db2set, the package cache must be flushed to ensure re-compilation of the impacted SQL statements. | |
Solution | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : follow-up : IT23874 IT23875 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 30.11.2017 16.07.2018 16.07.2018 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |