DB2 FOR LUW / DB2FORLUW / 970 - DB2

When you rollback a transaction including DDLs on nickname, 
Federation Server will try to flush catalog cache about that 
nickname. This flushing wrongly requests lock of FEDServerNm in 
X mode. This behavior could cause deadlock and the deadlock can 
not be automatically resovled by DB2 because the transaction is 
doing rollback. The lock request can also happen when the DDL on 
nickname is automatically rollbacked because of error. 
 
When nnstat is running with method 1, Federation Server will 
create or drop nickname with name ending with "_DB2II". If error 
happens when nnstat create or drop nickname, it will request 
that lock too and possibly cause deadlock. 
 
When deadlock happens, you can see below error message reported 
in db2diag.log: 
 
2011-12-26-02.44.33.229005-300 I2314012968E474     LEVEL: Error 
PID     : 11676                TID  : 47258607282496PROC : 
db2sysc 1 
INSTANCE: nyqxt160             NODE : 001          DB   : ATHENA 
EDUID   : 115                  EDUNAME: db2dlock (ATHENA) 1 
FUNCTION: DB2 UDB, lock manager, sqlpKillIfWaiting, probe:670 
DATA #1 : <preformatted> 
Cannot kill deadlock victim (with DLpriority  50 tidhdl 123 
tentryState 4 holder_hdl 34) because it is doing rollback or 
commit. 
 
From the db2pd output, lock contention on FEDServerNm can be 
seen as below: 
 
94081    [001-28545] 34         5B160000000000000000000073 
FEDServerNm ..S  ..X  C   312021     db2ts    MSDB2 
10.172.100.232.15750.1112260741 
 
The stack of hanging agent can be like below one: 
 
semtimedop + 0x000a 
sqloWaitEDUWaitPost + 0x0199 
_Z8sqlplfndP9sqeBsuEduP14SQLP_LOCK_INFOP8SQLP_LCBP9SQLP_LHSHP15S 
QLP_LTRN_CHAINP8SQLP_LRBSA_P8SQLP_AWBbb + 0x112c 
_Z7sqlplrqP9sqeBsuEduP14SQLP_LOCK_INFO + 0x0cd9 
_Z16sqlqgFlushServerPhPvS_ + 0x00be 
_ZN14UnfencedServer20flush_cached_objectsEv + 0x0080 
_ZN15UnfencedWrapper20flush_cached_serversEv + 0x007b 
_ZN15UnfencedWrapper20flush_cached_objectsEv + 0x0091 
_Z16sqlqgFedRollbackP8sqeAgentP12SQLQG_F2PC_Tm + 0x0997 
_Z8sqlpxrbkP8sqeAgentP15SQLXA_CALL_INFOPiP9SQLP_GXIDPP11sqlo_xla 
tch + 0x07d7 
_Z8sqlrkrbkP8sqlrr_cbP15SQLXA_CALL_INFOi + 0x025f 
_Z12sqlrrbck_dpsP8sqlrr_cbiiiP15SQLXA_CALL_INFOP9SQLP_GXIDb + 
0x0a22 
_Z8sqlrrbckP8sqlrr_cbiiiiP15SQLXA_CALL_INFO + 0x0546 
_Z21sqlrr_rds_common_postP14db2UCinterfaceiil + 0x1797 
_Z14sqlrr_execimmdP14db2UCinterfaceP16db2UCprepareInfo + 0x014c 
_Z19sqljs_ddm_excsqlimmP14db2UCinterfaceP13sqljDDMObject + 
0x00fa 
_Z21sqljsParseRdbAccessedP13sqljsDrdaAsCbP13sqljDDMObjectP14db2U 
Cinterface + 0x03d5 
_Z10sqljsParseP13sqljsDrdaAsCbP14db2UCinterfaceP8sqeAgentb + 
0x035b 
_Z19sqljsDrdaAsSPDriverP14db2UCconHandleP17SQLCC_COMHANDLE_TPPhl 
+ 0x0278 
_Z24sqlerInvokeFencedRoutineP13sqlerFmpParms + 0x0713 
_Z18sqlriInvokeInvokerP10sqlri_ufobb + 0x0753 
_Z9sqlricallP8sqlrr_cb + 0x0122 
_Z15sqlriSectInvokeP8sqlrr_cbP12sqlri_opparm + 0x00eb 
_Z29sqlrr_process_execute_requestP8sqlrr_cbi + 0x1948 
_Z13sqlrr_executeP14db2UCinterfaceP9UCstpInfo + 0x0115 
_Z19sqljs_ddm_excsqlsttP14db2UCinterfaceP13sqljDDMObject + 
0x04e7 
_Z21sqljsParseRdbAccessedP13sqljsDrdaAsCbP13sqljDDMObjectP14db2U 
Cinterface + 0x007d 
_Z10sqljsParseP13sqljsDrdaAsCbP14db2UCinterfaceP8sqeAgentb + 
0x035b

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* DDLS ON NICKNAME CAUSE DEADLOCK                              * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fixpack 6.                        * 
****************************************************************

Below operations can significantly reduce the chance of hitting 
this deadlock: 
 
1) Check and turn off the health indicators for Federation: 
   db.fed_servers_op_status 
   db.fed_nicknames_op_status 
 
To turn off the two health indicators, using below SQL 
statements: 
 
UPDATE ALERT CONFIGURATION FOR DATABASE ON FEDDB USING 
db.fed_servers_op_status SET THRESHOLDSCHECKED NO 
UPDATE ALERT CONFIGURATION FOR DATABASE ON FEDDB USING 
db.fed_nicknames_op_status SET THRESHOLDSCHECKED NO 
 
2) Turn off the auto_runstats database configuration parameter 
by issuing below SQL statement: 
UPDATE DB CFG FOR FEDDB USING AUTO_RUNSTATS OFF 
 
3) Execute reverse federated DDL instead of  rollback federated 
DDL

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 Fixpack 6.

keiner bekannt / siehe Local-Fix

Vorgänger  : APAR is sysrouted TO one or more of the following: IC84333 IT02338 IT02342 
Nachfolger : 

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

25.01.2012
07.07.2014
07.07.2014

9.7.FP6