Hi All,

I try to do some simulation of the deadlock and i manage to get -911 errcode 2. However, is not shown in the db2diag.log. In fact, even in instance.nfy only shows the successful escalation of locks but no Error was found.
Is it because deadlock is not consider an Error in the DB2?
So, i am wondering how does a DBA knows when a deadlock occures in DB2?

2ndly, do we have some sort of deadlock capturing like Oracle? It seems the db2detaildeadlock is very user unfriendly where the DBA has to flush the event monitor and use db2evmon to filter it, after that have to reset the event monitor to store the fresh deadlocks..

Thanks.

Regards,
Eddy Ng

what version of db2 are you on?

event monitor is the best choice to capture deadlock details ... yes, you have to flush the event monitor and get the info (not sure how oracle does) ... no,it is not necessary to reset the event monitor for fresh deadlocks ... you can use the same,though new ones will be appended to the current events ...

As an alternate to the default db2detaileddeadlock you can also setup your own 'WRITE TO TABLE' event monitor for easy access to the deadlock data, ie, you do not have to do the extra step of formatting the output


hth

sathyaram

hi Sathyaram,

I am using db2 v9.1 FP 5 running on Redhat 4 Enterprise. I tried to set the diaglevel 4, but still no error on the db2diag.log. But anyway, setting diaglevel 4 is not feasible as this will generate tons of messages into the db2diag.log.

well, my objective is trying to send out an email when db2 hit a deadlocks or locktimeout. I need to reset the monitor because i don't want the old deadlocks in the event monitor. Any idea on how to achieve this? I am currently running it with db2pdcfg to signal the error and having some issue if i come into more than 2 deadlocks at the same time.

Thanks.

if you want instant alert, i do not know of an option other than db2pdcfg .. what is the issue you have with db2pdcfg ?

as i mentioned earlier, you can use write to table event monitor and then prune data on a periodic basis and/or extract data of the events after a specific point in time from the tables ..
(or)
if you have the time, budget, skills and interest, you can do your own parsing of the event monitor data stream and do whatever you want with it -- send alert, do not report on old ones etc, etc ... there is a sample evm in the sqllib/samples/c directory

hth

sathyaram

Hi Sathyaram,

I did try to drop the default deadlock and create a new one with write to table. However, i am having the issues here...
I simulate a deadlock and got err911 code 2 which is a deadlock.
When i try to query the table, it is empty. But if re-run again the simulation, i would get some records
2ndly, the records that i get is short of 1. In other words, for each deadlock occurs, i should be getting 2 statement. However, i am only getting 1 and the 1 other is still inside the event monitor. So each time if i have a deadlock, i will be missing 1 statement in the table..

May i know is this a bug or it was designed? Why would DB2 designed that 1 statement to be push while the other statement is still stored in the event monitor? I am suspecting that the db2 are having some delay to push the 2nd statement to the output file/table.
I try the manual way which is without writing to the table, is still the same result which is missing the 2nd statement.

did you do FLUSH EVENT MONITOR before looking at the table ?