DB2 FOR LUW / DB2FORLUW / A10 - DB2

The DB2 optimizer might produce a non-optimal access plan for 
SQL statements that are eligible for star-join optimizations 
when the DB2_WORKLOAD=SAP aggregate registry variable is set in 
a partitioned database environment. 
 
When this problem is encountered, the optimizer will produce a 
non-optimal access plan consisting of a stack of hash join 
(HSJOIN) operators with multiple large dimension tables, located 
in a single database partition, that are broadcast to complete 
the joins.  You can gather an EXPLAIN of the affected queries to 
confirm this pattern of operations in the access plan.

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users on DB2 V10.1 Fixpak 4 and below                        * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v10.1 Fixpak 5                                * 
****************************************************************

You can try any of the following options for the tables located 
in a single database partition: 
 
1) Redistribute the table across multiple partitions, located in 
the same partition group as the fact table, using the join 
columns as the distribution key.  This can also be accomplished 
as a temporary fix by creating a MQT as (select * from table) 
that is located in the same partition group as the fact table 
 
2) Create a replicated MQT of the table, locating the MQT in the 
same partition group as the fact table

First fixed in DB2 v10.1 Fixpak 5

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

20.06.2014
15.07.2015
15.07.2015