Latest versionsfixlist
11.1.0.6 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC74950 Status: Closed

DB2 MIGHT ENCOUNTER 'DEADLOCK' ERROR WHEN PERFORMING DROP TABLE
AND RENAME TABLE SIMULTANEOUSLY.

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
When concurrently perform RENAME TABLE and DROP TABLE on two 
different tables but with similar namings, the two statements 
may lead to a deadlock among the catalog tables, 
SYSIBM.SYSVIEWDEP and SYSIBM.SYSDEPENDENCIES. 
The problem is more likely to happen if the table being dropped 
has dependent views and other objects (e.g. index or trigger) 
defined on it. 
 
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: 
 
 
  Table of lock waited on      : SYSDEPENDENCIES 
  Schema of lock waited on    : SYSIBM 
  Data partition id for table  : 0 
  Tablespace of lock waited on : SYSCATSPACE 
  Type of lock: End of table 
  Mode of lock: U  - Update 
  Mode application requested on lock: U  - Update 
    Text    : RENAME TABLE NEWTON.T1 
 
  Table of lock waited on      : SYSVIEWDEP 
  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: S  - Share 
  Mode application requested on lock: X  - Exclusive 
    Text    : DROP TABLE NEWTON.T2
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All users running Linux, Unix and Windows servers on DB2     * 
* v9.5 Fix Pack 7 or earlier are affected.                     * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* When concurrently perform RENAME TABLE and DROP TABLE on two * 
* different tables but with similar namings, the two           * 
* statements may lead to a deadlock among the catalog tables,  * 
* SYSIBM.SYSVIEWDEP and SYSIBM.SYSDEPENDENCIES.                * 
* The problem is more likely to happen if the table being      * 
* dropped has dependent views and other objects (e.g. index or * 
* trigger) defined on it.                                      * 
*                                                              * 
* 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:                          * 
*                                                              * 
*                                                              * 
* Table of lock waited on      : SYSDEPENDENCIES               * 
* Schema of lock waited on    : SYSIBM                         * 
* Data partition id for table  : 0                             * 
* Tablespace of lock waited on : SYSCATSPACE                   * 
* Type of lock: End of table                                   * 
* Mode of lock: U  - Update                                    * 
* Mode application requested on lock: U  - Update              * 
* Text    : RENAME TABLE NEWTON.T1                             * 
*                                                              * 
* Table of lock waited on      : SYSVIEWDEP                    * 
* 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: S  - Share                                     * 
* Mode application requested on lock: X  - Exclusive           * 
* Text    : DROP TABLE NEWTON.T2                               * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 LUW Version 9.5 Fix Pack 8 or higher levels.  * 
****************************************************************
Local Fix:
Perform DROP TABLE statement and RENAME TABLE statement 
sequentially.
available fix packs:
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Solution
First fixed in DB2 LUW Version 9.5 Fix Pack 8.
Workaround
Perform DROP TABLE statement and RENAME TABLE statement 
sequentially.
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC75112 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
10.03.2011
07.07.2011
07.07.2011
Problem solved at the following versions (IBM BugInfos)
9.5.,
9.5.FP8
Problem solved according to the fixlist(s) of the following version(s)
9.5.0.8 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.