MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Comparing two files and update one for specific records only

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Wed Jul 29, 2020 4:15 am    Post subject: Comparing two files and update one for specific records only Reply with quote
Hi all,

I am trying to match two input files F1, F2 based on 1 Key and update two fields from F1 in F2 at certain positions for Key matched records with other field matching condition in F2.

F1 format: (Not sorted on Key, but unique Key values and Key can be blank too)
Code:
----+----1----+----2----+----3----+----4----+----5----+----6---
PNX      ,        358521,  396943503,    5432196   , 0         
PNX      ,           LPL,           ,              ,           
PNX      ,        000003,  000000003,              ,           
PNX      ,        000007,  000000007,              ,           
PNX      ,        000008,  000000008,              ,           
PNX      ,        363842,  589966622,              , 16964991 


F1- Key position 28,9
Field1 position 42,10
Field2 position 52,10

F2 format:
(Not sorted on Key as there can be other records to in between whose position should not be disturbed)
Total length 1:300
Key position: 36,9 – Duplicate records can exist need to update all
Condition: Match Keys on records starting with ‘C1305’ from position 1 and update only if 58,2 in ('H1','RM','PA','RA','WA','IA','TM','HA','BG','G1','RI'). Remaining records Keep as it is.
Updation: Field1 from file 1 should be filled in position 186-195 and Field2 from file 1 should be filled in position 197- 206. Remaining fields should be copied as it is from File 2.
Final output file length same as F2 300.

I tried below ICETOOL code but didn’t work.

Code:
//STEP0100 EXEC PGM=ICETOOL                                           
//DFSMSG   DD SYSOUT=*                                                 
//TOOLMSG  DD SYSOUT=*                                                 
//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(100,50),RLSE)                     
//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(100,50),RLSE)                     
//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(100,50),RLSE)                     
//SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(100,50),RLSE)                     
//SORTWK05 DD UNIT=SYSDA,SPACE=(CYL,(100,50),RLSE)                     
//IN1      DD DSN=T2.S50.SCAN.IRS.OUTPUT.FB,                           
//            DISP=SHR                                                 
//IN2      DD DSN=T2.SJE.PJETS30.PVFP.BKUP.DT072120.C3105,             
//            DISP=SHR                                                 
//T1       DD DSN=&T1,DISP=(MOD,PASS),SPACE=(CYL,(100,100),RLSE)       
//OUT      DD DSN=T2.SJE.PJETS30.PVFP.BKUP.DT072120.UPDATED,           
//            DISP=(NEW,CATLG,DELETE),                                 
//         SPACE=(CYL,(100,100),RLSE)                                 
//TOOLIN   DD *                                                       
  COPY FROM(IN1) TO(T1) USING(CTL1)                                   
  COPY FROM(IN2) TO(T1) USING(CTL2)                                   
  SPLICE FROM(T1) TO(OUT) ON(301,9,CH) -                             
  WITH(1,329) WITHALL USING(CTL3)                                     
                                                                     
//CTL1CNTL DD *                                                       
  OUTREC BUILD=(301:28,9,                                             
                310:42,10,                                           
                320:54,10)                                           
//CTL2CNTL DD *                                                       
  OUTREC OVERLAY=(301:36,9,                                           
                  310:20X)                                           
//CTL3CNTL DD *                                                       
  OPTION EQUALS                                                       
  OUTFIL IFTHEN=(WHEN=(1,5,CH,EQ,C'1305',AND,(58,2,CH,EQ,C'H1',OR,   
                                              58,2,CH,EQ,C'RM',OR,   
                                              58,2,CH,EQ,C'PA',OR,   
                                              58,2,CH,EQ,C'RA',OR,   
                                              58,2,CH,EQ,C'WA',OR,   
                                              58,2,CH,EQ,C'IA',OR,   
                                              58,2,CH,EQ,C'TM',OR,   
                                              58,2,CH,EQ,C'HA',OR,   
                                              58,2,CH,EQ,C'BG',OR,   
                                              58,2,CH,EQ,C'G1',OR,   
                                              58,2,CH,EQ,C'RI')),     
          OVERLAY=(1,185,310,10,196,1,320,10,207,94))                 
