Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: STMM does not allocate enough SHEAPTHRES_SHR

    DB2 ESE v9.5.1/AIX 6.1
    We have STMM turned on and our application was running an SQL statement and we got an SQL error:

    SQL0955C - Sort memory cannot be allocated to process the statement. Reason code = reason-code.
    with Reason code 2 - Insufficient shared memory in the database-wide shared memory area designated for sort processing. For reason code 2, increase the size of the database-wide shared memory area designated for sort processing by increasing the value of the SHEAPTHRES_SHR database configuration parameter.

    Here is excerpt from diag file:

    2009-08-25-16.22.51.431771-240 I24444221A1517 LEVEL: Error
    PID : 74814 TID : 43068 PROC : db2sysc 0
    INSTANCE: db2v9i1 NODE : 000 DB : XXXXXXXX
    APPHDL : 0-8660 APPID: XXX.XXX.1.31.39553.090825195952
    AUTHID : XXXXXXXXP
    EDUID : 43068 EDUNAME: db2agntp (XXXXXXXX) 0
    FUNCTION: DB2 UDB, runtime interpreter, sqlri_hsjnLoadTupColREVER, probe:5
    MESSAGE : ZRC=0x801A006D=-2145779603=SQLZ_CA_BUILT
    "SQLCA has already been built"
    DATA #1 : <preformatted>
    Sort mem usage: 172 shrd, 0 priv, 4 heap (31), 13562 thres shrd, 0 thres priv, active: sort 0 hsjn 3 small sort 0 active: OLA
    P 0 type 2 thres sig 0
    CALLSTCK:
    [0] 0x09000000057285A0 @45@sqlri_hsjnMemError__FP8sqlrr_cblT2P11sqlri_hsj noP13SQLO_MEM_POOLPciN22 + 0x170
    [1] 0x0900000005732198 sqlri_hsjnLoadTupleCollectionREVERSED__FP8sqlrr_cb P11sqlri_hsjnoP25sqlri_hsjnPartitionBucket + 0xA00
    [2] 0x0900000005731214 sqlri_hsjnReturnMatch__FP8sqlrr_cbP11sqlri_hsjno + 0x246C
    [3] 0x090000000458F4E4 sqlrihsjn__FP8sqlrr_cb + 0x114
    [4] 0x0900000004798F2C sqlriExecThread__FP8sqlrr_cbP12sqlri_opparm@glue10 94 + 0x3C
    [5] 0x0900000004799030 sqlrihsjn__FP8sqlrr_cb + 0xC
    [6] 0x0900000004734A40 sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm + 0xFFFFFFFFFFFFF878
    [7] 0x09000000062B702C sqlrr_smp_router__FP8sqlrr_cb + 0x5C8
    [8] 0x0900000006248B48 sqlrr_subagent_router__FP8sqeAgentP12SQLE_DB2RA_T + 0xFC8
    [9] 0x090000000514AC54 sqleSubRequestRouter__FP8sqeAgentPUiT2 + 0x7E0

    2009-08-25-16.23.12.106464-240 E24445739A619 LEVEL: Warning
    PID : 74814 TID : 15936 PROC : db2sysc 0
    INSTANCE: db2v9i1 NODE : 000 DB : XXXXXXXX
    APPHDL : 0-6281 APPID: *LOCAL.DB2.090823102318
    AUTHID : XXXADMIN
    EDUID : 15936 EDUNAME: db2stmm (XXXXXXXX) 0
    FUNCTION: DB2 UDB, Self tuning memory manager, stmmComputeMinSHEAPTHRES, probe:836
    MESSAGE : Unexpected minimum value for SHEAPTHRES_SHR - value automatically
    corrected - 13562 - 31 - 143395 - 13562 - 60 - 12 - 350.000000
    - 5 - 31 - 114716 - 155


    We would expect STMM to allocate sufficient memory for all processes but it did not happen this time. Anybody had problems like this? Thanks in advance
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Sep 2003
    Posts
    78
    getting errors like that myself...self-tuning with not enough resources to tune. let me know what you find out.

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Have you done a complete design over haul on your indexes? You have to make sure that you have a correct table/index design first before turning STMM ON.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Cougar8000
    Have you done a complete design over haul on your indexes? You have to make sure that you have a correct table/index design first before turning STMM ON.
    What do you mean? We just migrated this database from v8.2 to v9.5.1 and turned on STMM. No other changes have been done.

    Changing dbm and db cfg parameters is the simplest thing I can do. Our databases are rather large and complex (and highly critical) so changing design is impossible.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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