Personal tools
You are here: Home DB2 Scripts DB2 Nagios Plugins to monitor DB2 events Checking database locks
Navigation
Log in


Forgot your password?
 
Document Actions

Checking database locks

by Felipe Alkain de Souza last modified 2010-09-20 10:28

This script checks the occurrence of applications in lock-wait states on DB2 databases

What
When 2010-08-06
from 17:41 to 17:41
Add event to calendar vCal
iCal

#!/usr/bin/ksh
#######################################################################################################
# Author: Felipe Alkain de Souza
#
# Script Name: check_db_locks
#
# Functionality: This script checks the occurrence of applications in lock-wait states on DB2 databases
#
# Usage: ./check_db_locks.sh -d <database_name>
#
#######################################################################################################

. $HOME/sqllib/db2profile

### Nagios RCs Variables
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3


db2 connect to $1 > /dev/null 2>&1

if [ $? -ne 0 ]
then
        echo "$STATE_CRITICAL&DB2 - The database $1 did not connect!"
        exit $STATE_CRITICAL
        db2 connect reset
fi

locks=`db2 connect to $1 >/dev/null; db2 "select rtrim(char(LOCKS_WAITING)) as locks FROM TABLE(SNAPSHOT_DATABASE('$1', -1)) as SNAPSHOT_DATABASE" |grep -v "-" |grep -vi locks |grep -v record |grep ^[0-9]; db2 connect reset >/dev/null 2>&1`

# you can use DB2 option -X instead of using multiple OS "grep".

#locks=`db2 connect to $1 >/dev/null; db2 -x "select rtrim(char(LOCKS_WAITING)) as locks FROM TABLE(SNAPSHOT_DATABASE('$1', -1)) as SNAPSHOT_DATABASE"; db2 connect reset >/dev/null 2>&1`

lockwait=`db2 get snapshot for locks on $1 |grep "Total wait time (ms)" |sort -rn |awk -F"= " '{print $2}' |head -1`

if [ $locks -ne 0 -a $lockwait -gt 60000 ] ; then
        lockids=`db2 get snapshot for locks on $1 |grep "ID of agent holding lock" |awk -F"= " '{print $2}' |sort -u`
        echo "$STATE_CRITICAL&DB2 - $locks lock(s). Possible app. handles are: $lockids"   
        exit $STATE_CRITICAL
else
        echo "$STATE_OK&DB2 - None applications in lock-wait state"
fi

Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls