|
Problem IC79992
|
Status: Closed |
STATEMENT HEAP USAGE IS HIGH WHEN THERE ARE MANY JOINS AND A DATA PARTITIONING KEY IS USED IN LOCAL PREDICATES |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
You might encounter large statement heap usage, which can
produce an SQL0101N error or SQL0437W warning, when compiling a
SQL statement with a large number of joins between tables
partitioned by range. This problem can occur when the joins
reference the range partitioning key and there are also
predicates that equate the key to a constant, such as
DP_KEY_COLUMN = 'Y'. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Table partitioning data organization *
****************************************************************
* PROBLEM DESCRIPTION: *
* You might encounter large statement heap usage, which can *
* produce an SQL0101N error or SQL0437W warning, when *
* compiling a SQL statement with a large number of joins *
* between tables partitioned by range. This problem can occur *
* when the joins reference the range partitioning key and *
* there are also predicates that equate the key to a constant, *
* such as DP_KEY_COLUMN = 'Y'. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Version 9.7 Fix Pack 6 or later release *
* containing the fix. *
**************************************************************** |
| Local Fix: |
You could try to make the predicates look complex to prevent the
compiler generate join predicates. For example, modify predicate
DP_KEY_COLUMN = 'Y' to DP_KEY_COLUMN || '' = 'Y'. However,
this will prevent it from consideration as an index range
delimiting predicate and for data partition elimination, and the
query runtime
performance may be affected. |
| 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 |
The problem was first fixed in Version 9.7 Fix Pack 6 |
| Workaround |
See Local Fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC84540
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 24.11.2011
08.06.2012
08.06.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
