IBM Support

IT21182: INTERRUPTED QUERY/SORT OPERATION CAUSES STMM TO UNNECESSARILY INCREASE SORT CONFIGURATION (SHEAPTHRES_SHR)

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When a sort operation is aborted, it is assumed that a larger
    sort configuration would have avoided the circumstances
    triggering the abort.  The information used by STMM is
    intentionally heavily biased towards increasing the sort
    configuration.  While this is a reasonable approach when an
    out-of-memory error is the reason for a sort operation not
    completing, this is not a good assumption for interrupted
    queries - they could be long-running and interrupted by a
    user/application for a variety of reasons.
    
    An isolated interrupted query that leads to a single errant
    tuning increase of sort (sheapthres_shr) should have negligible
    overall impact on a system's performance.  However, if
    interrupted queries are continuous/frequent, the sort
    configuration may be tuned excessively high to the detriment of
    overall performance.  The situation may become severe and
    prolonged due to bufferpools being decreased to unacceptably low
    sizes.
    
    Interrupted queries can be identified in the db2diag.log by the
    following message/code:
    
    FUNCTION: DB2 UDB, runtime interpreter, sqlrisrt, probe:50
    DATA #2 : Hexdump, 4 bytes
    0x00002AE4D9FFD48C : 0300 0480
    ....
    
    Or on big-endian architectures : 8004 0003
    

Local fix

  • manually adjust the sort configuration to correct the
    configuration :
      db2 connect to <database>
      db2 update db cfg for <database> using sheapthres_shr <value>
    automatic
    
    If the interrupts are frequent and cannot be avoided, it may be
    necessary to remove sort from automatic/STMM tuning until the
    fix for this APAR is applied.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Systems 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 solved in DB2 Version 11.1 Mod 3 Fix Pack 3
    

Temporary fix

  • see Local Fix
    

Comments

APAR Information

  • APAR number

    IT21182

  • 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-06-26

  • Closed date

    2018-03-15

  • Last modified date

    2018-03-15

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

    IT20456

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

Modules/Macros

  • stmm
    

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