DB2 FOR LUW / DB2FORLUW / 970 - DB2

When concurrently performing DROP INDEX and ALTER TABLE...DETACH 
PARTITION statements affecting the same table, the two 
statements may lead to a deadlock among the system catalog 
tables. The target index for DROP INDEX is defined for a table 
that is the same as the target table of the ALTER TABLE 
statement. 
 
To identify the problem, you need to use DB2 event monitor to 
collect detailed information for deadlocks. When the deadlock 
error occurs, in the event monitor output you will see the 
following entries: 
============================================= 
 
  Deadlock ID:   1 
  Participant no.: 2 
  Participant no. holding the lock: 1 
  Appl Id: *LOCAL.svtdbm9.110226130946 
  Appl Seq number: 00041 
  Tpmon Client Applname: CLP drop.clp 
  Appl Id of connection holding the lock: 
*LOCAL.svtdbm9.110226130942 
  Seq. no. of connection holding the lock: 00001 
  Lock wait start time: 02/26/2011 08:09:30.481822 
  Lock Name       : 0x0000000500000000003F000052 
  Lock Attributes : 0x00000000 
  Release Flags   : 0x40000000 
  Lock Count      : 1 
  Hold Count      : 0 
  Current Mode    : U   - Update 
  Deadlock detection time: 02/26/2011 08:09:40.166573 
  Table of lock waited on      : SYSTABLES 
  Schema of lock waited on     : SYSIBM 
  Data partition id for table  : 0 
  Tablespace of lock waited on : SYSCATSPACE 
  Type of lock: Row 
  Mode of lock: NS  - Share (CS/RS) 
  Mode application requested on lock: X   - Exclusive 
 
  Isolation level         : Cursor Stability 
  Stmt first use time     : 02/26/2011 08:09:30.476267 
  Stmt last use time      : 02/26/2011 08:09:30.476267 
  Statement text          : DROP INDEX "DLTEST_T40_IX1" 
 
 
  Deadlock ID:   1 
  Participant no.: 1 
  Participant no. holding the lock: 2 
  Appl Id: *LOCAL.svtdbm9.110226130942 
  Appl Seq number: 00007 
  Appl Id of connection holding the lock: 
*LOCAL.svtdbm9.110226130946 
  Seq. no. of connection holding the lock: 00001 
  Lock wait start time: 02/26/2011 08:09:30.513920 
  Lock Name       : 0x0000000500000000003F000052 
  Lock Attributes : 0x00000000 
  Release Flags   : 0x40000000 
  Lock Count      : 2 
  Hold Count      : 0 
  Current Mode    : NS  - Share (CS/RS) 
  Deadlock detection time: 02/26/2011 08:09:40.167378 
  Table of lock waited on      : SYSTABLES 
  Schema of lock waited on     : SYSIBM 
  Data partition id for table  : 0 
  Tablespace of lock waited on : SYSCATSPACE 
  Type of lock: Row 
  Mode of lock: U   - Update 
  Mode application requested on lock: X   - Exclusive 
 
  Isolation level         : Cursor Stability 
  Stmt first use time     : 02/26/2011 08:09:30.400240 
  Stmt last use time      : 02/26/2011 08:09:30.400240 
  Statement text          : alter table dltest_t40 detach 
partition PART2 into DLTEST_T40_D

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users running DB2 Version 9.7 on Linux, Unix and Windows * 
* performing DROP INDEX and ALTER TABLE..DETACH PARTITION      * 
* concurrently.                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* When concurrently performing DROP INDEX and ALTER            * 
* TABLE...DETACH                                               * 
* PARTITION statements affecting the same table, the two       * 
* statements may lead to a deadlock among the system catalog   * 
* tables. The target index for DROP INDEX is defined for a     * 
* table                                                        * 
* that is the same as the target table of the ALTER TABLE      * 
* statement.                                                   * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Follow the recommendation specified in "Local Fix" or update * 
* to DB2 Version 9.7 fix pack 6.                               * 
****************************************************************

Perform the two statements in sequence.

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in DB2 Version 9.7 fix pack 6.

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

27.03.2012
05.06.2012
05.06.2012

9.7.FP6