PM74776: INDEX PROBING ACQUIRED READ CLAIM WHEN INDEX PAGESET WAS OPENED,BUT NEVER DECLAIMED (TOO MANY UNNEC 13/11/15 PTF PECHANGE

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Too many claims with PAGE_RANGE = Y / 00C200EA
    due to index probing, that obtained the claim, but did not
    release the claim.
    
    Can be seen as unjustified with claim info on all partitions
    
    R01S6201 IX    0001 RW                TSO      SYSADM
                                                           (CS,C)
        -                  AGENT TOKEN 3
    R01S6201 IX    0002 RW                TSO      SYSADM
                                                           (CS,C)
        -                  AGENT TOKEN 3
    R01S6201 IX    0003 RW                TSO      SYSADM
                                                           (CS,C)
        -                  AGENT TOKEN 3
    R01S6201 IX    0004 RW                TSO      SYSADM
                                                           (CS,C)
        -                  AGENT TOKEN 3
    R01S6201 IX    0005 RW                TSO      SYSADM
                                                           (CS,C)
        -                  AGENT TOKEN 3
    R01S6201 IX    0006 RW                TSO      SYSADM
                                                           (CS,C)
        -                  AGENT TOKEN 3
    
    ...
    
    DB2 should not claim all partitions when executing
    this SQL with PAGE_RANGE = Y.
    
    It may cause RESOURCE UNAVAILABLE problems, eg during load
    since claims are held.
    
    REASON 00C200EA
    TYPE 00000210
    

Local fix

  • subsequent SQL run does not show claims anymore.
    Resource unavailable problem can be circumvented by committing
    the SELECT before running the LOAD.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS users with dynamic       *
    *                 queries.                                     *
    ****************************************************************
    * PROBLEM DESCRIPTION: DB2 incorrectly claims partitions       *
    *                      during prepare of a dynamic query when  *
    *                      probing the index to estimate the       *
    *                      number of qualifying keys.  More        *
    *                      specifically...                         *
    *                       - Claims are taken on unqualified      *
    *                         partitions.                          *
    *                       - Claims are not released until commit *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    During prepare of an SQL statement, DB2 probed the index
    to estimate number of qualifying keys. A claim was acquired on
    the object (table space, index space, partition) and was not
    released until commit.  For a partitioned index, DB2 incorrectly
    acquired claims on all partitions during this index probing.
    For queries with page range screening (PAGE_RANGE='Y' in the
    PLAN_TABLE), claims should not be required on unqualified
    partitions.
    
    Symptoms of this problem include...
    
      * Claim/drain contention with DB2 utilities resulting in
        deadlocks and/or timeouts (RC00C200EA, RC00C90088,
        RC00C9008E, RC00C90086).
    
      * Performance degradation of prepare due to unnecessary
        index probing of unqualified partitions.
    
    Additional Keywords: SQLACCESSPATH SQLPERFORMANCE
    
    The following command may be used to determine claims held:
             -DIS DB(dbname) SP(*) CLAIMERS LIMIT(*)
    

Problem conclusion

  • DB2 has been updated so that only qualifying index partitions,
    for queries with page range screening (PAGE_RANGE='Y' in
    the PLAN_TABLE) are claimed and processed.  Additionally,
    claim(s) acquired while probing the index are declaimed
    immediately after the probe.
    
    As with any change to DB2 query optimization that can drive
    a different access path, there is some risk of query performance
    regression.
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM74776

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    YesPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-10-10

  • Closed date

    2013-09-18

  • Last modified date

    2013-11-15

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

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

    UK97675

Modules/Macros

  • DSNKCEST DSNXOGBM DSNXOIXP DSNXOPRP DSNXOPRS DSNXOTL
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK97675

       UP13/10/04 P F310 ½

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

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM74776

Modified date:

2013-11-15

Translate my page

Machine Translation

Content navigation