#! /bin/ksh
############################
#   AFI Monitor Script
############################
. /db2/uszlad48/sqllib/db2profile
export mondir=/home/bmwdev1/script/krishna/arc
export monlog1=$mondir/Archive_ZB_RCBL_ERROR_MSG_MIG_`date +%Y%m%d`.log
# connect to DB
db2 "connect to r2pdev user bmwdevup using summer08"
# extract the records from the table ZB_RCBL_ERROR_MSG_MIG
db2 -x "SELECT * FROM ZB_RCBL_ERROR_MSG_MIG WHERE TIMESTAMP_UPDATED < (CURRENT TIMESTAMP - 1 YEAR) WITH UR" >> "$monlog1"
echo "extraction completed"
while read line
do
 trackingnum=`expr substr "$line" 1 12`
 trackingnumsuffix=`expr substr "$line" 27 6`
 msgnum=`expr substr "$line" 47 5`
 tstmp=`expr substr "$line" 185 26`
#Delete the extracted records one by one
 db2 "DELETE FROM ZB_RCBL_ERROR_MSG_MIG WHERE TRACKING_NUM = '$trackingnum' AND TRACKING_NUM_SUFFIX = $trackingnumsuffix AND TIMESTAMP_UPDATED = '$tstmp'"
done < "$monlog1"
echo "purging completed"
# disconnect from DB2
db2 terminate
exit 0