|
Problem IC67829
|
Status: Closed |
Optimzer might yield a poor-performing access plan for queries that can make use of replicated MQTs. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The problem occurs when the optimizer chooses a plan that
repartitions the stream from the replicated MQT even when the
source and target partition groups are same. This
repartitioning is unnecessary, because the data is already
available on all partitions. The fix will avoid such
repartitioning.
Example of unnecessary repartitioning is DTQ(26) below:
...
|
100.9657
DTQ
( 26)
30.41432
2
|
760.7598
TBSCAN
( 27)
30.24803
2
|
760.7598
SORT
( 28)
30.23759
2
|
760.7598
>HSJOIN
( 29)
30.17059
2
/------+-------\
760.7598 1
IXSCAN IXSCAN
( 30) ( 31)
10.60995 10.54317
1 1
| |
198982 18910
INDEX: HARI INDEX: HARI
IND_REPL_MQT2 IND_REPL_MQT1
Q2 Q1
Partial details of TQ(26):
Input Streams:
-------------
...
Partition Map ID:3
...
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
...
Partition Map ID:3
...
Partition Column Names:
----------------------
+1: 'XYZ'+2: Q5.COLA |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users with replicated MQT. *
****************************************************************
* PROBLEM DESCRIPTION: *
* Without this APAR, customer is exposed to the issue as *
* described in the "ERROR DESCRIPTION" section. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7, Fixpack 5. *
**************************************************************** |
| Local Fix: |
n/a |
| available fix packs: |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
First fixed in DB2 Version 9.7, Fixpack 5. |
| Workaround |
n/a |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC74787
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 13.04.2010
03.01.2012
09.01.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP4,
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
