I am writing a shell script for Archive Purge for the table having rows < 1 year. The shell script has to extract the rows from the table and write those extracted rows to a text file. Then from the text file, each rows will be read and deleted by means of delete query one by one. The fields will be defined by means of position.
For Eg. ZB_RCVBL_TRACKER consists of the fields as follows.
PCN, ACCNO, TRANCD, AMOUNT, LOB_TIMESTAMP
After the rows are extracted and written in to a text file. I need to get the fields PCN, ACCNO, TRANCD, LOB_TIMESTAMP from the text file and pass those values to the query
DELETE FROM ZB_RCVBL_TRACKER where PCN = ? and ACCNO=? and TRANCD = ? and LOB_TIMESTAMP = ?
If the SQLCODE is 0, I can proceed furthur.
Can anyone help me to achieve this task.
Code:
==================================
#! /bin/ksh
############################
# AFI Monitor Script
############################
. /db2/uszlad48/sqllib/db2profile
export mondir=/home/bmwdev1/script/krishna
export monlog=$mondir/Error_Report_`date +%Y%m%d`.log
export bakdir=/home/bmwdev1/script/krishna/bkup
export baklog=$bakdir/Error_Report_`date +%Y%m%d`_bkup.log
# connect to DB
#echo "connect to database r2pdev" >>$monlog
# extract the eligible rows from the table
db2 "SELECT * FROM ZB_RCVBL_TRACKER WHERE TIMESTAMP_UPDATED < (SYSDATE – 1 YEAR)WITH UR" >> $monlog
echo "After SQL execution" >> $monlog
==================================
Krishnakanth
More UNIX and Linux Forum Topics You Might Find Helpful
Powered by 
10-13-2008
Threaded Mode
