DB2 health script

12 October,2011 by Jack Vamvas

This script runs across all DB2 servers every morning and creates a report.  It’s a quick way of getting DB2 health status , in my inbox. I  open the report in my Blackberry , while travelling into work.

 It doesn’t replace Monitoring , which deals with thresholds being met .

 The script runs as part of a Powershell  scripts , using plink to connect to the Linux Servers.

 The information returned is : Hostname,Server_instance,db_name,db_status,db_size,db_space_available,disk_space_available,last_backup,db_connect,hadr_status,instance_status

With minimal work , the script is easily modified to return other information such as HADR heartbeats missed.

 

 

#!/bin/sh
#
#------------------------------------------------------------------------------------
# Program      :  healthDB2.sh
# Description  :  The script gets the following status            
#              
# Usage        : ./healthDB2.sh
               : Run as the instance owner 
#
# Author       :  Jack Vamvas
#-----------------------------------------------------------------------------------
#
#set -x
delimiter=";"
OUTPUTDIR="/tmp"
TMPFILE=$OUTPUTDIR/healthDB2.output.$DB2INSTANCE
TIMESTAMP="`date +%Y-%m-%d-%H:%M:%S`"
User="$(id -un)"
Hostname="$(hostname -s)"
Server_instance=$Hostname"_"$User
role="na"
state="na"
connection="na"
heartbeats_missed="na"
db_name="na"
db_status="na"
db_size="na"
db_space_available="na"
disk_space_available="na"
last_backup="na"
db_connect="na"
hadr_status="na"
instance_status="na"
#column headers
echo "SERVER","INSTANCE","DATABASE","DB_STATUS","DB_SIZE","DB_SPACE_AVAILABLE","DISK_SPACE_AVAILABLE","LAST_BACKUP","DB_CONNECT","HADR_STATUS","INSTANCE_STATUS"

db_status="$(db2 get snapshot for database on $DB | grep "Database status"|awk '{ print $4 }')"
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,$db_size,$db_space_available,$disk_space_available,$last_backup,$db_connect,$hadr_status,$instance_status
done


Read More

DB2 list schemas of a database - DBA DB2

Force connections of a database in DB2

DB2 Query Tuning – db2expln

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 DB2 health script

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