#!/bin/ksh # purge_dba_snapshot_tables.ksh # purge tables that store historical system administrative view # database performance data. # 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 scriptStatusLog=/db2/support/scripts/purge_dba_snapshot_tables.log # edit if needed prfile=/home/db2/sqllib/db2profile db2options="-av" function Usage { echo 'Usage: purge_dba_snapshot_tables.ksh -d -n ' echo 'All parameters are required. ' echo 'Use this script to purge snapshot table data older than n days.' } 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." >> $scriptStatusLog ; exit 1 ;; esac done if [ -f $prfile ]; then . $prfile else echo "$(date) Cannot locate $prfile." >> $scriptStatusLog Usage exit 1 fi # edit below if you have chosen to define these tables under a schema # other than DBA echo "$(date) Now executing purge_dba_snapshot_tables.ksh with following options:" >> $scriptStatusLog echo "DbName = $DbName " >> $scriptStatusLog echo "Purge Days = $PurgeDays " >> $scriptStatusLog echo "$(date) Now connecting . . . " >> $scriptStatusLog db2 connect to $DbName >> $scriptStatusLog 2>&1 echo "$(date) Now executing purge . . . " >> $scriptStatusLog db2 $db2options "delete from dba.snapdb where snapshot_timestamp < current timestamp - $PurgeDays days" >> $scriptStatusLog 2>&1 db2 $db2options "delete from dba.snapappl where snapshot_timestamp < current timestamp - $PurgeDays days" >> $scriptStatusLog 2>&1 db2 $db2options "delete from dba.snapappl_info where snapshot_timestamp < current timestamp - $PurgeDays days" >> $scriptStatusLog 2>&1 db2 $db2options "delete from dba.snapstmt where snapshot_timestamp < current timestamp - $PurgeDays days" >> $scriptStatusLog 2>&1 # Added 2009.02.27 - forgot to add the purge when I added the populate - jbenner db2 $db2options "delete from dba.snaptab where snapshot_timestamp < current timestamp - $PurgeDays days" >> $scriptStatusLog 2>&1 echo "$(date) purge_dba_snapshot_tables completed with RC = $? " >> $scriptStatusLog db2 connect reset > /dev/null 2>&1 db2 terminate > /dev/null 2>&1