|
Problem IC97349
|
Status: Closed |
IN A RARE SCENARIO, REPLICATED MQTS ARE NOT EXPLOITED TO IMPROVE PERFORMANCE OF QUERIES |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
In certain complex conditions using the Data Partition Feature
(DPF), replicated MQTs
are not exploited to improve performance of queries.
The Extended Explain Information section of the db2exfmt output
may show :
Diagnostic Identifier: 2
Diagnostic Details: EXP0062W The following MQT or
statistical view was
not eligible because one or more columns or
expressions referenced in the query were not found
in the MQT: "<schema>"."<replicated mqt name>".
The following is an example of the conditions required for this
problem:
1. Queries contain EXISTS or IN subquery in the predicates,
2. The objects outside of the subquery in the queries are
complex views,
3. The complex views reference tables in which there are
replicated MQTs
defined for those tables. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* all *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Cancun Release 10.5.0.4 (also known as Fix *
* Pack 4) or Later *
**************************************************************** |
| Local Fix: |
Rewrite the subquery to a semantic equivalent using GROUP BY
clause
e.g.
IN ( SELECT DISTINCT COL FROM BASETABLE )
can be rewritten to :
IN ( SELECT COL FROM BASETABLE GROUP BY COL ) |
| available fix packs: |
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
|
| Solution |
First Fixed in DB2 Cancun Release 10.5.0.4 (also known as Fix
Pack 4) |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 31.10.2013
13.10.2014
13.10.2014 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.4
|
 |
