DB2 FOR LUW / DB2FORLUW / 970 - DB2

Under the db2_workload="SAP" setting, one of the 
optimizations enables Query Rewrite to produce (copy) a 
filtering Semijoin from the row preserving side to the null 
producing side of an Outer Join. 
 
You are experiencing this problem if: 
1) You are using db2_workload="SAP" 
2) You are using Data Partitioning Facility 
3) Your query involves an Outer Join, and the row perserving 
side has a filtering Inner Join and is reduced to a small number 
of rows 
4) The Null Producing side (table) is partitioned, and there are 
no predicates on the partitioning columns 
5) The Null Producing side (table) is joined to the Row 
Perserving side via predicates that are not on the 
Null Producing side's partitioning columns 
7) The Null Producing side is very large 
8) When you gather the Query Access Plan (either using Explain, 
Optimum Query Tuner, or Visual Explain), you see that the Null 
Producing side is large (many Millions of rows) and is Broadcast 
TQed to the cordinator node, unfiltered. 
 
If all of the above conditions are met, then you are very likely 
experiencing the problem this APAR fixes.

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users of the DB2 Data Partitioning Facility in combination   * 
* with SAP.                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Under the db2_workload="SAP" setting, one of the             * 
* optimizations enables Query Rewrite to produce (copy) a      * 
* filtering Semijoin from the row preserving side to the null  * 
* producing side of an Outer Join.                             * 
*                                                              * 
* You are experiencing this problem if:                        * 
* 1) You are using db2_workload="SAP"                          * 
* 2) You are using Data Partitioning Facility                  * 
* 3) Your query involves an Outer Join, and the row perserving * 
* side has a filtering Inner Join and is reduced to a small    * 
* number                                                       * 
* of rows                                                      * 
* 4) The Null Producing side (table) is partitioned, and there * 
* are                                                          * 
* no predicates on the partitioning columns                    * 
* 5) The Null Producing side (table) is joined to the Row      * 
* Perserving side via predicates that are not on the           * 
* Null Producing side's partitioning columns                   * 
* 7) The Null Producing side is very large                     * 
* 8) When you gather the Query Access Plan (either using       * 
* Explain,                                                     * 
* Optimum Query Tuner, or Visual Explain), you see that the    * 
* Null                                                         * 
* Producing side is large (many Millions of rows) and is       * 
* Broadcast                                                    * 
* TQed to the cordinator node, unfiltered.                     * 
*                                                              * 
* If all of the above conditions are met, then you are very    * 
* likely                                                       * 
* experiencing the problem this APAR fixes.                    * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade the DB2 Server to DB2 for Linix, Unix and Windows    * 
* Version 9.7 Fix Pack 6 or higher.                            * 
****************************************************************

Rewrite query to include semijoin filtering on the null 
producing side too.

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
 

Once upgraded to DB2 for Linux, Unix and Windows Version 9.7 Fix 
Pack 6 or higher, the user will see the filtering Semijoin under 
the conditions described in the problem description.

keiner bekannt / siehe Local-Fix

Vorgänger  : APAR is sysrouted TO one or more of the following: IC84545 
Nachfolger : 

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

14.03.2012
12.06.2012
12.06.2012

9.7.,
9.7.FP6