Script for monitoring Deadlock, lock-wait and Locktimeout

Shashi Shekhar

unread,

Feb 29, 2016, 2:00:12 PM2/29/16

to

Hi , We have got a task to write a proper script to alert DBA, if any deadlock or lock-timeout occurs. and regarding Lock-wait, we need to alert only when there are more than 25 parallel applications (i.e Application handle according to DB2 Perspective ) are in lock-wait.

I am working on DB2 - AIX. I am writing this script with approach of getting snapshot on database and get the lock timeout, deadlock and lock-wait value and keep those value in some table and when next time the script run and takes snapshot and if this time if any increment we find in any of these three item, we can send a mail to DBA.

like to know if this standard approach ? if there is something actually better way also if we can get some detail ( which application or which query was there during deadlock) and send the same in alert mail?

Your reply is really needed. Thanks in advance.

Regards
Shashi

Troels Arvin

unread,

Feb 29, 2016, 2:21:52 PM2/29/16

to

Hello Shashi,

We use an SQL statement like this:

SELECT count(*)
FROM sysibmadm.lockwaits
WHERE lock_wait_start_time < (CURRENT_TIMESTAMP - ? MINUTES)
AND db_name=?
AND appl_name<>'DB2HMON'

If it returns a value > 0, then we get an alert from the monitoring
system.

(Of course, we substitute the ? placeholders with relevant values for the
particular database.)

We also have a web-page where all current sessions may be seen including
session state and blocking/blocked other sessions. That way, the users
may themselves check the current state of affairs and contact each other,
if someone is blocking things. That page is written in Python (WSGI); I
can provide it somewhere, if someone's interested.

--
Troels

jba...@calculo-sa.es

unread,

Mar 2, 2016, 2:08:11 AM3/2/16

to

We use db2top -d mydatabase -a to monitor activity in the database. The option U shows you in real time all about current locks. Have you considered trying it? You don't need to install anything. It comes along with DB2.

Shashi Shekhar

unread,

Mar 2, 2016, 11:43:44 AM3/2/16

to

Thanks for reply, Actually I need an approach that I can use in shell script to alert in falling in critical scenario ( say 25 lock-wait in parallel) .

Ian

unread,

Mar 2, 2016, 12:26:36 PM3/2/16

to

On Wednesday, March 2, 2016 at 9:43:44 AM UTC-7, Shashi Shekhar wrote:

> Thanks for reply, Actually I need an approach that I can use in shell script to alert in falling in critical scenario ( say 25 lock-wait in parallel) .

Are you asking for someone to do your job for you? Troels gave you a very good suggestion as a starting point; please try this out and work to adapt it for your requirements.

Shashi Shekhar

unread,

Mar 21, 2016, 6:48:03 PM3/21/16

to

Thanks Troels. It's a good option for what my requirement is. For this, the script should run quite frequently, so that, it tries to capture most of the locakwait. But my team Leader was not ready to run very frequently.
Then we planned to use sysibmadm.snapdb from where we hold, total count and then compare with new total count. By the way , through this way, my expected requirement is not meeting but we thought to run this script for every 5 mins.

Thanks again.
Shashi