|
Problem IC84540
|
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 / A10 - 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 DB2 Version 10 Fix Pack 1 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 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 |
The problem was fixed in DB2 Version 10 Fix Pack 1 |
| Workaround |
See Local Fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 14.06.2012
03.06.2013
03.06.2013 |
| 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
|
|
