Platform : Shell script & DB2 v9.1 , 9.5 , Linux
A simple shell script that returns:
database status
instance status
last backup
The column headers are created :Server,Instance,Database,DB_Status,Lats_backup,instance_status
The next line establishes a process exists for that instance , you may need to consider what value you use for $User on that line.
That's what I'm using as the input for the instance name .If the process exists the instance is available
The script iterates through all databases in a DB2 instance .A connect is created to derive value for last backup , then disconnect.
The database status is derived from the snapshot.
In the variable list the variables :OUTPUTDIR="/tmp" & TMPFILE=$OUTPUTDIR/DB2_status.output can be used to output the content.
To output to that file - do :
echo $Hostname,$Server_instance,$db_name,$db_status,$last_backup,$instance_status >> $TMPFILE
How regularly you poll is dependant on IT policies and server policies, there are a number of methods to poll and execute the script.
Emailing the output can be something as simple as using the : mail -s "Output from DB2_status script" db2@myserver.com
Overall this is a simple approach, I work in various companies where more enterprise level monitoring systems are set up ,such Tivoli Monitoring- which has
an agent installed on the server which polls every 5 minutes- that data is staored on a remote server - and depending on the situation , emails are sent to administrators
Even using this simple shell script approach- I would advise to have a further process that does a ping test on the server .
--------CODE STARTS------------------------
#!/bin/sh
#
#------------------------------------------------------------------------------------
# Program : DB2_status.sh
# Description : The script gets the following status :database_status,instance_status,last_backup
# :The script iterates through all the databases in the instance
# Usage : ./DB2_status.sh
: Run as the instance owner
#
# Author : Jack Vamvas
#-----------------------------------------------------------------------------------
#
#set -x
delimiter=";"
OUTPUTDIR="/tmp"
TMPFILE=$OUTPUTDIR/DB2_status.output
TIMESTAMP="`date +%Y-%m-%d-%H:%M:%S`"
User="$(id -un)"
Hostname="$(hostname -s)"
Server_instance=$Hostname"_"$User
db_name="na"
db_status="na"
last_backup="na"
instance_status="na"
#column headers
echo "SERVER","INSTANCE","DATABASE","DB_STATUS","LAST_BACKUP","INSTANCE_STATUS"
instance_status="$(ps -ef | grep db2sysc | grep $User | awk 'NR==1')"
for DB in `db2 list db directory | grep "Database name" | awk '{print $4}'`
do
db_name=$DB
db2 "connect to $DB" > /dev/null
last_backup="$(db2 select END_TIME FROM SYSIBMADM.DB_HISTORY where operation = "'B'" AND OBJECTTYPE = "'D'" ORDER BY START_TIME DESC FETCH FIRST 1 ROWS ONLY )"
db2 "connect reset" > /dev/null
db_status="$(db2 get snapshot for database on $DB | grep "Database status"|awk '{ print $4 }')"
echo $Hostname,$Server_instance,$db_name,$db_status,$last_backup,$instance_status
done
Related Posts
DB2 event monitor and snapshot monitor
DB2 - Real time monitoring of DB2 backup and restore