DB2 FOR LUW / DB2FORLUW / 970 - DB2

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. 
 
This APAR addresses two problems: 
 
1. Array allocation uses system memory statistics similar to 
STMM to decide whether to pass/fail allocations.  This approach 
may be excessively conservative.  The goal of capping array 
allocations is to limit single very large allocations only, 
which is easy to trigger if one is not careful with array usage. 
eg. through use of the array_agg function.  This APAR implements 
a threshold of 1MB below which system memory statistics will not 
be checked (this also removes some overhead incurred from the 
underlying OS monitoring APIs).  This problem occurs only when 
INSTANCE_MEMORY, APPLHEAPSZ, and APPL_MEMORY are all set to 
AUTOMATIC. 
 
2. 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. 
 
In db2diag.log,  "runtime interpreter, 
arrayControlBlock::getMem" can generate error message. 
if  "db2pdcfg -catch sqlcode=-20442 stack" is set to 
capture this error, the following kind of stack would show 
in db2diag.log: 
 
2012-06-13-11.12.44.941978-300 I5550918E1328       LEVEL: Event 
PID     : 8005                 TID  : 46921766922560PROC : 
db2sysc 1 
INSTANCE: bculinp              NODE : 001          DB   : 
PCFPROD 
APPHDL  : 0-1761               APPID: *N0.bculinp.120613161237 
AUTHID  : BCULINP 
EDUID   : 170051               EDUNAME: db2agntp (PCFPROD) 1 
FUNCTION: DB2 UDB, runtime interpreter, 
arrayControlBlock::getMem, probe:550 
DATA #1 : <preformatted> 
Caught sqlcode -20442.  Dumping stack trace. 
CALLSTCK: (Static functions may not be resolved correctly, as 
they are resolved to the nearest symbol) 
  [0] 0x00002AAAABA33693 pdLogPrintf + 0x3B3 
  [1] 0x00002AAAAC1B5383 pdInvokeCatchInterface + 0x1BB 
  [2] 0x00002AAAAC242A8A 
_Z12sqlzeSqlCodeP8sqeAgentjmjP5sqlcaitP13__va_list_tag + 0x13A 
  [3] 0x00002AAAAD792A3A sqlrrSqlCode + 0xE2 
  [4] 0x00002AAAAD8AF34F 
_ZN17arrayControlBlock6getMemEP8sqlrr_cbmPPv + 0x117 
  [5] 0x00002AAAAD8B1D8B 
_ZN20sqlriArrayDescriptor11allocateDynEP8sqlrr_cbm + 0x71 
  [6] 0x00002AAAAD9D29EC 
_Z33sqlriSectInitializeFirstRemoteMPPP8sqlrr_cbP9sqlri_shdP14sql 
riDssHeader + 0xCFC 
  [7] 0x00002AAAAD8EE34D 
_Z15sqlriReceiveDssP8sqlrr_cbP16sqlkdRqstRplyFmtPl + 0x87D 
  [8] 0x00002AAAAD7301AB _Z16sqlrr_dss_routerP8sqlrr_cb + 0x27F 
  [9] 0x00002AAAAD7324CC 
_Z21sqlrr_subagent_routerP8sqeAgentP12SQLE_DB2RA_T + 0xD5A

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 7                        * 
****************************************************************

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.

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
 

First fixed in Version 9.7 Fix Pack 7

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC85190 IC85193 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

25.06.2012
10.12.2012
21.03.2014

9.7.FP7