IBM Support

IC77166: WITH STMT_CONC=LITERALS, PACKAGE CACHE PERFORMANCE CAN DEGRADE

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • In systems that generate large numbers of unique statements with
    literals, turning statement concentrator on reduces the number
    of unique SQL statements (concentrated statements) that are
    compiled.     However, the dynamic SQL cache can be
    over-populated by statement entries of the original statement
    (ie, the pre-concentrated statements that contain the literals).
         These statements are themselves not compiled - but are
    meant to help route repeating statements containing literals to
    the same 'concentrated statement'.    However due to the reduced
    number of concentrated statements there is much more room for
    many of the 'pre-concentrated statements'.
    
    Depending on the package cache size and the number of unique
    literal containing statements that are issued, the package cache
    can contain many thousands of pre-concentrated statement
    entries.   This can result in the package cache containing long
    chains and increasing the cost of lookup in the package cache
    
    Using db2pd -db <dbname> -dynamic, one can evaluate the contents
    of the dynamic SQL part of the package cache and determine how
    the contents of the dynamic SQL cache and the amount of
    statements containing literals can be monitored/evaluated.
    

Local fix

  • Decreasing the package cache can help reduce the impact of this
    by forcing earlier eviction of the unconcentrated statements
    
    Disabling STMT_CONC will avoid this issue - although will lose
    the benefits of statement concentration
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * databases with stmt_conc=literals                            *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Package cache lookup degrades                                *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * upgrade to DB2 for LUW v9.7 fixpack 5                        *
    ****************************************************************
    

Problem conclusion

  • Fixed in DB2 for LUW V9.7 fixpack 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC77166

  • 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

    2011-06-26

  • Closed date

    2012-01-02

  • Last modified date

    2012-01-02

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

  • R980 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC77166

Modified date: 02 January 2012