DB2 FOR LUW / DB2FORLUW / 970 - DB2

"COPY NO" Loads in recoverable databases can conflict with DROP 
TABLE command in very rare timing scenarios, resulting in 
Deadlock. The table being LOADed and the table being dropped 
would need to be in the same tablespace for this problem to 
occur.  Also, the table names may be different. 
 
Deadlock event monitor explains following behavior: 
 
LOAD with "COPY NO": 
- Holds lock on SYSIBM.SYSDATAPARTITIONS. 
- Waits for SYSIBM.SYSTABLES. 
 
DROP: 
- Holds lock on SYSIBM.SYSTABLES. 
- Waits for SYSIBM.SYSDATAPARTITIONS. 
 
Deadlock can be seen in db2diag.log as follows : 
 
2009-06-10-04.03.55.995750+120 I8813013E598        LEVEL: Error 
PID     : 24366                TID  : 183228164448 PROC : 
db2sysc 0 
INSTANCE: XXXXXXXX             NODE : 000          DB   : 
XXXXXXX 
APPHDL  : 0-2132               APPID: 
*LOCAL.XXXXXXXXX.090610022252 
AUTHID  : XXXXXXXX 
EDUID   : 40                   EDUNAME: db2agent (DBFORCEP) 0 
FUNCTION: DB2 UDB, catalog services, sqlrlCatalogScan::fetch, 
probe:40 
RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock 
detected" 
          DIA8002C A deadlock has occurred, rolling back 
transaction. 
DATA #1 : Hex integer, 4 bytes 
0x0000000B 
 
2009-06-10-04.03.55.996244+120 I8813612E574        LEVEL: Error 
PID     : 24366                TID  : 183228164448 PROC : 
db2sysc 0 
INSTANCE: XXXXXXXX             NODE : 000          DB   : XXXXXX 
APPHDL  : 0-2132               APPID: 
*LOCAL.XXXXXXXXX.090610022252 
AUTHID  : XXXXXXXX 
EDUID   : 40                   EDUNAME: db2agent (DBFORCEP) 0 
FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0 
DATA #1 : String, 139 bytes 
LOADID: 40.2009-06-10-04.03.51.114120.0 (2;93) 
Failed to get TS Contents , -2146435070, 0x2, Detected in 
file:sqluCCatalogInfo.C, Line:3164 
 
This deadlock problem can occur even if the registry variable 
DB2_LOAD_COPY_NO_OVERRIDE is used.

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users prior to DB2 V97 FP4.                                  * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* "COPY NO" LOADS IN RECOVERABLE DATABASES RESULTS IN DEADLOCK * 
* WITH DROP TABLE COMMAND.                                     * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V97 FP4.                                      * 
****************************************************************

(i)  We should not run "COPY NO" Loads in recoverable databases 
     and DROP TABLE in parallel. 
 
(ii) Also, this problem does not occur if 'COPY YES' or 
     'NONRECOVERABLE' is specified explicitly in the LOAD 
     command.

DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 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
 

Problem is first fixed in DB2 V97 FP4.

keiner bekannt / siehe Local-Fix

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

05.05.2010
09.05.2011
09.05.2011

9.7.FP4