PH67088: NEW FUNCTION TO IMPROVE REORG INDEX PACKAGE INVALIDATION PROCESSING.

APAR status

• Closed as new function.

Error description

• NEW FUNCTION to improve REORG INDEX package invalidation
  processing.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 13 for z/OS users of REORG INDEX     *
  *                 SHRLEVEL CHANGE utility to materialize       *
  *                 pending definition changes.                  *
  ****************************************************************
  * PROBLEM DESCRIPTION: Improve the REORG INDEX SHRLEVEL        *
  *                      CHANGE utility serialization            *
  *                      mechanism for materializing pending     *
  *                      definition changes.                     *
  ****************************************************************
  User ran a REORG INDEX SHRLEVEL CHANGE utility to materialize a
  pending definition change.  DSNT501I messages with RESOURCE
  UNAVAILABLE RC00C90088 were issued due to a deadlock.  The
  utility issued DSNU283I - MATERIALIZATION ERROR with SQLCODE
  -904 RC00E70010 and abended with a RC00E400E3.  During pending
  definition materialization, REORG INDEX was holding a drain on
  the object and attempted to acquire locks on plans and packages
  while concurrent DML held the lock on those resources and was
  attempting to get a claim on the same object.
  
  The current break-in mechanism by REORG INDEX SHRLEVEL CHANGE
  to materialize pending definition changes constitutes a single
  break-in attempt during the SWITCH phase. If any locks or
  exclusive control of necessary resources cannot be obtained,
  it would cause the utility to abnormally terminate, without any
  'retry' attempt performed by the utility. Additionally, the
  utility would leave the target objects in an unavailable state,
  UTUT, with SQLCODE -904 until the utility can be properly
  terminated with -TERM UTIL command execution, resulting in an
  extended outage window.
  
  In the scenario described above, REORG INDEX SHRLEVEL CHANGE
  should maximize its chance and number of attempts to break-in
  successfully.  In addition, when exclusive control of the
  target objects cannot be acquired, it should terminate
  gracefully with minimal disruption to ensure continuous data
  availability.
  
  Additional Keywords: MSGDSNU283I DSNT375I RC00C90082
  MSGDSNT501I SQLCODE904
  

Problem conclusion

Temporary fix

Comments

• This fix improves the REORG INDEX serialization mechanism
  currently used for materializing pending definition changes
  to minimize its impact on concurrent applications for both
  success and failure scenarios. The necessary resources are
  now acquired in the LOG phase, and all dependencies are
  quiesced. These include plans, packages, and the dynamic
  statement cache.
  
  REORG INDEX SHRLEVEL CHANGE will now issue DSNU1383I -
  DYNAMIC STATEMENT CACHE WAS INVALIDATED, REASON = STATIC
  PACKAGE INVALIDATED when materializing pending definition
  changes.
  
  If the necessary resources are unavailable during the LOG
  phase message DSNU590I - RESOURCE UNAVAILABLE,
  REASON=X'00E70010', ON database.tspace PROHIBITS PROCESSING
  will be issued for each attempt.  The utility will end with
  RC8.
  
  Additional Keywords: MSGDSNU283I DSNT375I RC00C90082
  MSGDSNU590I RC00E70010
  

APAR Information

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

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

  UO07393

Modules/Macros

• DSNUGPAL DSNURDRN DSNURFIT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels