The UNIX and Linux Forums  


Go Back   The UNIX and Linux Forums > Top Forums > Shell Programming and Scripting
.
google unix.com



Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

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

Closed Thread
English Japanese Spanish French German Portuguese Italian Dutch Swedish Russian Norwegian Hungarian Hebrew Danish Bulgarian Greek Powered by Powered by Google
 
LinkBack Thread Tools Search this Thread Rate Thread Display Modes
  #1 (permalink)  
Old 10-13-2008
regnumber regnumber is offline
Registered User
  
 

Join Date: Oct 2008
Posts: 9
shell script for archive purge

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
  #2 (permalink)  
Old 10-13-2008
cfajohnson's Avatar
cfajohnson cfajohnson is online now Forum Advisor  
Shell programmer, author
  
 

Join Date: Mar 2007
Location: Toronto, Canada
Posts: 2,362


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"

  #3 (permalink)  
Old 10-14-2008
regnumber regnumber is offline
Registered User
  
 

Join Date: Oct 2008
Posts: 9
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
  #4 (permalink)  
Old 10-14-2008
cfajohnson's Avatar
cfajohnson cfajohnson is online now Forum Advisor  
Shell programmer, author
  
 

Join Date: Mar 2007
Location: Toronto, Canada
Posts: 2,362

What's wrong with the script I posted?
  #5 (permalink)  
Old 10-14-2008
regnumber regnumber is offline
Registered User
  
 

Join Date: Oct 2008
Posts: 9
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
  #6 (permalink)  
Old 10-14-2008
cfajohnson's Avatar
cfajohnson cfajohnson is online now Forum Advisor  
Shell programmer, author
  
 

Join Date: Mar 2007
Location: Toronto, Canada
Posts: 2,362

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"

Make sure that the line you have built is correct before removing the echo.
Closed Thread

Bookmarks

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On




All times are GMT -4. The time now is 06:02 PM.


Powered by: vBulletin, Copyright ©2000 - 2006, Jelsoft Enterprises Limited. Language Translations Powered by .
vBCredits v1.4 Copyright ©2007 - 2008, PixelFX Studios
The UNIX and Linux Forums Content Copyright ©1993-2009. All Rights Reserved.Ad Management by RedTyger

Content Relevant URLs by vBSEO 3.2.0