Can anybody tell me how can get a trace or even logging the statements that produces deadlocks in a DB2 UDB 7.2 db server ?

Thanks in advance.

Pablo

1] For obtaining the trace, you can modify db2cli.ini file located at "DB2PATH" (db2 installation path). Set the "trace" flag under [COMMON] section to "1". This will enable the logging. Please ensure that you have enough disk space though... Resetting the value to 0 will stop logging.

[COMMON]
trace=0
TraceFileName=c:\trace.log
traceflush=1

2] For your second part of the question; One can enable snapshot monitors so that we can find out which statements are producing deadlocks.
--** Enable monitor switches
DB2 UPDATE MONITOR SWITCHES USING bufferpool ON lock ON sort ON statement ON table ON uow ON
DB2 GET MONITOR SWITCHES

--**To get snapshot of lock information
DB2 GET SNAPSHOT FOR locks ON {db_name} > c:\temp\locks_{db_name}txt

Besides, make sure you have proper value set for parameters like "LOCKTIMEOUT" and related ones. Refer the db2 performance tuning guide for more information.

Hope this helps.

Or you can use Event Monitor ...

(I will post the steps from workplace ... I do not have a copy of the doc with me at home now)

Create :
create event monitor mon1
for deadlocks
write to file '/tmp/evmon'
nonblocked append
;

Start :
set event monitor mon1 state 1;

Simulate the deadlock error ,,,

Stop:
set event monitor mon1 state 0;

Query State:
select evmonname,event_mon_state(evmonname) from syscat.eventmonitors"
;

Report :

db2evmon -db <dbname> -evm mon1


HTH

Sathyaram

well, i started the monitor and traying to find out wich statement is generating the deadlocks.

sathyaram_s thanks a lot for the piece of information

thanks dr_suresh20 too

Pablo1