Checking database locks
This script checks the occurrence of applications in lock-wait states on DB2 databases
What | |
---|---|
When |
2010-08-06 17:41
2010-08-06 17:41
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