PI28671: DEADLOCK ON SYSIBM.SYSTABLESPACESTATS IN DSNDB06.SYSRTSTSBY MASS DELETE ON MULTIPLE TABLES ON SAME TABLESPACE.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Deadlock on SYSIBM.SYSTABLESPACESTATS in DSNDB06.SYSRTSTS by
  mass delete on multiple tables on same tablespace.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 10 for z/OS and DB2 11 for z/OS          *
  *                 customers who may run mass delete table      *
  *                 jobs on multi-table table spaces.            *
  ****************************************************************
  * PROBLEM DESCRIPTION: MSGDSNT501I RC00C90088 deadlock on the  *
  *                      Real-time Statistics (RTS) catalog      *
  *                      table (SYSIBM.SYSTABLESPACESTATS) may   *
  *                      occur when running multiple SQL mass    *
  *                      delete jobs on multi-table table        *
  *                      spaces concurrently.                    *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available          *
  ****************************************************************
  Deadlock may occur on SYSIBM.SYSTABLESPACESTATS table when
  running multiple mass delete jobs on multi-table table spaces
  concurrently.  Below is a possible scenario for a deadlock.
  
  Job #1 mass deletes table 1 in table space A without a commit.
  Job #2 mass deletes table 3 in table space B without a commit.
  Job #2 mass deletes table 2 in table space A.
  Job #1 mass deletes table 4 in table space B.
  
  SYSIBM.SYSTABLESPACESTATS table maintains statistics at the
  table space level and not at the table level.
  
  When job #1 mass deletes table 1 in table space A, a row level
  lock is obtained for table space A.
  Similarly, job #2 obtains a row level lock for table space B
  when it mass deletes table 3 in table space B.
  
  Because job #1 does not commit, job #2 that mass deletes
  table 2 in table space A needs to wait for the lock to be
  released by job #1.
  
  In the mean time, job #1 that mass deletes table 4 in
  table space B waits for job #2 to release the lock on table
  space B.
  
  A deadlock is detected.
  
  MSGDSNT375I, MSGDSNT376I, and/or DSNT501I may be issued.
  
  Additional keywords:  DSNT375I DSNT376I SYSTABLESPACESTATS
  

Problem conclusion

• RTS mass delete processing has been modified to resolve the
  reported deadlock problem.
  
  Following a mass delete, some of the RTS statistics, such as
  TOTALROWS, DATASIZE, and NPAGES, may become null.  This is
  mainly because, for a multi-table table space, the number of
  records removed by a mass delete on a table is unknown.
  With this APAR fix applied, the period when the RTS data may
  stay nulls becomes longer as in-memory statistics are not
  externalized during a mass delete.
  In a data sharing environment, RTS statistics may not be
  accurate.  This problem will be addressed by a future APAR.
  
  While it is not necessary to apply the APAR fix on all data
  sharing members at once, the fix does need to be on members
  that may run mass delete jobs on multi-table table spaces
  concurrently.
  

Temporary fix

Comments

• ×**** PE15/07/02 FIX IN ERROR. SEE APAR PI37019  FOR DESCRIPTION
  

APAR Information

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

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

  UI23552 UI23553

Modules/Macros

•    DSNIRTSU DSNIRTSX
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI23552

     UP14/12/17 P F412

• RB10 PSY UI23553

     UP14/12/17 P F412

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.