Ever wanted to check what's the HADR status, on multiple instances and multiple databases on the same host?
#!/usr/bin/ksh
# file name - "hadr_status_check_v2.sh"
# Crontab entry --------
# 0 6 * * * . $HOME/.profile; /db2_maint_scripts/hadr_check.sh
#
#
clear
rm hadr_check.out
db2ilist | while read instance >> hadr_check.out 2>&1
do
export DB2INSTANCE=$instance
db2 -o terminate > /dev/null 2>&1
db2 list active databases|grep -i 'Database name'|awk '{print $NF}' | while read dbname
do
echo "Checking HADR Status for - $dbname on Instance $instance"
echo "\n========== HADR Status for - $dbname on Instance $instance ==========\n" >> hadr_check.out 2>&1
db2 get snapshot for database on $dbname | grep -p -i 'HADR Status' >> hadr_check.out 2>&1
done
done
# email the report
if [ "$?" = "0" ]; then
cat hadr_check.out | mail -s "HADR Status Report for host: `hostname`" "someone@email.com"
else
cat hadr_check.out | mail -s "HADR Status Report errored on host: `hostname`" "someone_else@email.com"
fi
echo "========== Check out the file "hadr_check.out" for actual details =========="
# --- end of script ------
If you run this script as the db2 instance owner or with any user with privileges as db2 sys admin you should be able to run this script successfully.
To get it to work as a scheduled job, make any entry in the crontab as follows - while logged in as db2 instance owner or as user with db2 sys admin.
0 6 * * * . $HOME/.profile; /db2_maint_scripts/hadr_check.sh
run every morning 6 AM at 0th minute every day, every month etc...
If you carefully look at the crontab command, I use ". $HOME/.profile;" to call the profile so that it picks up the db2 profile setting as well. Otherwise the db2ilist and other db2 commands will fail. Other pieces are "db2_maint_scripts" - is where my scripts are located and
"hadr_check.sh" - is the script name.
If other settings like email and privileges are setup as required, you should receive one email every morning with HADR status details for each database on each instance on a given host. Please if the database is in deactivated state the "LIST ACTIVE DATABASES" will not list all databases. Any thoughts on how to get around this?
Apart from HADR status check, this script also demonstrates how to use unix command output to create a loop and this script can be easily adapted to monitor and take some action like failing over to other stand-by database - to trigger a TAKOVER command.
Please post any questions or troubleshooting help in the comments sections.
