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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM84810

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-03-14

  • Closed date

    2013-04-29

  • Last modified date

    2013-06-04

  • 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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM84810

Modified date:

2013-06-04

Translate my page

Machine Translation

Content navigation