I am widely used Arrays data types in my stored procedures in DB2 9.5. Sometimes, the application folks report the following exceptions from their locks -

ErrorText User generated exception-EDS4023---20442 - SQL20442N There is not enough storage to represent the array value. SQLSTATE=57011 -CHKPT006-57011 -

EDS4023 - - -20442 - SQL20442N There is not enough storage to represent the array value. SQLSTATE=57011 - CHKPT006 - 57011
ErrorDetail . The error description gives below explanation -

SQL20442N There is not enough storage to represent the array value.Explanation:

The amount of memory required to represent an array value is larger than
the maximum allowed for the system.

User response:

Possible solutions may include:
* Correct the statement that is attempting to create the array value
* Reduce the number of elements in the array or the sizes of some of
the elements
* Increase the value of APPLHEAPSZ or APPL_MEMORY if they are not set
to AUTOMATIC
* Increase the amount of physical memory available to the system.

sqlcode: -20442

sqlstate: 57011


I want to take the right approach to solve this problem as this application is highly visible to my company. Personally I feel that this might be due to STMM because of which the application heap is sometimes not able to re-size itself to accomodate enough memory for array data processing. I am getting such error messages in db2diag.log ....

2010-12-12-11.01.44.282680-480 I14606722A638 LEVEL: Error
PID : 6308092 TID : 7187 PROC : db2sysc 0
INSTANCE: ieds0t00 NODE : 000 DB : DEDS0T01
APPHDL : 0-36159 APPID: *LOCAL.DB2.101212183143
AUTHID : RCARR2
EDUID : 7187 EDUNAME: db2stmm (DEDS0T01) 0
FUNCTION: DB2 UDB, Self tuning memory manager, stmmUpdateDBConfig, probe:275
MESSAGE : ZRC=0x82AE00A7=-2102525785=STMM_CONFIG_UPDATE_FAILED
"An attempted configuration update failed"
DATA #1 : String, 79 bytes
Error updating parameter Database_memory, updateValue = 213080, sqlcode = -5047

2010-12-12-11.04.44.295036-480 I14607361A490 LEVEL: Warning
PID : 6308092 TID : 7187 PROC : db2sysc 0
INSTANCE: ieds0t00 NODE : 000 DB : DEDS0T01
APPHDL : 0-36159 APPID: *LOCAL.DB2.101212183143
AUTHID : RCARR2
EDUID : 7187 EDUNAME: db2stmm (DEDS0T01) 0
FUNCTION: DB2 UDB, Self tuning memory manager, stmmEnforceMinSizeConstraints, probe:2358
MESSAGE : Unable to find donor to satisfy minSize constraint

2010-12-12-11.04.44.296926-480 E14607852A642 LEVEL: Warning
PID : 6308092 TID : 7187 PROC : db2sysc 0
INSTANCE: ieds0t00 NODE : 000 DB : DEDS0T01
APPHDL : 0-36159 APPID: *LOCAL.DB2.101212183143
AUTHID : RCARR2
EDUID : 7187 EDUNAME: db2stmm (DEDS0T01) 0
FUNCTION: DB2 UDB, Self tuning memory manager, stmmCheckIfFreeMemoryIsEnoughForSizeIncr, probe:663
MESSAGE : ZRC=0xFFFFEC49=-5047
DATA #1 : String, 143 bytes
There is not enough free memory for size increase. Free memory in pages: Physical memory = 0, Instance memory = 954189, Database memory = 33840

Just wanted to if someone has any experience on such issues before and if he can give a right approach to the problem.

Thanks
Rahul Anand

I have not had this problem as I tend to avoid arrays. Relying on STMM is not the correct approach. STMM is reactive rather than proactive. That means that it makes changes after they are needed, not before.

You need let us know what the array is like and how many elements there are that the problem occurs.

Andy

Sounds like you should be doing these 2 items from the above.

I have the following arrays defined.

CREATE TYPE BIGINTARRAYAS BIGINT ARRAY [] ;
CREATE TYPE CHARARRAYAS VARCHAR(32672) ARRAY [] ;
CREATE TYPE DATEARRAYAS DATE ARRAY [] ;
CREATE TYPE POLICYARRAYAS CHAR(11) ARRAY [] ;
CREATE TYPE PSRCARRAYAS CHAR(8) ARRAY [] ;
CREATE TYPE VARCHARARRAYAS VARCHAR(32672) ARRAY [] ;

But at any point in time, the application will not send elements which will be more than 100 ...so the maximum size of these arrays can be 100.

This issue was resolved by changing the AUTOMATIC nature of both APPLHEAPSZ and APPL_MEMORY to a fixed value. Seems that STMM is somethng unreliable, especially when you are working in a shared environment. You need to allocate fixemd memory limits to core database elements else chances are there that DB2 will not be able to allocate sufficient memory as required.

Thanks
Rahul Anand
rahul0705@yahoo.com
IBM India