DB2 - Problem description
Problem IT30839 | Status: Closed |
AN OUTER JOIN QUERY MIGHT BE WRONGFULLY RE-WRITTEN TO AN INNER JOIN QUERY WHEN MQT MATCHING IS ENFORCED FOR SHADOW TABLES. | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
Description: When the following registries are set and there's a matching shadow table for the base table used in the original query, the outer join can be wrongfully converted into an inner join. db2 "set CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION REPLICATION" db2 "SET CURRENT REFRESH AGE ANY" With registries and mqt enforced, the table used in the outer join could be replaced with its matching MQT. After replacing the table with its MQT, the outer join could wrongfully turn into inner join which could cause a wrong result. Workaround: Disable shadow tables when optimizing when optimizing the processing of dynamic SQL queries. db2 "set CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION NONE" | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 10.FP11 * **************************************************************** | |
Local Fix: | |
Disable shadow tables when optimizing when optimizing the processing of dynamic SQL queries. db2 "set CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION NONE" | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 10.FP11 * **************************************************************** | |
Comment | |
Upgrade to Db2 10.FP11 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 04.11.2019 10.03.2020 10.03.2020 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |