DB2 - Problem description
Problem IT37840 | Status: Closed |
INNER JOIN OF AT LEAST 4 COLUMN-ORGANIZED TABLES MIGHT RETURN WRONG RESULTS UNDER SPECIFIC CONDITIONS | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
Under the following conditions, the query might return fewer rows than expected. 1. at least 4 column-organized tables are joined using inner joins 2. at least 3 of those tables have a primary key or unique index 3. simple 'column=column' join predicates reference all the columns in the primary keys/unique indexes 4. some of the columns appear in more than one 'column=column' join predicate 5. it is NOT the case that all the keys are identical and all join to each other on all the columns The problem is dependent on the choice of access plan and the data. If the access plan (obtained via the EXPLAIN facility and db2exfmt) has the following specific characteristics, the query _might_ be vulnerable: FILTER | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Db2 Version 11.1 * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 Version 11.1 Mod 4 Fix Pack 7. * **************************************************************** | |
Local Fix: | |
The problem can be avoided by setting registry variable: db2set -immediate DB2_EXTENDED_OPTIMIZATION="COLJOIN 1" If the registry variable is already set to a value, append "COLJOIN 1" to the existing setting, separated by a comma (no spaces). | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * Db2 Version 11.1 * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 Version 11.1 Mod 4 Fix Pack 7. * **************************************************************** | |
Comment | |
Problem was first fixed in Db2 Version 11.1 Mod 4 Fix Pack 7. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 02.08.2021 17.04.2022 20.04.2022 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |