|
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) |
