db2diag.log doesn't capture deadlock

[eddy82]

Hi DB2 Gurus,

I just want to clarify, does the db2diag.log capture a deadlock error? SQL911 with code 2 and 68.

I am using db2 V9.1 FP 5 running in RHEL4. I have done a simulation to the deadlock and locktimeout, however, the error didn't report to the db2diag.log. I am using DIAGLEVEL 3.
Is this expected? Or it should be reported in my db2diag.log?
I have enable the stmt and lock, stmt and timestamp monitor switches.
Is there any ways that to make the db2diag.log to capture the deadlock alert?

Thanks.

Regards,
Eddy Ng

[fuangwith]

I don't how to force db2 write deadlock message to db2diag.log

Normally, when we would like to monitor deadlock we will use deadlock eventmonitor which write information to Table or File depend on your setting.
Moreover, you can force the database to send email to you when deadlock occur.

[Shashank1984]

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.db2inst 2.100402003252"
Statement:
DATA #2 : Hexdump, 33 bytes
0x0000000112FB1 A94 : 6C6F 636B 2074 6162 6C65 2074 6573 7474 lock table testt
0x0000000112FB1 AA4 : 6162 3220 696E 2073 6861 7265 206D 6F64 ab2 in share mod
0x0000000112FB1 AB4 : 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_v ar, 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_dat a, probe:0
DATA #1 : SQLCA, PD_DB2_TYPE_SQL CA, 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_v ar, probe:225
MESSAGE : stmt
DATA #1 : Hexdump, 33 bytes
0x0000000112FB1 A94 : 6C6F 636B 2074 6162 6C65 2074 6573 7474 lock table testt
0x0000000112FB1 AA4 : 6162 3220 696E 2073 6861 7265 206D 6F64 ab2 in share mod
0x0000000112FB1 AB4 : 65 e


Hope this answers your question.

Cheers,
Shashank K
IBM Australia

[eddy82]

Hi Shashank,

Thanks for the reply. However, to set the diaglevel 4 is not feasible as this will actually dump too many info on the db2diag.log. I have use the db2pdcfg to actually capture the deadlock.

btw, for your simulation, how often you get locktimeout and deadlock? during my simulation, the results that i got was:
a) 1 error code 2, 1 error code 68
b) 2 error code 68
does you guys faced the same issue?

[charchilp]

not worked out shashank
<link removed: possible spam>