By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,119 Members | 2,064 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,119 IT Pros & Developers. It's quick & easy.

How to find out the SQL statements causing deadlock?

P: n/a
Hello,

We are runnig DB2 on AIX and have DB2 deadlock problem.
Is there any way or any tool to find out which SQL statements cause the deadlock?

Thanks a lot.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

P: n/a
Not mentioned in this article is the fact that in V8.1 we now create a detailed deadlock event monitor for you so if you are using DB2 UDB V8.1 and are seeing deadlocks you should simply be able to format the event monitor output to see the deadlock events AND the SQL statements involved in the deadlock situation. The deadlock event monitor is called DB2DETAILDEADLOCK.

Also not mentioned in the article is the fact that to ensure that you get the full deadlock event monitor output to your file, turn off the event monitor (set its state to 0) before using db2evmon to dump the records, otherwise you will not get all the records in the file. Then simply turn the state back to zero. You can also try the FLUSH EVENT MONITOR SQL in this case, but I tend to do the former.

In any event, that will give you the SQL you are interested in...

--

Bob
Consulting I/T Specialist
IBM Toronto Lab
IBM Software Services for Data Management
[My comments are solely my own and are not meant to represent an official IBM position - ask my cat!]
"Haider Rizvi" <ha****@nouce.ca.ibm.com> wrote in message news:ad**********@ca.ibm.com...
Read up
http://www7b.software.ibm.com/dmdd/l...10wilkins.html
Regards,
--
Haider
Nov 12 '05 #3

P: n/a
Correction - I see the FLUSH EVENT MONITOR SQL statement is in this article ... sorry I did not see it earlier ...

--

Bob

Not mentioned in this article is the fact that in V8.1 we now create a detailed deadlock event monitor for you so if you are using DB2 UDB V8.1 and are seeing deadlocks you should simply be able to format the event monitor output to see the deadlock events AND the SQL statements involved in the deadlock situation. The deadlock event monitor is called DB2DETAILDEADLOCK.

Also not mentioned in the article is the fact that to ensure that you get the full deadlock event monitor output to your file, turn off the event monitor (set its state to 0) before using db2evmon to dump the records, otherwise you will not get all the records in the file. Then simply turn the state back to zero. You can also try the FLUSH EVENT MONITOR SQL in this case, but I tend to do the former.

In any event, that will give you the SQL you are interested in...

--

Bob
Consulting I/T Specialist
IBM Toronto Lab
IBM Software Services for Data Management
[My comments are solely my own and are not meant to represent an official IBM position - ask my cat!]
"Haider Rizvi" <ha****@nouce.ca.ibm.com> wrote in message news:ad**********@ca.ibm.com...
Read up
http://www7b.software.ibm.com/dmdd/l...10wilkins.html
Regards,
--
Haider
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.