DB2 FOR LUW / DB2FORLUW / 970 - DB2

When performing a multiple detach partition on a partitioned 
table, in the same unit of work, the Asynchronous Partition 
Detach (APD) processes with each other. The APD function locks 
the row in SYSIBM.SYSTABLES for the partitioned table with an NS 
lock. Later, the process will need to lock this row in X-clusive 
mode to update the packed descriptor. This is will cause a 
deadlock between the two APD tasks. 
 
Here is the stack for when DB2 acquires the X-lock on the row in 
SYSIBM.SYSTABLES: 
 
sqlrlLockSystablesRow 
sqlrlSysPartitionsAdjustSeqno 
sqlrlSysPartitionsDelete 
sqlrlAlterDropCatalogChange 
sqlrlAlterDetachCatalogChange 
sqlrlAlterPartCatalog 
sqlrlPhysicalDetach 
apdTaskProcessor

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version V9.7 Fix Pack 9                       * 
****************************************************************

If you hit this problem and are concerned that you cannot 
reclaim the space by dropping or archiving the detached 
partition there are two workarounds available each with their 
own pros and cons. 
 
Workaround 1: 
Pros:  No need to recycle the instance if LockTimeout is already 
set to -1. 
Cons:  Need to export the data from the table and recreate it. 
Workaround: 
- Set LockTimeout to -1 using the UPDATE DATABASE CONFIGURATION 
command. 
- Recycle the instance using db2stop and db2start. 
- Drop the partitioned table. 
 
Workaround 2: 
Pros:  No need to recreate the table. 
Cons: 
- Need to recycle the instance 
- Time required for the system to resolve itself using this 
method may vary based on timing and the number system jobs to 
complete. 
Workaround: 
- Set LockTimeout to -1 using the UPDATE DATABASE CONFIGURATION 
command. 
- Set DLChkTime to 1000 (i.e. 1000 ms or 1 s) using UPDATE 
DATABASE CONFIGURATION and set DB2_ABP_SUSPEND_DELAY to 3 (i.e. 
3 seconds) using db2set. 
- Recycle the instance using db2stop and db2start. 
- Connect to the database and wait for the system to resolve 
itself.  Monitor SysCat.DataPartitions until there are no 
partitions left with STATUS='L'.

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 v9.7 Fix Pack 9

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC92146 IC95308 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

12.03.2013
19.06.2014
19.06.2014

9.7.FP9