PI04813: LOOPING OCCURRED IN DSNXISUB WHEN ISSUING SQL DROP TABLESPACE OR SQL DROP OTHER DB2 OBJECTS. THREADS MAY APPEAR HUNG.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Looping occurred when SQL DROP a DB2 object, such as DROP
  a tablespace.  The tablespace is defined with LOCKSIZE ROW.
  The systrace shows CLKC entries as follows:
  DSNXISUB +2E36 06/26/13UK95420
  DSNICUBC +A60 06/11/13UK95016
  DSNICUBC +3320 06/11/13UK95016
  DSNB1REL +5E2 06/26/13UK95421
  DSNXISUB +44D0 06/26/13UK95420
  DSNITCUS +104 06/11/13UK95016
  DSNIMOFR +E02C 06/11/13UK95016
  DSNXISB1 +7F0 06/26/13UK95420
  .
  ZPARM EVALUNC in on when the looping occurred.
  .
  The bypass is to turn off zparm EVALUNC.
  search keywords: SQLDROP
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9,10,11 for z/OS users of            *
  *                 subsystem parameter EVALUNC                  *
  *                 (EVALUATE UNCOMMITTED).                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: When subsystem parameter EVALUNC        *
  *                      (EVALUATE UNCOMMITTED) is set, SQL DROP *
  *                      TABLESPACE reads from SYSIBM.SYSVIEWDEP *
  *                      catalog table to search the view for    *
  *                      the table being dropped. It skipped the *
  *                      pseudo deleted entry from the index     *
  *                      created on SYSIBM.SYSVIEWDEP table and  *
  *                      caused orphan view after the pseudo     *
  *                      deleted entry was reinserted due to     *
  *                      ROLLBACK.                               *
  *                      There could be potentially other        *
  *                      unexpected error/symptoms as a result   *
  *                      of this issue. Some of the other known  *
  *                      symptoms are:                           *
  *                      <1>Looping occurred when SQL DROP a DB2 *
  *                         object, such as DROP a tablespace.   *
  *                      <2>User might receive an unexpected     *
  *                         SQLCODE -204 error on subsequent     *
  *                         DROP VIEW statement.                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Subsystem parameter EVALUNC
  controls whether predicate evaluation is to
  be allowed on uncommitted data.
  
  When subsystem parameter EVALUNC was set
  to YES, Data that did not satisfy the predicate during
  evaluation but then, because of undo processing (ROLLBACK
  or statement failure), reverted to a state that did
  satisfy the predicate was missing from the answer set.
  
  Since EVALUNC is a system option, it also affects how the
  catalog and directory objects are accessed. When
  subsystem parameter EVALUNC is set to YES,
  operation reading from catalog or directory objects
  can miss uncommitted rows.
  

Problem conclusion

• After the change in this APAR, subsystem parameter
  EVALUNC does not apply to the following case:
  <1>To predicates in SQL statements that access the DB2 catalog
     or directory.
  <2>To data definition statements, such as DROP TABLESPACE, for
     which internal processes query catalog or directory tables.
  
  Information about this changed subsystem parameter
  will be included in the Information Management Software for z/OS
  Solutions Information Center
  (http://publib.boulder.ibm.com/infocenter/imzic) at a later
  date.
  

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:

  UI14113 UI14114 UI14115

Modules/Macros

• DSNIACCH DSNIONX2 DSNIOST2 DSNIRNXT DSNIRPRE DSNISFRL DSNISFS
  DSNISRID DSNIXLDI DSNKFLST DSNKFPRV DSNKFTCH DSNKINSL DSNKNXT2
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI14113

     UP14/01/25 P F401 ½

• RB10 PSY UI14114

     UP14/01/25 P F401 ½

• R910 PSY UI14115

     UP14/01/25 P F401 ½

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.