Hi,
I am writing a shell script for archive data from a table.
The design is as follows.
Step 1:
Execute the select query and extract the data into a text file.
Step 2:
The primary key for this table is TRACKING_NUM, TRACKING_NUM_SUFFIX, TIMESTAMP_UPDATED. So These three fields will be read from the file rcbl1.log and pass it to the delete query. so that the deletion will happen line by line.
Following is the script that performs this task.
Code :
______________________________________________________
#! /bin/ksh
############################
# AFI Monitor Script
############################
. /db2/uszlad48/sqllib/db2profile
export mondir=/home/bmwdev1/script/krishna/arc
export monlog1=$mondir/rcbl1.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 TRACKING_NUM, TRACKING_NUM_SUFFIX, ERROR_SRC_CD, MSG_NUM, MSG_ID, MSG_TYP, MSG_TEXT, TIMESTAMP_UPDATED, USER_UPDATED FROM BMWDEV1.ZB_RCBL_ERROR_MSG_MIG WHERE TIMESTAMP_UPDATED < (CURRENT TIMESTAMP - 1 YEAR)WITH UR" >> $monlog1
while read TRACKING_NUM TRACKING_NUM_SUFFIX ERROR_SRC_CD MSG_NUM MSG_ID MSG_TYP MSG_TEXT TIMESTAMP_UPDATED USER_UPDATED
do
db2 "DELETE FROM ZB_RCBL_ERROR_MSG_MIG WHERE TRACKING_NUM = $TRACKING_NUM AND TRACKING_NUM_SUFFIX = $TRACKING_NUM_SUFFIX AND TIMESTAMP_UPDATED = $TIMESTAMP_UPDATED"
done < "$monlog1"
# disconnect from DB2
db2 terminate
exit 0
______________________________________________________
Following is the extracted data rcbl1.log
Code :
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
6634583444 0 R 902 - J System Exception Occured, please refer to BMW_EXCEPTION_LOG table 2008-09-15-12.52.10.730692
6634583444 0 R 902 - J System Exception Occured, please refer to BMW_EXCEPTION_LOG table 2008-09-15-11.28.36.504163
BEYJY2M66L 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-04.39.59.637770
BEYJY2MZMS 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-03.15.50.294828
BEYJY207BX 0 R 017 B Invalid Fee Amount 2008-09-17-06.39.33.514821
BEYJY207BX 0 R 017 B Invalid Fee Amount 2008-09-17-06.39.33.516753
BEYJY207BX 0 R 017 B Invalid Fee Amount 2008-09-17-06.39.33.514821
BEYJY207BX 0 R 017 B Invalid Fee Amount 2008-09-17-06.39.33.516753
BEYJY207BX 0 R 017 B Invalid Fee Amount 2008-09-17-06.39.33.514821
BEYJY207BX 0 R 017 B Invalid Fee Amount 2008-09-17-06.39.33.516753
BEYEHGBGZV 0 R 003 I No message exists in ZBV_BILL_REJCT_RSN table 2008-08-28-16.31.08.850864
900000010 0 R 031 J Premium amount can not be less than or equal to zero 2008-08-28-13.12.38.757170
BEYJY20X27 0 R 013 B Invalid Transaction Code 2008-09-17-04.49.56.241529
BEYJY20X3V 0 A 888 888 B TEST ASSOCIATED ERROR MESSAGE 2008-09-17-23.01.14.944406 R2P
BEYJY5RV2K 0 F 103 ZU1CD_0001 K VVSCPOS cannot be found for for BP 0200005012 IO X929459861, dates 20080925 2008-09-25-22.01.09.687593
BEYJY5RV2K 0 R 902 - K System Exception Occured, please refer to BMW_EXCEPTION_LOG table 2008-09-26-10.31.21.675572
BEYJYTT8XL 0 R 013 B Invalid Transaction Code 2008-09-04-17.39.08.354862
BEYJYTT8XL 0 R 016 B Invalid Fee Code 2008-09-04-17.39.08.356149
BEYJYTT8XL 0 R 016 B Invalid Fee Code 2008-09-04-17.39.08.358613
BEYJYTT8XL 0 R 016 B Invalid Fee Code 2008-09-04-17.39.08.359875
BEYEHF99F1 0 R 003 B No message exists in ZBV_BILL_REJCT_RSN table 2008-08-28-16.31.06.232450
BEYJY2M85F 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-06.49.33.347725
BEYJY2M7JY 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-05.16.23.029908
BEYJY2PC0K 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-07.02.32.423376
BEYJY3SVMY 0 R 003 E Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-19-09.31.36.462707
BEYJY2MZMS 0 - - - E - - -
BEYJY55XB0 0 R 003 E Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-27-08.47.33.815017
BEYJY2M87C 0 R 003 E Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-06.57.29.689330
BEYJY44F3X 0 F 103 ZU1CD_0001 E VVSCPOS cannot be found for for BP 0200004528 IO 929459574, dates 20080924 20090923 2008-09-24-09.00.53.264824
BEYJY3JNK7 0 F 065 MIMA E Insurance object was not found 2008-09-18-12.20.16.686911
BEYEHE3P7V 0 R 011 K Invalid Company Code 2008-08-26-23.49.12.709837
BEYJY2SMD7 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-08.48.42.547830
BEY3GN4K9H 0 R 003 I Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-29-12.37.27.124697
BEYJY2T375 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-11.58.53.088913
BEY3GPRVWZ 0 F 120 ZU1CD_0001 B Unknown main/sub code for IO 175058183 2008-09-30-23.58.57.605725
BEY3GPRVWY 0 F 120 ZU1CD_0001 B Unknown main/sub code for IO 175058183 2008-10-01-01.22.05.612926
BEY3GN4K7R 0 R 055 I No Financial transaction found in billing 2008-09-29-12.27.18.474721
BEY3GN4K7N 0 R 003 I Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-29-12.27.21.887539
BEYJY54DHX 0 F 009 VVSCDI E : Field RISKTO must be empty 2008-09-26-23.34.27.694991
BEYJY2M85R 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-06.50.14.369153
BEY3GN4K7R 0 R 055 B No Financial transaction found in billing 2008-10-01-08.57.12.949291
BEYJY2M86V 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-06.54.27.133730
BEYJY10819 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-14-15.32.55.469621
BEYJY20X01 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-17-04.20.23.633014
BEY3GPT2B0 0 F 038 >4 B Enter an amount 2008-10-01-09.03.53.443943
BEYEHF96RK 0 R 003 B No message exists in ZBV_BILL_REJCT_RSN table 2008-08-28-16.31.02.352400
BEY3GN4K7Y 0 R 055 B No Financial transaction found in billing 2008-09-29-12.27.23.483298
BEYJY207C7 0 R 017 B Invalid Fee Amount 2008-09-17-06.44.26.071328
BEYJY207C7 0 R 017 B Invalid Fee Amount 2008-09-17-06.44.26.072844
BEYEHFQ54J 0 R 017 B Invalid Fee Amount 2008-09-18-23.14.21.320100
BEYEHFQ54J 0 R 017 B Invalid Fee Amount 2008-09-18-23.14.21.321366
BEYJY2SQD9 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-09.10.30.497066
BEY3GPSNZ5 0 F 038 >4 B Enter an amount 2008-10-01-06.59.48.662127
BEYJY2STNN 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-16-09.14.18.841586
BEY3GN6VS9 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-29-19.06.35.663466
BEY3GN6PTY 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-29-16.58.10.721440
BEYJY5C7L7 0 R 019 B Adjustment code missing 2008-09-24-22.13.16.586532
BEY3GN2NJB 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-29-07.10.54.732583
BEY3GN4GPS 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-29-11.12.20.172209
BEY3GN4K7P 0 R 055 B No Financial transaction found in billing 2008-09-29-12.27.16.808223
BEY3GPKVWP 0 F 038 >4 B Enter an amount 2008-09-30-10.34.39.463024
BEYJY207KF 0 R 017 B Invalid Fee Amount 2008-09-17-06.56.14.229894
BEYJY207KF 0 R 017 B Invalid Fee Amount 2008-09-17-06.56.14.232849
BEY3GPK18Y 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-30-10.38.35.854308
BEY3GPLH06 0 F 038 >4 B Enter an amount 2008-09-30-10.45.16.264626
BEY3GPK18Z 0 F 120 ZU1CD_0001 B Unknown main/sub code for IO 175058120 2008-09-30-10.38.28.377003
BEY3GPLJL8 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-30-10.45.35.567861
BEY3GPMTJV 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-30-13.00.02.810329
BEY3GPMTKZ 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-30-13.11.02.243884
BEY3GPMTQW 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-30-13.36.42.646563
BEY3GPMTRH 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-30-13.39.29.580386
BEY3GPMTSW 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-30-13.46.45.515572
BEY3GPMYD7 0 F 120 ZU1CD_0001 B Unknown main/sub code for IO 175058158 2008-09-30-15.08.44.519885
BEY3GPMYD8 0 F 120 ZU1CD_0001 B Unknown main/sub code for IO 175058158 2008-09-30-15.08.44.525847
BEY3GPMYFG 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-30-15.13.11.329547
BEY3GPMYFM 0 R 024 B Effective date does not match billing term begin date 2008-09-30-15.13.52.546579
BEY3GPMYFN 0 R 055 B No Financial transaction found in billing 2008-09-30-15.13.52.550473
BEY3GN6VSY 0 F 056 B Policy Not Found in Billing 2008-09-30-17.19.10.610556
BEY3GPM1P1 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-30-17.28.19.687689
BEY3GPVMXJ 0 F 125 VVSCDI B 200005749/X175058232/10000105709: No payment plan key available on 00000000 for insurance object 2008-10-01-12.15.58.680987
BEY3GPSNR5 0 F 038 >4 B Enter an amount 2008-10-01-06.12.23.769876
BEY3GPSNRR 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-10-01-06.09.26.356436
BEY3GPSNN5 0 F 038 >4 B Enter an amount 2008-10-01-05.55.43.744213
BEY3GPSM6X 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-10-01-05.46.28.469604
BEY3GPSM6E 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-10-01-05.43.54.742278
BEY3GPSM3V 0 F 038 >4 B Enter an amount 2008-10-01-05.35.27.918090
BEY3GPSM3T 0 F 038 >4 B Enter an amount 2008-10-01-05.34.47.034630
BEY3GPSM3E 0 F 038 >4 B Enter an amount 2008-10-01-05.29.35.545858
BEY3GPM4J8 0 F 120 ZU1CD_0001 B Unknown main/sub code 2700 0100 for IO 175058173 2008-09-30-23.42.53.058623
BEY3GPM20W 0 F 002 ZSOA B Provider Runtime Exception-System Not Available 2008-09-30-17.30.04.528810
BEY3GPMTYT 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-30-14.09.08.149372
BEY3GPMTT6 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-30-13.51.28.303094
BEY3GPMTJY 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-30-13.02.46.854286
BEY3GPH9XN 0 F 038 >4 B Enter an amount 2008-09-30-10.13.47.049998
BEY3GQDL5K 0 F 038 >4 E Enter an amount 2008-10-03-13.04.52.678478
BEY3GQDL5K 0 F 001 ZSOA E Missing Mandatory Data Elements 2008-10-03-13.37.39.878343
BEY3GQDEL0 0 F 038 >4 E Enter an amount 2008-10-03-11.55.16.833647
BEY3GQDC33 0 F 038 >4 E Enter an amount 2008-10-03-11.41.31.346301
BEY3GP86T2 0 F 038 >4 E Enter an amount 2008-10-03-08.25.31.638912
BEY3GP7YD2 0 F 038 >4 E Enter an amount 2008-10-03-05.25.25.252836
BEY3GN4K7V 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-29-12.27.31.766603
BEY3GN4K7V 0 R 003 I Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-10-03-23.34.41.450724
BEY3GN2RGM 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-29-07.26.02.491998
BEY3GN2RHL 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-09-29-07.26.02.495999
BEY3GN31PK 0 F 125 VVSCDI B 200005464/X175057998/10000105359: No payment plan key available on 00000000 for insurance object 2008-10-01-10.16.15.080731
BEY3GQERH4 0 R 003 E Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-10-03-17.33.22.461623
BEY3GQFHCB 0 F 038 >4 E Enter an amount 2008-10-03-18.03.51.233049
BEY3GQNP20 0 R 058 E Main and Sub not found in Billing 2008-10-06-06.11.31.261536
BEY3GPT5TC 0 R 003 E Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-10-01-09.28.44.790961
BEY3GPT53R 0 F 038 >4 E Enter an amount 2008-10-01-09.32.09.777162
BEY3GPT53R 0 F 001 ZSOA I Missing Mandatory Data Elements 2008-10-06-07.15.01.100380
BEY3GPT6BK 0 F 038 >4 E Enter an amount 2008-10-01-09.33.59.610288
BEY3GQDEL0 0 F 001 ZSOA E Missing Mandatory Data Elements 2008-10-03-13.44.46.315471
BEY3GPT4TM 0 F 120 ZU1CD_0001 E Unknown main/sub code for IO 175058219 2008-10-01-09.18.59.293013
BEY3GPT4TN 0 F 120 ZU1CD_0001 B Unknown main/sub code for IO 175058219 2008-10-01-09.18.59.298114
BEYJY3JM22 0 F 065 MIMA E Insurance object was not found 2008-09-18-10.58.14.279999
BEYJY3JM22 0 F 001 ZSOA I Missing Mandatory Data Elements 2008-10-06-07.59.01.687955
BEYJY5WLGD 0 F 120 ZU1CD_0001 I Unknown main/sub code for IO 175057664 2008-09-26-07.31.58.382501
BEYJY50RBB 0 F 120 ZU1CD_0001 I Unknown main/sub code for IO 175057716 2008-09-26-09.40.05.240307
BEYJY5C02N 0 F 120 ZU1CD_0001 I Unknown main/sub code for IO 175057373 2008-09-24-18.51.11.631367
BEYJY5WLGD 0 R 058 I Main and Sub not found in Billing 2008-10-07-23.18.15.846557
BEYJY2M7JY 0 R 902 - I System Exception Occured, please refer to BMW_EXCEPTION_LOG table 2008-09-26-23.36.43.747284
BEYJY5WH2S 0 R 019 I Adjustment code missing 2008-09-26-04.32.02.011803
BEYJY5WH2S 0 R 057 I Current Term Policy status in- force in billing 2008-09-26-04.32.02.013628
BEY3GRCEMV 0 F 002 ZSOA I Provider Runtime Exception-System Not Available 2008-10-07-17.20.50.834123
BEYJY5WLJF 0 F 120 ZU1CD_0001 I Unknown main/sub code for IO 175057681 2008-09-26-07.55.03.601961
BEYJY5WLJP 0 F 120 ZU1CD_0001 I Unknown main/sub code for IO 175057682 2008-09-26-07.57.27.525001
BEYJY5Y2B8 0 F 120 ZU1CD_0001 I Unknown main/sub code for IO 175057689 2008-09-26-08.24.24.732156
BEYJY5Y5SB 0 F 120 ZU1CD_0001 I Unknown main/sub code for IO 175057691 2008-09-26-08.30.25.232782
BEYJY5WLJF 0 R 058 I Main and Sub not found in Billing 2008-10-08-17.07.42.080108
BEYJY5WLJP 0 R 058 I Main and Sub not found in Billing 2008-10-08-17.09.26.549362
BEYJY5Y2B8 0 R 058 I Main and Sub not found in Billing 2008-10-08-17.10.28.028321
BEYJY5Y5SB 0 R 058 I Main and Sub not found in Billing 2008-10-08-17.11.20.657189
BEY3GPVET4 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-10-01-10.39.34.951855
BEYJY5WLJP 0 F 002 ZSOA I Provider Runtime Exception-System Not Available 2008-10-08-17.19.18.620133
BEYJY5Y2B8 0 F 002 ZSOA I Provider Runtime Exception-System Not Available 2008-10-08-17.20.07.415193
BEYJY5Y5SB 0 F 002 ZSOA I Provider Runtime Exception-System Not Available 2008-10-08-17.20.36.331224
BEY3GPT80F 0 R 003 B Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG 2008-10-01-10.00.21.156540
Here the problem that I am facing is deletion is not happen because the field MSG_TEXT's value will be 73 bytes long. (Eg. 'Policy / Contract Account Number are pending in the ZB_MASTER_DATA_LOG'). If this is the case, the field MSG_TEXT is separated by spaces, the moving of the data to the corresponding field is not happen correctly.
I have tried by doing like this
var1 = <get the tracking_num from position 1-12 in the text file>
var2 = <get the tracking_num from position 14-32 in the text file>
var3 = <get the tracking_num from position 185-210 in the text file>
I don't know the syntax for that.
or else is there any other way to perform this task
Can anyone help me to achieve this task.
Krishnakanth