Monitor DB2 instance status and database status

28 May,2010 by Jack Vamvas

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

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Monitor DB2 instance status and database status

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright