PI65187: RC00C90101 DSNKINSL ERQUAL5007, RC00C20031 DSNUAHR2 OR OTHER INDEX DEADLOCK OR TIMEOUT ERRORS. 16/07/05 PTF PECHANGE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Symptoms include:
  - SQLCODE904 with RC00C20031 or RC00C20255 when running the
    Modify utility?  A deadlock can occur on any object during
    delete index leaf page.  The reason for the deadlock is
    when This can occur regardless of whether PI62167  or
    PI56935 is applied.
                                                                 .
  - RC00C90101 with ERQUAL5007 in DSNKINSL occurs after
    application of  PI62167. Objects involved are
    DSNDB06.SYSTSFAU and one of it's 4 indexes ( DSNACX01,
    DSNACX02, DSNACX03, DSNACX04 ).
                                                                 .
  - ABEND04E in DSNIOST2 ERQUAL5063 with the following title:
    DSN ,ABND=04E-00C90101,U=uuuuuuu ,M=N ,C=111.DMC
    -DSNIST2O,M=DSNTFRCV,LOC=DSNIDM DSNIOST2 5063
                                                                 .
  - ABEND04E RC00E40304 in DSNUTILA DSNURBXE offset 2316
                                                                 .
  - DSNKREST:5007(DSNURBXE->DSNKREST) due to IPSMOD=on
                                                                 .
  - Abend04e rc00c90101 dsnionx2 erqual5063
    rc00c2002a in bmtr prior.
  
  - RC00C90101 DSNKINSL ERQUAL5007, RC00C20031 DSNUAHR2 or
    other index serialization errors.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS and DB2 11 for z/OS users*
  ****************************************************************
  PROBLEM DESCRIPTION: This APAR addresses two problems:         *
  *                    1.In DB2 11 datasharing, deadlocks or     *
  *                      timeouts on index leaf page plocks or   *
  *                      latches during index leaf page delete   *
  *                      processing may result in SQLCODE904     *
  *                      RC00C20031, RC00C20255 or RC00C200B8    *
  *                      in DSNUAHR2 or other modules.           *
  *                    2.After applying APAR PI62167             *
  *                      (PTF UI38289/UI38290),                  *
  *                      or APAR PI56935(PTF UI38291)            *
  *                      - RC00C90101 DSNKINSL ERQUAL5007,       *
  *                      - RC00C90101 DSNIOST2 ERQUAL5063        *
  *                      - RC00E40304 DSNURBXE +x'2316'          *
  *                      - RC00C90101 DSNKREST ERQUAL5007        *
  *                      during index access.                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This APAR addresses the following two problems:
  1.Performance delays, deadlocks or timeouts accessing index
  leaf pages during index leaf page delete processing in
  DB2 11 datasharing.
  When deleting an index key from an index page, DB2 could also
  clean up committed pseudo empty index pages from the same
  index.  In a datasharing system, when deleting an index page
  from the index tree, DB2 requests the leaf page plock of the
  previous leaf page while holding the plock of the page being
  deleted.  This may cause a deadlock situation with another
  thread running on a different DB2 member which holds the
  plock of the previous leaf page and requests the plock for the
  page being deleted.
  When the index leaf page plock request deadlock happens, the
  threads involved could also hold the index tree plock and
  possibly other locks or latches.  This can cause performance
  delays or applications to timeout on any resource affected
  by the deadlock.
  Symptoms include SQLCODE904 RC00C20031, RC00C20255 or RC00C200B8
  This problem can occur on any index.
  In the case of DSNDB01.DSNLLX02, deletes by MODIFY RECOVERY
  can result in: DSNT500I DSNUAHR2 RESOURCE UNAVAILABLE
                          REASON 00C20031
                          TYPE 00000300
                          NAME DSNDB01.DSNLLX02.page-number
                 DSNT500I DSNUAHR2 RESOURCE UNAVAILABLE
                          REASON 00C20255
                          TYPE 00000201
                          NAME DSNDB01.DSNLLX02
  The index pseudo delete daemon 014.IDAEMKxx may also see
  p-lock negotiation failures RC00C20255 due to the aforementioned
  plock deadlock situation.
  2.The second problem addressed by this APAR was introduced
  by APAR PI62167(PTF UI38289/UI38290)
  or APAR PI56935(PTF UI38291):
  -SQL INSERT may fail with RC00C90101 DSNKINSL ERQUAL5007
  when reading an index page with the structure modification
  flag on.  The structure modification flag was turned on by a
  concurrent thread which was deleting the index leaf page.
  -RC00C90101 DSNIOST2 ERQUAL5063 can occur when SQL index access
  with isolation level repeatable read deletes a pseudo empty
  index page from the index and the delete page rolls back after
  a conditional plock request failure and fails to set up the
  proper error code.
  -RC00E40304 DSNURBXE OFFSET2316 +x'2316' can occur when running
  LOAD REPLACE and DB2 deletes an index page from an NPI index.
  The delete fails after a conditional plock request failure
  causing the LOAD utility to abend.
  RC00C90101 DSNKREST ERQUAL5007 may also occur when running the
  LOAD utility.
  

Problem conclusion

• DB2 index leaf page delete processing has been changed as
  follows:
  1.DB2 will request the page latch and plock conditionally
  for the previous page whilst holding the latch and plock of
  the page being deleted. This will prevent the deadlock
  situation from occurring.  If the plcok cannot be acquired then
  the delete of the page will be skipped.
  2.When turning on the structure modification flag on the index
  leaf page, DB2 will also increase the sequence number of the
  page so SQL can detect that the page has been changed.
  

Temporary fix

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

Comments

APAR Information

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

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

Modules/Macros

• DSNB1ULT DSNKDLE  DSNKDPG
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI40720

     UP16/09/28 P F609 «

• RB10 PSY UI40721

     UP16/09/28 P F609 «

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.