Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2010
    Location
    India
    Posts
    4

    Unanswered: Memory issues with Arrays in 9.5

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Sounds like you should be doing these 2 items from the above.
    * 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.

  4. #4
    Join Date
    Dec 2010
    Location
    India
    Posts
    4
    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.

  5. #5
    Join Date
    Dec 2010
    Location
    India
    Posts
    4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •