#!/usr/bin/ksh
#------------------------------------------------------------------------------------
# Program      :  backupdb.sh
# Description  :  The script backup database on an instance and notifies 
#              :  the dba the status
#              : usage :  ./backupdb.sh rajudb  /home/dba/reports/  
# Author       :  Raju Pillai
# Date         :  2010-10-28
#-----------------------------------------------------------------------------------
#####################################
### variables  ###
#####################################
notify_dba="Y"
prune_retention=7
dbas="raju@techsatwork.com"
prune_date=`TZ=$TZ+168 date "+%Y%m%d"`    ## 7 days =168 hrs
Server=$(hostname)
Instance="db2inst1"
backup_path="/db2inst1_backup,/db2inst1_backup,/db2inst1_backup"
backup_type="online"
####################################
#
if [ "$1" = "" ]
then    echo ""
        echo "Parm 1 database                     (required)"
        echo "Parm 2 Script Output Directory Path (optional) Defaults to instance home directory/reports"
        echo ""
        return
else    db_name=$1
fi
if [ "$2" = "" ]
then    OutputDir=`echo "/home/$Instance/reports"`
else    OutputDir=$2
fi
export PATH=$PATH:${HOME}/sqllib/bin/
. ${HOME}/sqllib/db2profile
Datetime="`date +%Y-%m-%d-%H:%M:%S`"
echo "  " > $OutputDir/backupdb.out
echo "###################################################################################" >> $OutputDir/backupdb.out
echo "Backup for $db_name started on : $Datetime on $Server in $Instance " >> $OutputDir/backupdb.out
echo "###################################################################################" >> $OutputDir/backupdb.out
echo "  " >> $OutputDir/backupdb.out
echo "backup db $db_name $backup_type to $backup_path compress;" > $OutputDir/backup_cmd.tmp
echo "prune history $prune_date and delete;" > $OutputDir/prune_cmd.tmp
echo "select substr(comment,1,30),timestamp(start_time),timestamp(end_time),substr(firstlog,1,25),substr(lastlog,1,25),seqnum,location from sysibmadm.db_history where operation = 'B' and date(timestamp(end_time)) = current_date and sqlcode is null;" > $OutputDir/chkbkpsql.sql

 db2 -txf $OutputDir/backup_cmd.tmp >> $OutputDir/backupdb.out

            if [ "$?" = "0" ]
              then
                   echo "Backup successful for  - > $db_name"
                      db2 connect to $db_name;
                      db2 -txf $OutputDir/chkbkpsql.sql >> $OutputDir/backupdb.out
                      echo " "  >> $OutputDir/backupdb.out
                      echo "Pruning recovery history,deleting archive and backup file .."  >> $OutputDir/backupdb.out
                      db2 -txf $OutputDir/prune_cmd.tmp >> $OutputDir/backupdb.out
                   if [ "$notify_dba" = "Y" ]
                     then
                          for emailaddr in $dbas
                              do
                                 mailx -s "Backup successful for $db_name - $Server" $emailaddr < $OutputDir/backupdb.out
                          done
                   fi 
  
              else
                      for emailaddr in $dbas
                              do
                                 mailx -s "Backup failed for $db_name - $Server" $emailaddr < $OutputDir/backupdb.out
                          done
            fi