/*


Can somebody please help with corrections in my code to achieve desired result.
_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Wed Jul 29, 2020 4:20 am    Post subject: Reply with quote
Also tried using JOINKEYS of SORT as below

Code:
//STEP0100 EXEC PGM=SORT                                         
//DFSMSG   DD SYSOUT=*                                           
//TOOLMSG  DD SYSOUT=*                                           
//SORTJNF2 DD DSN=T2.S50.SCAN.IRS.OUTPUT.FB,                     
//            DISP=SHR                                           
//SORTJNF1 DD DSN=T2.SJE.PJETS30.PVFP.BKUP.DT072120,             
//            DISP=SHR                                           
//SORTOUT  DD DSN=T2.SJE.PJETS30.PVFP.BKUP.DT072120.UPDATED,     
//            DISP=(NEW,CATLG,DELETE),                           
//         SPACE=(CYL,(100,100),RLSE)                           
//SYSPRINT DD SYSOUT=*                                           
//SYSOUT   DD SYSOUT=*                                           
//SYSIN    DD *                                                 
 JOINKEYS FILE=F2,FIELDS=(28,9,A)                               
 JOINKEYS FILE=F1,FIELDS=(36,9,A)                               
 JOIN UNPAIRED,F1                                               
 REFORMAT FIELDS=(F1:1,300,F2:42,22),FILL=X'FF'                 
 SORT FIELDS=COPY                                               
 OUTREC IFTHEN=(WHEN=(323,1,BI,EQ,X'FF'),                       
        BUILD=(1,300)),                                             
        IFTHEN=(WHEN=((323,1,BI,NE,X'FF'),AND,                       
                     (1,5,CH,EQ,C'1305'),AND,(58,2,CH,EQ,C'H1',OR,   
                                              58,2,CH,EQ,C'RM',OR,   
                                              58,2,CH,EQ,C'PA',OR,   
                                              58,2,CH,EQ,C'RA',OR,   
                                              58,2,CH,EQ,C'WA',OR,   
                                              58,2,CH,EQ,C'IA',OR,   
                                              58,2,CH,EQ,C'TM',OR,   
                                              58,2,CH,EQ,C'HA',OR,   
                                              58,2,CH,EQ,C'BG',OR,   
                                              58,2,CH,EQ,C'G1',OR,   
                                              58,2,CH,EQ,C'RI')),   
        BUILD=(1,185,310,10,196,1,320,10,207,94))                   
 END


But getting error "FIELD BEYOND MAXIMUM RECORD LENGTH"
_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Wed Jul 29, 2020 9:10 am    Post subject: Reply with quote
psmadhusudhan,

The splice is an older technique and will not handle conditions for a many to many match. So I am not going to fix it.

As for the joinkeys you are getting the " "FIELD BEYOND MAXIMUM RECORD LENGTH" due to

1. The joined record is ONLY 320 bytes. but you are referring to fields at position 323 for a length of 1 and position 320 for a length of 10.

Either way use the following DFSORT JCL which will give you the desired results

It is ideal to pass the file with most data as SORTJNF1. In the below example file with LRECL 300 is to be as passed to the ddname INA
Code:

//STEP0100 EXEC PGM=SORT                     
//SYSOUT   DD SYSOUT=*                       
//INA      DD DISP=SHR,
//            DSN=T2.S50.SCAN.IRS.OUTPUT.FB             
//INB      DD DISP=SHR,
//            DSN=T2.SJE.PJETS30.PVFP.BKUP.DT072120.C3105
//SORTOUT  DD DSN=T2.SJE.PJETS30.PVFP.BKUP.DT072120.UPDATED,     
//            DISP=(NEW,CATLG,DELETE),                           
//            SPACE=(CYL,(100,100),RLSE)         
//SYSIN    DD *                               
  OPTION COPY                                 
  JOINKEYS F1=INA,FIELDS=(036,09,A,301,1,A)   
  JOINKEYS F2=INB,FIELDS=(001,09,A,030,1,A)   
  JOIN UNPAIRED,F1                           
  REFORMAT FIELDS=(F1:01,300,                 
                   F2:10,020,                 
                   ?)                         
  INREC IFOUTLEN=300,                         
  IFTHEN=(WHEN=(321,1,CH,EQ,C'B'),           
       OVERLAY=(186:301,10,                   
                197:311,10))                 
/*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Wed Jul 29, 2020 10:06 am    Post subject: Reply with quote
Thank Kolusu for quick reply.

even though I had doubt in field positions in your code I straight away tried it and getting below error

ICE414A 0 INA (F1) KEY FIELD END AT 301 IS BEYOND LENGTH OF 300

Please note I have reversed file names as INB is of 300 length in your jcl and you advised to you bigger file as INA.

Also can you help to read my Key and field positions once again, it seems some mismatch in jcl.
_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Wed Jul 29, 2020 10:48 am    Post subject: Reply with quote
psmadhusudhan wrote:
Thank Kolusu for quick reply.

even though I had doubt in field positions in your code I straight away tried it and getting below error

ICE414A 0 INA (F1) KEY FIELD END AT 301 IS BEYOND LENGTH OF 300

Please note I have reversed file names as INB is of 300 length in your jcl and you advised to you bigger file as INA.

Also can you help to read my Key and field positions once again, it seems some mismatch in jcl.


psmadhusudhan,

In my earlier post, I missed posting the JNF1CNTL and JNF2CNTL where I modified the key fields. Here is the complete version

Code:

//STEP0100 EXEC PGM=SORT                     
//SYSOUT   DD SYSOUT=*                       
//INA      DD DISP=SHR,
//            DSN=T2.S50.SCAN.IRS.OUTPUT.FB             
//INB      DD DISP=SHR,
//            DSN=T2.SJE.PJETS30.PVFP.BKUP.DT072120.C3105
//SORTOUT  DD DSN=T2.SJE.PJETS30.PVFP.BKUP.DT072120.UPDATED,     
//            DISP=(NEW,CATLG,DELETE),                           
//            SPACE=(CYL,(100,100),RLSE)         
//SYSIN    DD *                               
  OPTION COPY                                 
  JOINKEYS F1=INA,FIELDS=(036,09,A,301,1,A)   
  JOINKEYS F2=INB,FIELDS=(001,09,A,030,1,A)   
  JOIN UNPAIRED,F1                           
  REFORMAT FIELDS=(F1:01,300,                 
                   F2:10,020,                 
                   ?)                         
  INREC IFOUTLEN=300,                         
  IFTHEN=(WHEN=(321,1,CH,EQ,C'B'),           
       OVERLAY=(186:301,10,                   
                197:311,10))                 
/*                                                               
//JNF1CNTL DD *                                                 
  INREC IFTHEN=(WHEN=(1,5,CH,EQ,C'C1305',AND,                   
                58,2,SS,EQ,C'H1,RM,PA,RA,WA,IA,TM,HA,BG,G1,RI'),
             OVERLAY=(301:C'M'))                                 
/*                                                               
//JNF2CNTL DD *                                                 
  INREC BUILD=(28,09,                                           
               42,10,                                           
               52,10,                                           
               C'M')                                             
/*                             

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Thu Jul 30, 2020 1:58 am    Post subject: Reply with quote
Thanks kolusu for full jcl.

Sort job ran fine. Matching and updation occured correctly.
But one issue is there. All C1305 records came at the end of file.

For example before run:
Code:
C1301
C1302
C1302
C1303
C1303
C1305
C1306
C1309
C1310
C1301
C1302
C1302
C1303
C1305
C1306
C1309
C1310


After run:
Code:
C1302
C1302
C1302
C1302
C1305
C1305
C1305
C1305


Also remaining records also got displaced.
There are header and Trailer records too which got displaced.
Is it possible to retain records positions as the same in input file.
_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Thu Jul 30, 2020 8:17 am    Post subject: Reply with quote
psmadhusudhan wrote:
Thanks kolusu for full jcl.

Sort job ran fine. Matching and updation occured correctly.
But one issue is there. All C1305 records came at the end of file.

Also remaining records also got displaced.
There are header and Trailer records too which got displaced.
Is it possible to retain records positions as the same in input file.


psmadhusudhan,

If you need to retain the original order of the records, then you need to add a sequence number and then sort on it. Here are the updated control cards

Code:

//SYSIN    DD *                                                 
  JOINKEYS F1=INA,FIELDS=(036,09,A,301,1,A)                     
  JOINKEYS F2=INB,FIELDS=(001,09,A,030,1,A)                     
  JOIN UNPAIRED,F1                                               
  REFORMAT FIELDS=(F1:001,300,                                   
                      302,008,                                   
                   F2:010,020,                                   
                   ?)                                           
                                                                 
  SORT FIELDS=(301,8,BI,A)                                       
                                                                 
  OUTREC IFOUTLEN=300,                                           
  IFTHEN=(WHEN=(329,1,CH,EQ,C'B'),                               
       OVERLAY=(186:309,10,                                     
                197:319,10))                                     
/*                                                               
//JNF1CNTL DD *                                                 
  INREC IFTHEN=(WHEN=INIT,                                       
             OVERLAY=(302:SEQNUM,8,BI)),                         
        IFTHEN=(WHEN=(1,5,CH,EQ,C'C1305',AND,                   
                58,2,SS,EQ,C'H1,RM,PA,RA,WA,IA,TM,HA,BG,G1,RI'),
             OVERLAY=(301:C'M'))                                 
/*                                                               
//JNF2CNTL DD *                                                 
  INREC BUILD=(28,09,                                           
               42,10,                                           
               52,10,                                           
               C'M')                                             
/*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Thu Jul 30, 2020 9:18 am    Post subject: Reply with quote
Thanks Kolusu. It worked awesome. Very Happy
_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Sat Sep 12, 2020 3:54 am    Post subject: Reply with quote
Hi kolusu,

my input file F1 got modified with new positions and one additional field.

F1- Key position 11,9
Field1 position 192,9
Field2 position 230,10
Field3(new) position 219,10

F2 file remains same.

I have modified control cards to above requirements as below.
Code:
//SYSIN    DD *                                 
  JOINKEYS F1=INA,FIELDS=(036,09,A,301,1,A)     
  JOINKEYS F2=INB,FIELDS=(001,09,A,039,1,A)     
  JOIN UNPAIRED,F1                             
  REFORMAT FIELDS=(F1:001,300,                 
                      302,008,                 
                   F2:010,029,                 
                   ?)                           
                                               
  SORT FIELDS=(301,8,BI,A)                     
                                               
  OUTREC IFOUTLEN=300,                         
  IFTHEN=(WHEN=(338,1,CH,EQ,C'B'),             
       OVERLAY=(186:309,09,                     
                197:318,10,                     
                207:328,10))                   
/*                                             
//JNF1CNTL DD *                                 
  INREC IFTHEN=(WHEN=INIT,                     
             OVERLAY=(302:SEQNUM,8,BI)),                         
        IFTHEN=(WHEN=(1,5,CH,EQ,C'C1305',AND,                   
                58,2,SS,EQ,C'H1,RM,PA,RA,WA,IA,TM,HA,BG,G1,RI'),
             OVERLAY=(301:C'M'))                                 
/*                                                               
//JNF2CNTL DD *                                                 
  INREC BUILD=(16,09,                                           
              192,09,                                           
              230,10,                                           
              219,10,                                           
               C'M')                                             
/*


job is running fine, but records are not getting matched and updated. Can you please advise where I am going wrong.
_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Sat Sep 12, 2020 10:58 am    Post subject: Reply with quote
psmadhusudhan wrote:
Hi kolusu,

my input file F1 got modified with new positions and one additional field.

F1- Key position 11,9
Code:
                                                       
//JNF2CNTL DD *                                                 
  INREC BUILD=(16,09,                                           
              192,09,                                           
              230,10,                                           
              219,10,                                           
               C'M')                                             
/*


job is running fine, but records are not getting matched and updated. Can you please advise where I am going wrong.


psmadhusudhan,

If your key position is at 11, why does JNF2CNTL have position 16 in JNF2CNTL?

which position is correct 11 or 16?
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Mon Sep 14, 2020 1:11 am    Post subject: Reply with quote
Sorry my typo mistake, Key starts from position 16 as mentioned in control card
_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Mon Sep 14, 2020 9:58 am    Post subject: Reply with quote
psmadhusudhan wrote:
Sorry my typo mistake, Key starts from position 16 as mentioned in control card


psmadhusudhan,

1. Did you check if the input file is a VB/FBA/VBA file? If so you need to account for RDW/carriage control characters

2. Did you realize that JNF1 file is specifically looking for 'C1305' in positions 1 thru 5 and a 2 byte values at position 58? Is that still valid?

3. Did you do a manual verification of couple of keys that you think should have matched?

4. You can extract the keys and see if they match visually.(only extracted first 500 keys)

something like this

Code:

//STEP0100 EXEC PGM=SORT 
//SYSOUT   DD SYSOUT=*   
//SORTIN   DD DISP=SHR,DSN=T2.S50.SCAN.IRS.OUTPUT.FB
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *
 OPTION COPY.STOPAFT=500
 INCLUDE COND=(01,5,CH,EQ,C'C1305',AND,                   
               58,2,SS,EQ,C'H1,RM,PA,RA,WA,IA,TM,HA,BG,G1,RI')

 INREC BUILD=(36,09) 
/*


and in your new file
Code:

//STEP0200 EXEC PGM=SORT 
//SYSOUT   DD SYSOUT=*   
//SORTIN   DD DISP=SHR,DSN=Your new file
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *
 OPTION COPY.STOPAFT=500
 INREC BUILD=(16,9)
/*



Now match these 2 contents and see if they match.
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Tue Sep 15, 2020 6:23 am    Post subject: Reply with quote
Quote:
1. Did you check if the input file is a VB/FBA/VBA file? If so you need to account for RDW/carriage control characters


The file format is FBA. Thanks for pointing it out. I have copied file to another FB file and above control cards are working fine and getting matching result.

Thank you Kolusu.
_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Tue Sep 15, 2020 9:57 am    Post subject: Reply with quote
psmadhusudhan wrote:

The file format is FBA. Thanks for pointing it out. I have copied file to another FB file and above control cards are working fine and getting matching result.

Thank you Kolusu.



psmadhusudhan,

why do you need to copy it to another file? If the file has a RECFM of FBA, then all you need to do is adjust the positions by 1 byte.

Code:

//JNF2CNTL DD *                                                 
  INREC BUILD=(017,09,                                           
               193,09,                                           
               231,10,                                           
               220,10,                                           
               C'M')                                             
/*   

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
psmadhusudhan
Beginner


Joined: 28 Nov 2006
Posts: 143
Topics: 48
PostPosted: Tue Sep 15, 2020 6:46 pm    Post subject: Reply with quote
Yes Kolusu,I just want to convey that above control cards worked fine when file format is FB and there was no issue in positions.
I have adjusted 1 byte for FBA too and ran it, it worked fine.

Thank you for taking time and helping me.
_________________
Thanks
Madhu Sudhan
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group