f



Script for monitoring Deadlock, lock-wait and Locktimeout

Hi , We have got a task to write a proper script to alert DBA, if any deadl=
ock 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 a=
ccording to DB2 Perspective )  are in lock-wait.=20

I am working on DB2 - AIX. I am writing this script with approach of gettin=
g snapshot on database and get the lock timeout, deadlock and lock-wait val=
ue 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 t=
hree item, we can send a mail to DBA.

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

Your reply is really needed. Thanks in advance.=20

Regards
Shashi=20


=20

0
Shashi
2/29/2016 7:00:08 PM
comp.databases.ibm-db2 12448 articles. 3 followers. arunrocks (9) is leader. Post Follow

5 Replies
1067 Views

Similar Articles

[PageSpeed] 10

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
0
Troels
2/29/2016 7:21:47 PM
El lunes, 29 de febrero de 2016, 20:00:12 (UTC+1), Shashi Shekhar  escribi=
=F3:
> Hi , We have got a task to write a proper script to alert DBA, if any dea=
dlock or lock-timeout occurs. and regarding Lock-wait, we need to alert onl=
y when there are more than 25 parallel applications (i.e Application handle=
 according to DB2 Perspective )  are in lock-wait.=20
>=20
> I am working on DB2 - AIX. I am writing this script with approach of gett=
ing snapshot on database and get the lock timeout, deadlock and lock-wait v=
alue and keep those value in some table and when next time the script run a=
nd takes snapshot and if this time if any increment we find in any of these=
 three item, we can send a mail to DBA.
>=20
> like to know if this standard approach ? if there is something actually b=
etter way also if we can get some detail ( which application or which query=
 was there during deadlock) and send the same in alert mail?
>=20
> Your reply is really needed. Thanks in advance.=20
>=20
> Regards
> Shashi

We use db2top -d mydatabase -a to monitor activity in the database. The opt=
ion U shows you in real time all about current locks. Have you considered t=
rying it? You don't need to install anything. It comes along with DB2.
0
jbaron
3/2/2016 7:08:06 AM
On Wednesday, 2 March 2016 12:38:11 UTC+5:30, jba...@calculo-sa.es  wrote:
> El lunes, 29 de febrero de 2016, 20:00:12 (UTC+1), Shashi Shekhar  escrib=
i=F3:
> > Hi , We have got a task to write a proper script to alert DBA, if any d=
eadlock or lock-timeout occurs. and regarding Lock-wait, we need to alert o=
nly when there are more than 25 parallel applications (i.e Application hand=
le according to DB2 Perspective )  are in lock-wait.=20
> >=20
> > I am working on DB2 - AIX. I am writing this script with approach of ge=
tting 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 the=
se three item, we can send a mail to DBA.
> >=20
> > 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 que=
ry was there during deadlock) and send the same in alert mail?
> >=20
> > Your reply is really needed. Thanks in advance.=20
> >=20
> > Regards
> > Shashi
>=20
> We use db2top -d mydatabase -a to monitor activity in the database. The o=
ption 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.

Thanks for reply, Actually I need an approach that I can use in shell scrip=
t to alert in falling in critical scenario ( say 25 lock-wait in parallel) =
..=20
0
Shashi
3/2/2016 4:43:41 PM
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.

0
Ian
3/2/2016 5:26:34 PM
On Tuesday, March 1, 2016 at 12:51:52 AM UTC+5:30, Troels Arvin wrote:
> 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

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 


0
Shashi
3/21/2016 10:47:59 PM
Reply: