|
Problem IC82035
|
Status: Closed |
OUTERJOIN SEMIJOIN NOT APPLIED WHEN NO PREDICATES ON PARTITIONS COLUMN(S) |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| 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. |
| Problem Summary: |
****************************************************************
* 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. *
**************************************************************** |
| Local Fix: |
Rewrite query to include semijoin filtering on the null
producing side too. |
| available fix packs: |
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 |
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. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC84545
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 14.03.2012
12.06.2012
12.06.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.,
9.7.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
