DB2 - Problem description
Problem IC85193 | Status: Closed |
ARRAY ALLOCATION FAILS IN STORED PROCEDURE WITH SQL20442N EVEN WHEN APPLHEAPSZ AND APPL_MEMORY ARE SET TO AUTOMATIC | |
product: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problem description: | |
Array allocation may fail intermittently with sql20442n even when APPLHEAPSZ and APPL_MEMORY are set to AUTOMATIC. The following message will be returned : SQL20442N There is not enough storage to represent the array value. When Instance Memory is set to a fixed value, the calculation of how much memory is available is incorrect (this does not affect STMM, only the array allocation pass/fail check). The calculation does not take into account the ability to use "cached" Instance Memory, which is very flexible instance memory usage and can be easily reduced to make room for other requirements. STMM in particular treats cached memory as available, and may tune a system such that the free Instance memory is largely backed by cached instance memory, i.e. making it more likely that the unnecessary SQL20442 errors will occur. This problem occurs only when an INSTANCE_MEMORY limit is enforced by using a fixed value OR through a license limit, AND both APPLHEAPSZ and APPL_MEMORY are AUTOMATIC. If instance memory usage is very close to the limit (monitor with db2pd -dbptnmem), SQL20442 errors are likely. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Systems where an INSTANCE_MEMORY limit is in effect * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 10.1 Fix pack 2 * **************************************************************** | |
Local Fix: | |
Set APPLHEAPSZ to a fixed value high enough to cover any requirements for cust's workload. There is no side-effect in setting it to a very high value as no memory is pre-allocated, and the setting does not generally influence the amount of memory DB2 will allocate/cache. | |
available fix packs: | |
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows | |
Solution | |
First fixed in DB2 Version 10.1 Fix Pack 2 | |
Workaround | |
See Local Fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 09.07.2012 17.12.2012 17.12.2012 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) | |
10.1.0.2 | |
10.5.0.2 |