#!/bin/ksh
###
# Synopsis:   An all-in-one utility script for pre-production environments. 
#             Synchronise online backups to disk with recovery history and archived transaction log prunes, such that:
#             - only the correct number of validated backup images are kept;
#             - stale backup images are only deleted after a successful backup;
#             - prune history is in step with deleted backup images;
#             - archived logs are kept for the same period as retained backups, are only deleted after a successful backup,
#               and are removed at the same time as pruning history (this also removes the need for a secondary directories).
#               In other words we keep only those logs associated with the earliest backup we are keeping (and newer ones).
#               See note 6. for what constitutes the 'correct' number of backups.
# 
# Parameters:   $1 instance
#               $2 database alias to backup
#               $3 number of backups to keep (see note 6.)
#           
#            CUSTOMISATION NOTES
#            =================== 
#            1. This runs from the instance owner login only; on that basis there is minimal error-checking.
#            2. Supply a value for $BACKUP_PATH at current line 40.
#            3. There will always be a gap between the end of the backup and the next archived transaction log, so the code
#               has extra logic to ensure the oldest transaction log is kept for the oldest backup.
#            4. It allows for recovery history file changes (during a list history backup, SQL2155W)
#            5. It can cope with log streams other than C0000000. 
#            6. For consistency, the num_backups_to_keep parameter should really be ONE LESS than db cfg NUM_DB_BACKUPS. 
#               Reason: DB2 will automatically prune the history at the end of a successful backup if the number of backups 
#               in the history file  >= NUM_DB_BACKUPS. However automatic pruning does not touch the archive logs,
#               so we need the history in place to prune both the logs and the history in one go.
#            7. The REC_HIS_RETENTN db cfg parameter should also be consistent.
#            8. This script is for backups on single partition databases only. See the dbascripts maintenance suite at
#               http://www.sustainablesoftware.net/free_downloads.htm for an example of a partitioned backup. If you modify
#               the script here for multi-node backups, remember that there is a separate recovery history for each partition.
#
# Credits:      Mark Samson of IBM, with whom I have had the pleasure of collaborating on this and much else for many months.
# Alex Levy t/a http://www.sustainablesoftware.net September 2008.
###
#set -x

