. $HOME/sqllib/db2profile
set -x
LD=/dbamon/`whoami`/mon/hadr_stats
mac=`hostname`
DTTM=`date +"%y.%m.%d"`
db=$1
user=`whoami`
#NFCNLST=abc@gmail.com
NFCNLST=abc@gmail.com
rm $LD/state.msg 2>/dev/null
rm $LD/role.msg 2>/dev/null
mkdir -p $LD 2>/dev/null
rm $LD/loggap.$1.$mac.$DTTM.out $LD/hbeatm.$1.$mac.$DTTM.out $LD/time.$1.$mac.$DTTM.out $LD/state.$1.$mac.$DTTM.out $LD/status.$1.$mac.$DTTM.out $LD/buffused.$1.$mac.$DTTM.out $LD/all.$1.$mac.$DTTM.out $LD/db2pd.$1.$mac.$DTTM.out 2>/dev/null
db2pd -d $1 -hadr > $LD/db2pd.$1.$mac.$DTTM.out
cp $LD/db2pd.$1.$mac.$DTTM.out $LD/db2pd.$1.$mac.$DTTM.debug.out ##----Added to dbug the script
if [ $? -eq 0 ]
then
#CHK=`cat $LD/db2pd.$1.$mac.$DTTM.out | grep -i STANDBY_REPLAY_LOG_FILE | awk -F "," {'print $1'}`
CHK=`cat $LD/db2pd.$1.$mac.$DTTM.out | tail -2 | head -1 | awk '{print $1}'`
if [ "$CHK" != "HADR" ]
then
cat $LD/db2pd.$1.$mac.$DTTM.out | grep HADR_LOG_GAP | awk {'print $3'} >> $LD/loggap.$1.$mac.$DTTM.out
cat $LD/db2pd.$1.$mac.$DTTM.out | grep TIME_SINCE_LAST_RECV | awk {'print $3'} >> $LD/hbeatm.$1.$mac.$DTTM.out
cat $LD/db2pd.$1.$mac.$DTTM.out | egrep -i "HADR_ROLE|HADR_STATE" | awk -F "=" {'printf $2'} >> $LD/state.$1.$mac.$DTTM.out
cat $LD/db2pd.$1.$mac.$DTTM.out | grep -i -w "HADR_CONNECT_STATUS" | awk {'print $3'} >> $LD/status.$1.$mac.$DTTM.out
find $LD/ignore.congestion -cmin +10 | while read line
do
rm $line 2>/dev/null
done
cat $LD/status.$1.$mac.$DTTM.out | tr '[:lower:]' '[:upper:]' | read status
if [ $status = "CONGESTED" ]
then
if [ ! -f $LD/ignore.congestion ]
then
mail -s "$mac:`whoami`:$1: Traffic is $status between the HADR pair" abc@gmail.com < $LD/db2pd.$1.$mac.$DTTM.out
touch $LD/ignore.congestion
fi
fi
if [ -f $LD/hadr_stats.$1.$mac.$DTTM.out ]
then
SRVO=`cat $LD/hadr_stats.$1.$mac.$DTTM.out | tail -1 | awk '{print $4}'`
else
file=`ls -lrt $LD/hadr_stats.$1.$mac.*.out | tail -1 | awk '{print $9}'`
SRVO=`cat $file | tail -1 | awk '{print $4}'`
fi
SRV=`cat $LD/db2pd.$1.$mac.$DTTM.out | grep -i HADR_ROLE | awk {'print $3'}`
if [[ $SRVO != $SRV && $status != "DISCONNECTED" && $SRVO != "" ]]
then
echo "Role switch happened for $db. $mac is now the new $SRV" > $LD/role.msg
mail -s "!$mac:$user:$db: HADR Role switch" $NFCNLST < $LD/role.msg
if [ $SRV = "STANDBY" ]
then
db2 connect to $db > /dev/null
db2 -v "force applications all" > $LD/force.apps.$DTTM.out
db2 terminate > /dev/null
fi
fi
if [ $SRV = "STANDBY" ]
then
cat $LD/db2pd.$1.$mac.$DTTM.out | grep -i STANDBY_RECV_BUF_PERCENT| awk -F "=" {'print $2'} >> $LD/buffused.$1.$mac.$DTTM.out
chk=`cat $LD/db2pd.$1.$mac.$DTTM.out | head -2 | tail -1 | awk '{print $8}'`
if [ $chk = "Active" ]
then
cat $LD/db2pd.$1.$mac.$DTTM.out | head -2 | tail -1 | awk '{print $17}' >> $LD/time.$1.$mac.$DTTM.out
else
cat $LD/db2pd.$1.$mac.$DTTM.out | head -2 | tail -1 | awk '{print $16}' >> $LD/time.$1.$mac.$DTTM.out
fi
else
cat $LD/db2pd.$1.$mac.$DTTM.out | head -2 | tail -1 | awk '{print $16}' >> $LD/time.$1.$mac.$DTTM.out
echo "-" >> $LD/buffused.$1.$mac.$DTTM.out
fi
paste $LD/time.$1.$mac.$DTTM.out $LD/loggap.$1.$mac.$DTTM.out $LD/hbeatm.$1.$mac.$DTTM.out $LD/state.$1.$mac.$DTTM.out $LD/status.$1.$mac.$DTTM.out $LD/buffused.$1.$mac.$DTTM.out > $LD/all.$1.$mac.$DTTM.out
if [ $? -eq 0 ]
then
rm $LD/loggap.$1.$mac.$DTTM.out $LD/hbeatm.$1.$mac.$DTTM.out $LD/time.$1.$mac.$DTTM.out $LD/state.$1.$mac.$DTTM.out $LD/status.$1.$mac.$DTTM.out $LD/buffused.$1.$mac.$DTTM.out $LD/db2pd.$1.$mac.$DTTM.out 2>/dev/null
fi
if [ ! -f $LD/hadr_stats.$1.$mac.$DTTM.out ]
then
echo "Timestamp LogGapAvg HeartBeatsMissed Server State Status RcvBuffUsedpct" >> $LD/hadr_stats.$1.$mac.$DTTM.out
fi
cat $LD/all.$1.$mac.$DTTM.out >> $LD/hadr_stats.$1.$mac.$DTTM.out
rm $LD/all.$1.$mac.$DTTM.out 2>/dev/null
elif [ "$CHK" = "HADR" ]
then
echo "HADR is not active on $mac:$user:$db" > $LD/state.msg
mail -s "!$mac:$user:$db: HADR state INACTIVE" $NFCNLST < $LD/state.msg
else
echo "Cannot identify HADR state on $mac:$user:$db" > $LD/state.msg
mail -s "!$mac:$user:$db: HADR state UNKNOWN" $NFCNLST < $LD/state.msg
fi
else
echo "Cannot identify HADR state on $mac:$user:$db as db2pd command is failing" > $LD/state.msg
mail -s "!$mac:$user:$db: HADR state UNKNOWN" $NFCNLST < $LD/state.msg
fi
exit 0