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.
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
* Increase the value of APPLHEAPSZ or APPL_MEMORY if they are not set
* Increase the amount of physical memory available to the system.
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 ....
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.
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.