|
Problem IC84545
|
Status: Closed |
OUTERJOIN SEMIJOIN NOT APPLIED WHEN NO PREDICATES ON PARTITIONS COLUMN(S) |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - 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: *
* DB2 v10.1 with Data Partitioning Facility (DPF) *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade the DB2 V10.1 Linux Unix and Windows server to Fix *
* Pack 1. *
**************************************************************** |
| Local Fix: |
Rewrite query to include semijoin filtering on the null
producing side too. |
| available fix packs: |
DB2 Version 10.1 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
Upgrade to DB2 V10.1 Fix Pack 1. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 14.06.2012
02.11.2012
02.11.2012 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.1
|
 |
| 10.5.0.1
|
|
