db2diag.log captures deadlock @ DIAGLEVEL 4 as the message is of type "INFO"
here is the test case :
On term1:
db2 "create db sample"
db2 "connect to sample"
db2 "create table testtab1 (col1 int)"
db2 "create table testtab2 (col1 int)"
db2 +c "lock table testtab1 in exclusive mode"
On term2:
db2 "connect to sample"
db2 +c "lock table testtab2 in exclusive mode"
On term1:
db2 +c "lock table testtab2 in share mode" # This will hang term1
On term2:
db2 +c "lock table testtab1 in share mode" # This will hang term 2
[red]
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001
[/red]
2010-04-01-14.34.12.912896-600 I2921A773 LEVEL: Info
PID : 4120592 TID : 1 PROC : db2agent (SAMPLE) 0
INSTANCE: db2inst2 NODE : 000 DB : SAMPLE
APPHDL : 0-11 APPID: *LOCAL.db2inst2.100402003258
AUTHID : DB2INST2
FUNCTION: DB2 UDB, lock manager, sqlplnfd, probe:80
DATA #1 : String, 147 bytes
Request for lock "TAB: (3, 21)" in mode "..S" failed due to deadlock
Application caused the lock wait is "*LOCAL.db2inst2.100402003252"
Statement:
DATA #2 : Hexdump, 33 bytes
0x0000000112FB1A94 : 6C6F 636B 2074 6162 6C65 2074 6573 7474 lock table testt
0x0000000112FB1AA4 : 6162 3220 696E 2073 6861 7265 206D 6F64 ab2 in share mod
0x0000000112FB1AB4 : 65 e
2010-04-01-14.34.12.913295-600 I3695A496 LEVEL: Info
PID : 4120592 TID : 1 PROC : db2agent (SAMPLE) 0
INSTANCE: db2inst2 NODE : 000 DB : SAMPLE
APPHDL : 0-11 APPID: *LOCAL.db2inst2.100402003258
AUTHID : DB2INST2
FUNCTION: DB2 UDB, access plan manager, sqlra_compile_var, probe:220
RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED "Dead lock detected"
DIA8002C A deadlock has occurred, rolling back transaction.
2010-04-01-14.34.12.945181-600 I4192A769 LEVEL: Info
PID : 4120592 TID : 1 PROC : db2agent (SAMPLE) 0
INSTANCE: db2inst2 NODE : 000 DB : SAMPLE
APPHDL : 0-11 APPID: *LOCAL.db2inst2.100402003258
AUTHID : DB2INST2
FUNCTION: DB2 UDB, trace services, sqlt_logerr_data, probe:0
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -911 sqlerrml: 1
sqlerrmc: 2
sqlerrp : SQLRL426
sqlerrd : (1) 0x80100002 (2) 0x00000002 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
2010-04-01-14.34.12.945418-600 I4962A629 LEVEL: Info
PID : 4120592 TID : 1 PROC : db2agent (SAMPLE) 0
INSTANCE: db2inst2 NODE : 000 DB : SAMPLE
APPHDL : 0-11 APPID: *LOCAL.db2inst2.100402003258
AUTHID : DB2INST2
FUNCTION: DB2 UDB, access plan manager, sqlra_compile_var, probe:225
MESSAGE : stmt
DATA #1 : Hexdump, 33 bytes
0x0000000112FB1A94 : 6C6F 636B 2074 6162 6C65 2074 6573 7474 lock table testt
0x0000000112FB1AA4 : 6162 3220 696E 2073 6861 7265 206D 6F64 ab2 in share mod
0x0000000112FB1AB4 : 65 e
Hope this answers your question.
Cheers,
Shashank K
IBM Australia