DB2 Blocking

It appears that there is no monitoring for DB2 blocking. We had an incident over the weekend where a process ended up timing out due to a lock wait, but we have no idea what process caused the blocking. I noticed for SQL Server there is a "Blocking" tab when you drill down into a time period, but not for DB2. Is there anyway to monitor blocking with DB2?

Thank you!

Find more posts tagged with

Blocking

db2

Accepted answers

All comments

brianflynn

awickham10, I checked around about this today. It seems that this is b/c when that feature was developed, the current versions of DB2 made the medicine worse than the illness... That is to say that monitoring blocking imposed too much performance overhead. We think this may have improved since then. We suggest you take a look at custom reports and alerts. You could do some reports to see blocking over time and alerts to let you know about any long outstanding locks. If you need help with the custom alerting and reporting features, the support team can help you. I will see if there is a feature request for this, here on Thwack, and if not, I will add it.

Thanks!

gcp

For DB2 9.7 and upwards, you can use "call monreport.LOCKWAIT" which will show who is holding and who is waiting on locks. Far easier than deciphering snapshots. There is also the Administrative view SYSIBMADM.MON_LOCKWAITS if you want to write your own queries/alerts.

Related to this, you may want to look at the DB CFG parameter LOCKTIMEOUT. The default for this is LOCKTIMEOUT = -1 which means wait forever. (Thanks IBM) Setting that to 30 or 60 seconds would see the waiting thread get a -911 SQLCode which would need to be handled in the application.