IBM Support

IT20456: 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:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 10.5 Fix Pack 9 or higher                     *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 10.5 Fix Pack 9
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT20456

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A50

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-05-04

  • Closed date

    2017-09-29

  • Last modified date

    2017-09-29

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

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

    IT21182

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA50 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":"10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 September 2017