shell script for archive purge

regnumber · #1 (**permalink**) 10-13-2008

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
Thread	Thread Starter	Forum	Replies	Last Post
Generic Shell Script to Archive a file	mak1600	Shell Programming and Scripting	8	07-26-2008 08:51 AM
script for purge	JP003	Shell Programming and Scripting	1	07-11-2008 06:42 AM
File Archive Script	KeesH	Shell Programming and Scripting	10	06-09-2008 11:11 AM
making a archive script	jimmyc	Shell Programming and Scripting	1	09-15-2007 04:02 AM
Read from file then purge or archive.	kayarsenal	Shell Programming and Scripting	15	08-10-2006 08:24 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode	Rate This Thread:


	Powered by