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
==================================

cfajohnson · #2 (**permalink**) 10-13-2008

Code:

while read PCN ACCNO TRANCD AMOUNT LOB_TIMESTAMP
do

   db2 "DELETE FROM ZB_RCVBL_TRACKER where PCN = $PCN and \
       ACCNO=$ACCNO and TRANCD = $TRANCD and LOB_TIMESTAMP = $LOB_TIMESTAMP" || break

done < "$FILE"

regnumber · #3 (**permalink**) 10-14-2008

Thank you for your reply Johnson.

Here the problem is how to read PCN, ACCNO, TRANCD, AMOUNT, LOB_TIMESTAMP from a text file. The data will be as follows in the text file.

Quote:

9564587 111222333 AD 563.85 2008-10-01-00.00.00.000000
9564588 111222334 AE 567.85 2008-10-02-00.00.00.000000
9564589 111222335 AF 963.85 2008-10-03-00.00.00.000000
9564590 111222336 AH 566.85 2008-10-04-00.00.00.000000

The data existing in a text file is a raw data. The length of each field is fixed length.
Is it possible to do like as follows.
pcn is 7 byte starting from 1 to 7
accno is 9 byte starting from 9 to 17
tran_cd is 2 byte starting from 19 to 20
amount is 8 byte starting from 22 to 29
LOB_TIMESTAMP is 26 byte starting from 30 to 55

can we define like this and will pass data. If so how to define in shell script ?

Could you please let me know how to proceed furthur.

Krishnakanth

cfajohnson · #4 (**permalink**) 10-14-2008

What's wrong with the script I posted?

regnumber · #5 (**permalink**) 10-14-2008

Johnson,

The script that you have given works fine. But in my case it is a different one.

I have given the sample what I am working now. Let me give you the exact one what i am working currently.

following is the data in a text file.(db2 "SELECT TRACKING_NUM, TRACKING_NUM_SUFFIX, ERROR_SRC_CD, MSG_NUM, MSG_ID, MSG_TYP, MSG_TEXT, TIMESTAMP_UPDATED, USER_UPDATED FROM ZB_RCBL_ERROR_MSG_MIG WITH UR" >> $monlog1)

Quote:

12345 10 E-123 - - - Hello - -
12345 10 E - - - Hello - -
12345 10 A - - - Hello - -
12345 10 T - - - Hello - -
1234 15 - - - - ERROR - -
123456 10 - - - - ERRORS - -
123456 10 - - - - ERRORS - -
255678 200 - - - - ERRORCORRECTION - -
255678 200 - - - - ERRORCORRECTION - -
1111111111 10 - - - - - - -
7890 10 ERRORMSG 90 78 E ERROR 2008-08-11-06.53.12.924263 R2P
12345 11 eadf j123 10 E this is emsg 2008-08-11-14.05.11.745265 -
12345 11 eadf j123 10 E this is emsg 2008-08-12-09.39.38.886385 -
12345 11 eadf j123 10 E this is emsg 2008-08-12-09.42.21.223536 -
12345 11 eadf j123 10 E this is emsg 2008-08-12-09.48.57.911702 -
12345 11 eadf j123 10 E this is emsg 2008-08-12-09.49.59.296360 -
22345 11 error j123 10 E this is an emsg 2008-08-12-10.08.33.843147 hello
255679 25 - - - - - - -
BEYJYWG83L 0 R 902 - B System Exception Occured, please refer to BMW_EXCEPTION_LOG table 2008-09-10-00.41.32.787352
BEYJYXMT4K 0 R 902 - B System Exception Occured, please refer to BMW_EXCEPTION_LOG table 2008-09-11-19.01.23.878551
BEYEHF93QY 0 R 003 B No message exists in ZBV_BILL_REJCT_RSN table 2008-08-28-16.31.01.881717
BEYJYTT8FQ 0 R 013 B Invalid Transaction Code 2008-09-04-17.39.04.005237
BEYJYTT8FQ 0 R 016 B Invalid Fee Code 2008-09-04-17.39.04.008137
BEYJYTT8FQ 0 R 016 B Invalid Fee Code 2008-09-04-17.39.04.011939
BEYJYTT8FQ 0 R 016 B Invalid Fee Code 2008-09-04-17.39.04.012851
BEYJY2SZVL 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-09.22.29.120192
BEYJY2SXNG 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-09.19.33.868772
BEYJY2SMS3 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-08.53.07.872138
BEYJY2SMJD 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-08.50.17.307262
BEYJY2EG2K 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-17-15.56.03.418917
002211556 0 R 001 B Identical Receivable transaction has already posted for this policy. 2008-09-19-01.41.01.454234
002211556 0 R 002 B One or more uncleared errored receivables for the policy 2008-09-23-01.36.07.982876
002211556 0 R 002 B One or more uncleared errored receivables for the policy 2008-09-23-01.38.44.515880
002211556 0 R 001 B Identical Receivable transaction has already posted for this policy. 2008-09-23-16.32.20.565704
6634583444 0 R 902 - J System Exception Occured, please refer to BMW_EXCEPTION_LOG table 2008-09-15-12.44.09.383234

If I give the following script.

Quote:

while read TRACKING_NUM TRACKING_NUM_SUFFIX ERROR_SRC_CD MSG_NUM MSG_ID MSG_TYP MSG_TEXT TIMESTAMP_UPDATED USER_UPDATED
do

db2 "SELECT TRACKING_NUM, TRACKING_NUM_SUFFIX, ERROR_SRC_CD, MSG_NUM, MSG_ID, MSG_TYP, MSG_TEXT, TIMESTAMP_UPDATED, USER_UPDATED FROM ZB_RCBL_ERROR_MSG_MIG WHERE TRACKING_NUM = $TRACKING_NUM AND TRACKING_NUM_SUFFIX = $TRACKING_NUM_SUFFIX AND TIMESTAMP_UPDATED = $TIMESTAMP_UPDATED WITH UR" >> $monlog2

done < "$FILE"

The script that you have given is not working in this case. Bacause the value of the field MSG_TEXT is like "Identical Receivable transaction has already posted for this policy." 73 bytes long.

Please let me know how to proceed furthur.

Krishnakanth

cfajohnson · #6 (**permalink**) 10-14-2008

Please put code and sample data inside [code] tags, not [quote] tags.

Test the script by echoing the command instead of executing it.

Code:
echo db2 "SELECT TRACKING_NUM, TRACKING_NUM_SUFFIX, ERROR_SRC_CD, MSG_NUM, MSG_ID, MSG_TYP, MSG_TEXT, TIMESTAMP_UPDATED, USER_UPDATED FROM ZB_RCBL_ERROR_MSG_MIG WHERE TRACKING_NUM = $TRACKING_NUM AND TRACKING_NUM_SUFFIX = $TRACKING_NUM_SUFFIX AND TIMESTAMP_UPDATED = $TIMESTAMP_UPDATED WITH UR"

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 09:51 AM
script for purge	JP003	Shell Programming and Scripting	1	07-11-2008 07:42 AM
File Archive Script	KeesH	Shell Programming and Scripting	10	06-09-2008 12:11 PM
making a archive script	jimmyc	Shell Programming and Scripting	1	09-15-2007 05:02 AM
Read from file then purge or archive.	kayarsenal	Shell Programming and Scripting	15	08-10-2006 09: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 Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by