#!/bin/ksh
# db2audit_tables_prune.ksh
# prune DBA audit tables

# This will probably require some editing to run in your environment.

#=========================================================================
#  Parameters:
#     -d  :  Required.  The name of the database against which the SQL will
#            run.
#     -n  :  Number of days back to purge
#=========================================================================

# Downloaded from http://www.ebenner.com/db2luwdba-howto
# Please direct any questions to consulting@ebenner.com

# This work is licensed under the Creative Commons Attribution 3.0 United States License. To view a copy of this license, visit http://creativecommons.org/licenses/by/3.0/us/ or send a letter to Creative Commons, 171 Second Street, Suite 300, San Francisco, California, 94105, USA.

# edit if needed
sysoutLog=/db2/support/scripts/db2audit_tables_prune.log
# edit if needed
prfile=/home/db2/sqllib/db2profile

function Usage {
echo 'Usage: db2audit_tables_prune.ksh -d <database name> -n <days old> '
echo 'All parameters are required. '
echo 'Use this script to prune snapshot table data older than n days.'
echo "Output can be found in $sysoutLog"
}


if [[ "$#" -lt "4" ]]; then
  Usage
  exit
fi

while getopts  d:n: OPT
do
   case $OPT in
      d) DbName=$OPTARG ;;
      n) PurgeDays=$OPTARG ;;
      *) Usage; echo "$(date) Usage error." >> $sysoutLog ; exit 1 ;;
   esac
done


if [ -f $prfile ]; then
   . $prfile
else
   echo "$(date) Cannot locate $prfile." >> $sysoutLog
   Usage
   exit 1
fi
 
echo "$(date) Now executing db2audit_tables_prune.ksh with following options:" >>  $sysoutLog
echo "DbName = $DbName " >>  $sysoutLog
echo "Purge Days = $PurgeDays " >>  $sysoutLog
echo "$(date) Now connecting . . . " >>  $sysoutLog
db2 connect to $DbName >> $sysoutLog 2>&1
echo "$(date) Now executing prune . . . " >>  $sysoutLog

# edit below if you have chosen to define these tables under a schema 
# other than DBA
db2 -vm "DELETE FROM DBA.AUDIT    WHERE TIMESTAMP < current timestamp - $PurgeDays days" >> $sysoutLog 2>&1
db2 -vm "DELETE FROM DBA.OBJMAINT WHERE TIMESTAMP < current timestamp - $PurgeDays days" >> $sysoutLog 2>&1
db2 -vm "DELETE FROM DBA.SECMAINT WHERE TIMESTAMP < current timestamp - $PurgeDays days" >> $sysoutLog 2>&1
db2 -vm "DELETE FROM DBA.SYSADMIN WHERE TIMESTAMP < current timestamp - $PurgeDays days" >> $sysoutLog 2>&1
db2 -vm "DELETE FROM DBA.VALIDATE WHERE TIMESTAMP < current timestamp - $PurgeDays days" >> $sysoutLog 2>&1

echo "$(date) db2audit_tables_prune.ksh completed with RC = $?  " >>  $scriptStatusLog
db2 connect reset > /dev/null 2>&1
db2 terminate  > /dev/null 2>&1
