PM84810: A TRUNCATE LOCK IS NOT RELEASED AT COMMIT WHEN EXPECTED.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A TRUNCATE lock is NOT released at commit when a package is
  bound with RELEASE(DEALLOCATE).
  The scenario:
  1. INSERT into table a select * from table b ;
  2. Run a truncate program with deallocate bind.
  EXEC SQL
  TRUNCATE table a REUSE STORAGE
  RESTRICT WHEN DELETE TRIGGERS IMMEDIATE ;
  ...
  EXEC SQL COMMIT ;
  ...
  sleep(1000);
  .
  3) Insert data into the (TRUNCATED + COMMITTED) table:
  insert into table a   select * from table b ;
  Gives an error:
  UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT.
  REASON CODE 00C9008E, TYPE OF RESOURCE 00000210,
  AND RESOURCE NAME (table space for table a ) SQLCODE=-913,
  SQLSTATE=57033, DRIVER=3.62.80
  DSNT376i timeout rc00c9008e
  DSNT501i -DSN dsnilmcl resource unavailable
            ..
            reason 00C9008E
            type 00000210
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS users with applications  *
  *                 that use SQL TRUNCATE Table statement and    *
  *                 bound with RELEASE(DEALLOCATE).              *
  ****************************************************************
  * PROBLEM DESCRIPTION: After TRUNCATE Table, DB2 keeps the     *
  *                      gross lock even after Commit if the     *
  *                      application is bound with               *
  *                      RELEASE(DEALLOCATE). This causes        *
  *                      other applications to be timed out      *
  *                      with RC00C9008E when trying to access   *
  *                      this table space.                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 will acquire a gross lock on the table space for Truncate
  table. If the application is bound with RELEASE(DEALLOCATE),
  then DB2 mistakenly keeps the gross lock across Commit and
  will not release it until this thread is deallocated.
  This causes other applications to be timed out when trying to
  access this table space with reason code 00C9008E.
  

Problem conclusion

• A fix is made in DB2 to release the gross lock on the table
  space at Commit. This will allow other applications to access
  this table space after Truncate table.
  

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:

  UK93912

Modules/Macros

• DSNICMT2 DSNILKDY DSNIPSFI DSNISDLE DSNISEGD
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK93912

     UP13/05/15 P F305

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.