Hi,
Have a Db2 9.5 Fp2 on Win2003x64. I have a .net App that connects using the IBM .Net Provider for DB2 (part of Data Server Client).

I get deadlock errors quite often. I want to know how I can debug the deadlock. Is there a way to find from logs the exact query that is causing a deadlock? Am a DB2 newbie, and request details on how to find the error. I couldnt find it on Control Center, and I couldnt find it with db2evmon.

To improve performance, I use db2bulkcopy.WritetoServer. This works fine from a business point-of-view as each row has only one user.

However, I am not sure how WriteToServer locks the table.
TIA
Kar

Db2BulkCopy seems to do a COMPLETE table Level LOCK. If you are having multiple threads trying to work on the same table, then there is a great deal of chance of having DEADLOCKs.

The easiest way to monitor, is to create an EVENT monitor for DEADLOCKs, run the applications and then Stop the event monitor. Check the DeadLock report generated.

Thanks,
Jayanta

You need to make sure that you are actually getting deadlocks before you check the deadlock event monitor (which is created by default on a new database since 8.2).

If the reason code (RC) = 2, it is a deadlock, if reason code = 68 it is a lock time out.

As pointed out by Marcus, you need to check whether it is a deadlock or a lock timeout....

Secondly you need to identify why there is a deadlock.... is it because of too many concurrent users or is it because of locks being held for too long, in which case you have to commit frequently

Hi,
Am a db2 newbie and request more help. My users get a message:
"Server was unable to process request. Error 40001 IBM DB2 NT64 SQl0911N. The current transaction has been rolled back because of a Deadloct or Timeout. Reason Code 2. SQLState 40001".
So it looks like a Deadlock to me.
I also get alerts in Control Center that the number of deadlock per hour has breached its limit.
I suppose I have a DB2DetailDeadlock event monitor, because it is creating evt files in the required folder. I cant see this monitor in Control Center however. How do I know what query is causing the deadlock? Is it available somewhere in control center? Or in the evt file? Or in a table?
TIA
Kar

You can print out the information that the deadlock event monitor is capturing by using DB2 supplied program which is described in the Command Reference manual (I don't recall the exact name, but you can look it up just as easily as me).

That tool is called db2evmon.

Hi,
I used db2evmon, but I still did not get the error details. Moreover, this command line tool just throws all the data into the Command Console, and that makes the data unavailable unless I pipe output to a text file (havent tried it yet).
What I see in evmon output is that the SQL Statement is not available.
I have just created my own Event Monitor, and have changed the Debugging Level to 4, and have also changed the Locktimeout to 30 s.
Lets see if that helps.

You need to get the detail report to see the SQL (and you need the event montior set up to allow details).

To help prevent deadlocks your LOCKLIST should be "automatic" (if you are using self-tunning memory manager) or try something like 4096.

Another thing you can try is alter all your tables to "volatile".