PI96115: SQLCODE -904 WHEN OBJECTS IN RW STATUS AFTER MASS DELETE RESET THOSE NOT LOGGED TS/INDEX'S RESTRICT STATUS.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE -904 when objects in RW status after MASS DELETE reset
  those NOT LOGGED TS/INDEX's restrict status.
  
  In reported scenario below:
  Customer has a NOT LOGGED object and he took some rollback
  action to leave the object in RECP,RBDP,LPL status.
  --
  TSSEGVC  TS         RW,RECP,LPL,ICOPY
  IXSEGARA IX         RW,RBDP,LPL
  IXSEGBRA IX         RW,RBDP,LPL
  IXSEGCRA IX         RW,RBDP,LPL
  IXSEGZRA IX         RW,RBDP,LPL
  --
  Then he used MASS DELETE to get rid of all the outstanding
  status.
  He then inserted some records and hit -904 when he tried to
  delete some records via index.
  ---
  DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED
  BY AN UNAVAILABLE
  00000000, AND RESOURCE NAME
  --
  When -904 message is returned,  all objects are in normal
  status.
  --
  NAME     TYPE PART  STATUS
  -------- ---- ----- -------------
  TSSEGVC  TS         RW,ICOPY
  IXSEGARA IX         RW
  IXSEGBRA IX         RW
  IXSEGCRA IX         RW
  IXSEGZRA IX         RW
  --
  
  Additional Symptom:
   SELECT failed due to -904 00C9000A when accessing not logged
  tablespace through an index in LPL (though DBET is clean).
    SQLCODE & SQLERRD:
       DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION
       CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00000000,
       TYPE OF RESOURCE 00000000, AND RESOURCENAME
       DSNT418I SQLSTATE   = 57011 SQLSTATE RETURN CODE
       DSNT415I SQLERRP    = DSNXRRC SQL PROCEDURE DETECTING ERROR
       DSNT416I SQLERRD    = 103  13172746  0  -1  0  0 SQL
                DIAGNOSTIC INFORMATION
       DSNT416I SQLERRD    = X'00000067'  X 00C9000A'
                X'00000000' X'FFFFFFFF'  X'00000000'  X'00000000'
                SQL DIAGNOSTIC INFORMATION
  
  Additional Keywords:
      SQLCODE -904, NOT LOGGED, INDEX,
      MASS DELETE, ZSA2, 00C9000A, RC00C9000A
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Db2 11 for z/OS and Db2 12 for z/OS users    *
  *                 using NOT LOGGED table spaces.               *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQL received -904 after mass delete was *
  *                      used to remove the LPL state on a NOT   *
  *                      LOGGED table space and its indexes.     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  A NOT LOGGED table space and its indexes were put into LPL state
  by a transaction rollback. Mass delete ran on the table and
  removed the LPL state on the table space and its indexes. After
  the mass delete, the SQL using index access received -904 when
  the index and table were both in the RW state.
  
  After mass delete removed the LPL state from the index, an
  internal flag on buffer manager control block PB still showed
  the index may be in LPL state, which caused index manager
  to return -904 when accessing this index.
  
  Additional Keywords: SQLCODE904 NOT LOGGED ZSA2
  

Problem conclusion

• Index manager code has been changed to allow index access after
  mass delete removes the LPL state from an index created on a NOT
  LOGGED table space.
  

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:

  UI56429 UI56433

Modules/Macros

•    DSNKFLST DSNKFPRV DSNKFTCH DSNKNXT2
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI56433

     UP18/06/26 P F806

• RC10 PSY UI56429

     UP18/06/26 P F806

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.