IBM Support

IC84891: ARRAY ALLOCATION FAILS IN STORED PROCEDURE WITH SQL20442N EVEN WHEN APPLHEAPSZ AND APPL_MEMORY ARE SET TO AUTOMATIC

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error 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.
    
    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
    

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.
    

Problem summary

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

Problem conclusion

  • First fixed in Version 9.7 Fix Pack 7
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC84891

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-06-25

  • Closed date

    2012-12-10

  • Last modified date

    2014-03-21

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC85190 IC85193

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
21 March 2014