PI70852: DEADLOCK HAPPENS WHEN RUNNING SEVERAL CONCURRENT REORG JOBS THAT CREATE THEIR MAPPING TABLES IN THE SAME DATABASE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Customer is running concurrent REORG jobs.
  REORG_MAPPING_DATABASE=DSNMAP3 in the ZPARM.
  Each of the Reorgs are composed by multiple steps , each one
  reorganizing a different partition of the same tablespace
  and without specifying the mapping table name.
  The Reorgs got deadlocked on the DBD lock for the mapping
  table DBD.
  In Syslog:
  DSNT375I  -DSN3 PLAN=DSNUTILX WITH 001
          CORRELATION-ID=QG3MMN2W
          CONNECTION-ID=UTILITY
          IS DEADLOCKED WITH PLAN=DSNUTIL WITH
          CORRELATION-ID=QG3MMJ2W
  .
  DSNT501I  -DSN3 DSNILMCL RESOURCE UNAVAILABLE 002
             CORRELATION-ID=QG3MMN2W
             CONNECTION-ID=UTILITY
             REASON 00C90088
             TYPE 00000100
  From the locking trace, the deadlock also involves
  DSNDB06.SYSTSIXS.
  - Reorg job QG3MMN2W holds SYSTSIXS X'00000282' ROW =X'08' in
   X mode AND is waiting for dbd lock on DSNMAP3 (mapping db)
  - Reorg job QG3MMJ2W holds dbd lock on DSNMAP3 (mapping db)
   in X mode AND is waiting for SYSTSIXS X'00000282' ROW =X'08'
  

Local fix

• Do not specify REORG_MAPPING_DATABASE zParm or the
  MAPPING_DATABASE keyword, as REORG would use implicit
  mapping data bases.
  Eventually split the mapping tables in different databases.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of REORG           *
  *                 TABLESPACE SHRLEVEL CHANGE utility           *
  ****************************************************************
  * PROBLEM DESCRIPTION: DEADLOCK encountered during concurrent  *
  *                      REORG TABLESPACE SHRLEVEL CHANGE jobs   *
  *                      execution with implicit mapping tables  *
  *                      creation in the same mapping data base  *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available          *
  ****************************************************************
  Users ran a high degree of concurrent REORG TABLESPACE SHRLEVEL
  CHANGE jobs where each REORG job was implicitly creating its
  own mapping objects in the same common mapping data base.
  Overtime, these REORG jobs began to deadlock one another, over
  the DBD lock of the mapping data base and a row lock on
  SYSIBM.SYSINDEXES.  This resulted in REORG terminations in the
  UTILTERM phase when implicitly created mapping objects were
  being deleted.
  
  The reported contention was due to the existence of pseudo
  deleted key entries in one of the SYSIBM.SYSINDEXES index, which
  were created when the mapping table/index objects with the same
  names were created and dropped overtime.  The rate of collision
  on these pseudo deleted key entries increases as users reuse
  the same utility-id and jobname on the REORG executions over a
  period of time.
  
  Additional symptoms:
  DSNT375I MSGDSNT375I DSNT501I MSGDSNT501I RC00C90888
  

Problem conclusion

• To alleviate the aforementioned deadlock contention, code has
  been modified for REORG to generate more unique mapping table
  and index names during implicit creations, which will cut down
  on the collision rate on the pseudo deleted key entries in
  SYSIBM.SYSINDEXES.
  
  Alternatively, users can also exploit different mapping data
  base for each REORG job to implicitly create the mapping objects
  in, which would reduce the contention on the DBD lock that
  contributed to the DEADLOCK.
  

Temporary fix

Comments

APAR Information

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

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

  PI71390 UI42610

Modules/Macros

• DSNURMAP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI42610

     UP16/12/01 P F611

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.