PM89016: HIGH LOCKING ACTIVITY FOR SINGLETON SELECTS WITH ISO(CS) AND CURRENTDATA(YES)

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • high locking activity for singleton selects with ISO(CS) and
    CURRENTDATA(YES)
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 9 and DB2 10 for z/OS who   *
    *                 have singleton SELECTs and use ISOLATION(CS) *
    *                 with CURRENTDATA(YES)                        *
    ****************************************************************
    * PROBLEM DESCRIPTION: Possible high locking activity with     *
    *                      singleton SELECTs when using            *
    *                      ISOLATION(CS) with CURRENTDATA(YES)     *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    High locking activity is possible with a large volume of
    singleton SELECTs (SELECT statements which return only
    one row) when using ISOLATION(CS) with CURRENTDATA(YES).
    Each singleton SELECT under ISOLATION(CS) with CURRENTDATA(YES)
    obtains a lock, which could add up to a high level of locking
    activity when such singleton SELECTs are executed in mass
    quantities.  To reduce the amount of locking, for singleton
    SELECTs which are not referencing a table in a
    partition-by-growth (PBG) table space and are being accessed
    with an index that is matching on all index columns with
    EQUAL predicates, lock avoidance will be used.
    
    For example:
      SELECT C1, C2, C3
      FROM T1
      WHERE C1 = 1 AND
            C2 = 10;
    
      IX1 (C1, C2)
    
    This is a singleton select with bind parameters ISOLATION(CS)
    and CURRENTDATA(YES).
    T1 is not in a PBG table space, and index IX1 is chosen.
    
    This query qualifies for lock avoidance.
    

Problem conclusion

  • Lock avoidance is used for certain types of singleton SELECTs
    when ISOLATION(CS) is used with CURRENTDATA(YES) to reduce
    the amount of locking activity, as described above.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM89016

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-05-14

  • Closed date

    2013-11-22

  • Last modified date

    2014-04-29

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

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

    UI12870 UI12871

Modules/Macros

  • DSNIONX2 DSNXOGP  DSNXOGPS DSNXOMI  DSNXOMS  DSNXOMXI
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI12870

       UP13/12/07 P F312

  • R910 PSY UI12871

       UP13/12/07 P F312

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

910

Reference #:

PM89016

Modified date:

2014-04-29

Translate my page

Machine Translation

Content navigation