Tuesday, February 15, 2011

db2 Automation - HADR status check

Ever wanted to check what's the HADR status, on multiple instances and multiple databases on the same host?

# file name - "hadr_status_check_v2.sh"
# Crontab entry --------
# 0 6 * * * . $HOME/.profile; /db2_maint_scripts/hadr_check.sh
rm hadr_check.out
db2ilist | while read instance >> hadr_check.out 2>&1
export DB2INSTANCE=$instance
db2 -o terminate > /dev/null 2>&1
db2 list active databases|grep -i 'Database name'|awk '{print $NF}' | while read dbname
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

# email the report
if [ "$?" = "0" ]; then
cat hadr_check.out | mail -s "HADR Status Report for host: `hostname`" "someone@email.com"
cat hadr_check.out | mail -s "HADR Status Report errored on host: `hostname`" "someone_else@email.com"

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.

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.