#!/usr/bin/ksh
#######################################################################################################
# Author: Felipe Alkain de Souza
#
# Script Name: check_db_log_full
#
# Functionality: This script checks the occurrence of transaction log full state on DB2 databases
#
# Usage: ./check_db_log_full.sh <database_name>
#
# Example: ./check_db_log_full.sh sampledb
#
# Obs: It's recommended that after the detection of a transaction log full situation, the command
# "db2diag -A" be executed to create another db2diag.log file and avoid future false alerts.
#
#######################################################################################################

. $HOME/sqllib/db2profile

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

LOGPRI=`db2 get db cfg for $1 |grep LOGPRIMARY |awk -F"= " '{print $2}' |tail -1`

LOGSEC=`db2 get db cfg for $1 |grep LOGSECOND |awk -F"= " '{print $2}' |tail -1`

LOGTOT=`echo "$LOGPRI+$LOGSEC" |bc`

LOGPATH=`db2 get db cfg for $1 |grep "Path to log files" |awk -F"= " '{print $2}' |tail -1`

LOGUSED=`ls -l $LOGPATH*LOG |wc -l`

if [ $LOGUSED -lt `echo "($LOGPRI+($LOGSEC/2))" |bc` ]; then
    echo "$STATE_OK&DB2 - The transaction log utilization is OK!"   
    exit $STATE_OK
   
    elif [ $LOGUSED -gt `echo "$LOGPRI+($LOGSEC/2)" |bc` -a $LOGUSED -lt $LOGTOT ]; then
    echo "$STATE_WARNING&DB2 - Please check database transaction workload."   
    exit $STATE_WARNING
   
    else
    LOGID=`db2diag -gi msg:="The active log is full" |grep "\"." |uniq |awk -F\" '{print $2}'`
    LOGCOUNT=`db2diag -gi msg:="The active log is full" |grep "\"." |uniq |awk -F\" '{print $2}' |wc -l`

    if [ $LOGCOUNT -ne "0" ]; then
    echo "$STATE_CRITICAL&DB2 - There was occurrence of transaction log full due to application handle: $LOGID"   
    exit $STATE_CRITICAL
    fi
   
fi