. ~$USER/sqllib/db2profile
BACKUP_PATH=                                       #  <========== Fill this in!  
BACKUP_PATH=${BACKUP_PATH:?`tput smso` Please supply a value for \$BACKUP_PATH, I\'m quitting. `tput rmso`}

if [[ $# -eq 3 ]]
then 
   typeset -l instance=$1 database=$2
   typeset -u INSTANCE=$1 DATABASE=$2
   typeset -i num_backups_to_keep=$3
else 
   print `tput smso` "Usage! $0 instance database number_of_backups_to_keep" `tput rmso`
   exit 1
fi 

# Create the backup directory if it doesn't already exist
if [[ -d $BACKUP_PATH ]]
then :
else mkdir -m 755 $BACKUP_PATH
fi

if [[ $num_backups_to_keep -gt 0 ]]
then
   #Backup database
   db2 -v backup db $database online to $BACKUP_PATH compress include logs without prompting
   typeset -i RC=$?
   print Backup return code: $RC
   if [[ $RC -eq 0 ]]
   then 
      image_to_check=$(ls -1r $BACKUP_PATH | head -1)
      db2ckbkp -h ${BACKUP_PATH}/$image_to_check
      RC2=$? ; if [[ $RC2 -gt 0 ]]
               then print "$0 : Cannot verify backup image, please investigate db2ckbkp output above. Exiting ..." 
                    exit $RC2
               fi
   fi
fi

typeset -i no_backups=`ls -1 ${BACKUP_PATH}/${DATABASE}*.001* |wc -l` # exclude files that aren't backups, e.g. backhist listing.
print number of backups $no_backups 
# Prune the history file, if and only if the last backup succeeded.
# Remove archive transaction logs for expired backups, if there are a requisite number of successful backups.
# Remove expired backups in step. 
 
if [[ $num_backups_to_keep -gt 0 && $RC -lt 2 && $no_backups -ge $num_backups_to_keep ]]
then
   db2 -v connect to $database 
   #  Remove all history and associated archive logs from 1 second before the N th successful backup.
   #  Use a default date/time in the past if we have less than N backups. The code uses the putative timestamp of the French Revolution.
   #  The subselect only returns the row for the N th backup,  so if less than N backups exist, then it returns no rows;
   #  in which case we need an aggregate function such as max to expose the null, so we can coalesce the null into the default timestamp.     
   # operation = 'B' = backup
   # objecttype = 'D' = full backup
   # devicetype = 'D' = disk
   # sqlcode is null  -- bit odd this, it's only populated on failure
   # sqlwarn is null -- ditto
   timestmp=$(db2 -x "select coalesce(max(start), 17890713235959) from \
                        (select bigint(start_time) - 1 as start, \
                        row_number() over(order by start_time desc) as backup \
                        from   sysibmadm.db_history              \
                        where  operation = 'B'                   \
                        and    objecttype = 'D'                  \
                        and    devicetype = 'D'                  \
                        and    sqlcode is null                   \
                        and    sqlwarn is null                   \
                        ) as zzz                                 \
                        where backup = $num_backups_to_keep" )
   # there really should be a return code check here
   db2 -v prune history $timestmp and delete
   # and here
   while [[ $no_backups -gt $num_backups_to_keep ]]
   do
      backup_to_zap=$(ls -1 ${BACKUP_PATH}/${DATABASE}.*.001.* | head -1)
      rm -f $backup_to_zap
      let no_backups=no_backups-1
   done

   # compress the  latest backup a bit more if it succeeded:
   # at worst this will only attempt to gzip an already gzipped file,if he backup failed and there's no image.
   target=$(ls -1t ${BACKUP_PATH}/${DATABASE}.*.001 | head -1)
   /usr/local/bin/gzip -v $target 
   > ${BACKUP_PATH}/backhist
 
   # loop until the recovery history file is stable and then report it 
   RC=999
   typeset -i no_loops=0
   while [[ $RC -gt 0 ]]
   do
      db2 -v list history backup since $timestmp for $database > ${BACKUP_PATH}/backhist
      RC=$?
      print RC for list history was $RC
      if [[ $no_loops -gt 720 ]]
      then
         # then you've been waiting an hour 
         print $0 "I'm tired of waiting for the recovery history file to stabilise. I'm giving up"
         break
      else
         sleep 5
         let no_loops=no_loops+1
      fi
   done
   echo "Content of backhist file:"
   cat ${BACKUP_PATH}/backhist
   db2 -v commit
   # SQL1493N: archive log command tries to connect, so you have to close the existing connection first.
   db2 -v connect reset
   db2 -v archive log for db $database 
   db2 -v terminate
fi

#Remove archive log files for databases that don't get backed up.
#operation = 'X'     = archive log
#operationtype = '1' = primary archlog directory, consistent with list history archive command and db cfg logarchmeth1
#                      [ note this won't work if a user exit is employed ].  

if [[ $num_backups_to_keep -eq 0 ]]
then 
   db2 -v connect to $database 
   db2 -x "select location                 \
           from   sysibmadm.db_history	   \
           where  operation = 'X'    	   \
           and    operationtype = '1' "	   > $BACKUP_PATH/archivelog.zaplist
   for log in `cat $BACKUP_PATH/archivelog.zaplist`
   do
       rm $log
   done
   # prune history in step
   timestmp=$(db2 -x "select max(start_time) from sysibmadm.db_history where operation = 'X' and operationtype = '1'")
   db2 -v prune history $timestmp and delete
   wait
   db2 -v commit
   db2 -v connect reset
   db2 -v archive log for db $database 
   db2 -v terminate
fi
# Brevis esse Labore, obscurus fio.
