PI22460: MASS DELETE IN LOCK EXCLUSIVE MODE ON PBG COULD HIT DSNIMS1W 5002 WHEN SELF-REFERENCE RI EXISTS.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Customer hit DSNIMS1W 5002 during mass delete operation in
  exclusive mode. same mass delete worked fine without locking
  table in exclusive mode.
  Sample SQL statement
  --
  LOCK TABLE TESTTB IN EXCLUSIVE MODE;
  DELETE FROM TESTTB;
  --
  
  DDL shows that there're sele-reference RIs(PARENT KEY & FOREIGN
  KEY in same table) existing. Delete rule is CASCADE.
  

Local fix

• Remove lock table statement before mass delete.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 10 and 11 for z/OS users of MASS DELETE  *
  *                 against a table with self reference and      *
  *                 delete rule is CASCADE.                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: ABEND04E RC00C90101 at DSNIMS1W         *
  *                      ERQUAL5002 during MASS DELETE execution *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  During MASS DELETE execution against a table with self
  reference, if there is page set level exclusive lock held and
  the pageset lock is not selective one, DB2 will decide to use
  row by row delete, but the foreign key for the self reference
  will not be disconnected along with data record.  If the table
  delete rule is cascade, child records via the foreign key index
  access failed to find record and the ABEND at DSNIMS1W happened.
  
  Here are 2 possible scenario making non selective exclusive
  pageset lock
  1. LOCK TABLE IN EXCLUSIVE MODE
  2. LOCKSIZE IS TABLESPACE OR TABLE
  

Problem conclusion

• DB2 code has been modified to force disconnection from the
  self reference foreign key during the slow mass delete against
  table with self reference and delete rule is CASCADE.
  

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:

  UI22611 UI22612

Modules/Macros

• DSNIDIFS DSNISDLE
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI22611

     UP14/11/18 P F411

• RB10 PSY UI22612

     UP14/11/18 P F411

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.