IBM Support

IT23173: DECREASED PERFORMANCE, STMMSTARTHJMONITORIFNECESSARY ERRORS LOGGED IN CONSTRAINED/VOLATILE STMM ENVIRONMENTS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • With STMM tuning of SORT parameters enabled (SORTHEAP,
    SHEAPTHRES set to AUTOMATIC, SELF_TUNING_MEM ON), a system may
    experience degraded performance over time along with db2diag.log
    errors such as :
    
    2017-10-05-06.58.01.086620-180 I21833545A697       LEVEL: Error
    FUNCTION: DB2 UDB, Self tuning memory manager,
    stmmStartHJMonitorIfNecessary, probe:306
    MESSAGE : ZRC=0x82120001=-2112749567=SQLR_NONSEVERE_PGM_ERROR
             "Non-severe programming error."
             DIA8532C An internal processing error has occurred.
    DATA #1 : String, 32 bytes
    ERROR starting hash join monitor
    
    In DB2 version 10.5, the throttling of shared sort reservations
    was made more aggressive in order to prevent memory
    overcommitment. For the most severe case, minimum requirements
    needed to satisfy an SQL request may be denied with SQL0955 if
    the shared sort reservation level reaches 125% of the configured
    amount.  STMM simulates what-if configuration scenarios as part
    of automatic sort tuning, and these scenarios-- which determine
    performance results under different configurations--may also
    simulate similar aggressive throttling.  Aggressive throttling
    should only occur in constrained tuning situations, which are
    typically temporary in STMM-tuned environments (due to volatile
    workload).
    
    Two problems may occur as a result of aggressive throttling
    during the running of STMM simulation
    1. the memory used to support the simulation may be leaked.
    this memory comes from the shared sort pool, and is part of the
    shared sort reservation level.
    2. the simulation may fail, which prevents STMM from sensing the
    need to increase the sort configuration.
    
    The problem can be identified by the symptoms mentioned above,
    and the fact that a higher-than-expected shared sort reservation
    level and memory usage can be observed when there is little
    activity on the system.
    

Local fix

  • Fixed SORTHEAP, SHEAPTHRES_SHR settings can be used by observing
    the tuning levels set by STMM during periods of normal activity.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * System using STMM (self-tuning memory manager)               *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * upgrade to DB2 Version 11.1 Mod 3 Fix Pack 3                 *
    ****************************************************************
    

Problem conclusion

  • Problem first fixed in DB2 Version 11.1 Mod 3 Fix pack 3
    

Temporary fix

  • see Local Fix
    

Comments

APAR Information

  • APAR number

    IT23173

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-11-15

  • Closed date

    2018-03-15

  • Last modified date

    2018-03-15

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

    IT22850

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

Modules/Macros

  • sqs
    

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 March 2018