Comparing 2 files

sivaprasad.gadhi · New User Joined: 21 Aug 2012 Posts: 17 Location: india

Hi ,

I have 2 files below are the layouts

File-1 : Record length -112

Header
detail
trailer

Key position: is combination of (Staring and length of positions given below)
Location number (3,7)
division number (8,3)
item number (11,5)

File-2 : Record length - 150
it don't have any header and trailer
key position:

Location number (109,7)
division number (70,3)
item number (73,5)

i need to compare these 2 files (keys) and write record to output file only from file-1.
if the key from file-1 matches to file-2 then i cannot write that record to output.Only the record from file-1 which don't have matching key in file-2 will be written to output file.Also i need header and trailer records from fil-1 to output file and also i need updation of record count in trailer record ar position 109 to 112 ( 3bytes) of packed decimal fields.

Could you please let me know whether i can do it using syncsort or DFsort utitlity ? please help me.

Bill Woodger · Posted: Tue Apr 23, 2013 3:01 pm

Do you have access to both DFSORT and SyncSort? Which is the one used by default?

sivaprasad.gadhi · New User Joined: 21 Aug 2012 Posts: 17 Location: india

I have access for both and by default it is DFSORT.

Also i have forgotten one more requirement and as follows.

File-1 has duplicates and file-2 don't have duplicates.

Thanks
siva

Bill Woodger · Posted: Tue Apr 23, 2013 3:20 pm

OK, a simple JOINKEYS.

For your file 2, use JNF2CNTL to BUILD just the key fields, in the same order as file 1.

Use JOIN UNPAIRED,F1,ONLY, which will get you all the unmatched records from file 1 (including the header and trailer).

Use OUTFIL to amend the trailer count, if you are up-to-date with your DFSORT, you can look at IFTRAIL.

sivaprasad.gadhi · New User Joined: 21 Aug 2012 Posts: 17 Location: india

Thanks bill. i have never used this join option in sort.It would be great help if you provide some sample code which will suit the requirement.

Bill Woodger · Posted: Tue Apr 23, 2013 5:48 pm

Here's an example. There are lots here if you search. Look at the example and my suggestions, give it a go. If you get stuck, show us what you've done and what problem you encountered.

sivaprasad.gadhi · New User Joined: 21 Aug 2012 Posts: 17 Location: india

Hi Bill ,

Thanks for your suggestion.

Unfortunately my RACF ID got invoked and possibly i may get it bytomorrow so till that time i cannot test my code.

based on your suggestions i have just prepared draft code using sort utility.

Bill Woodger · Posted: Tue Apr 23, 2013 9:45 pm

You don't need the columns on the BUILD, but other than that it looks OK. I've only looked, and not hard, and not tested.

Doesn't it feel better to get there yourself than to just take something and not know what it does? When you can, test, and make sure you understand it.

sivaprasad.gadhi · New User Joined: 21 Aug 2012 Posts: 17 Location: india

Hi Bill ,

Thanks for review.

I have gone through the DFSORT and understood the code atleast i have written above.

As we are using only key fields from file2 for matching only so i have used Build statement and have taken only 3 fields.

Please let me kmow if i am wrong,

Thanks for your help
Regards
Siva

Skolusu · Posted: Wed Apr 24, 2013 12:40 am

sivaprasad.gadhi,

If you need the original header and trailer you need to retain it using JNF1CNTL. You can use BUILD on JNF2CNTL to have only fields that you are going to match.

And you need to use IFTRAIL on the main task to update the trailer with the updated number of records.

sivaprasad.gadhi · New User Joined: 21 Aug 2012 Posts: 17 Location: india

Hi ,

Thanks Bill and kolusu.

In our shop if i give PGM=ICETOOL also ,it is invoking sync sort.

below si my code working for requirement.

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=SPR7.OMRSBRDG.TORIM,DISP=SHR
//SORTJNF2 DD DSN=AHAROO0.SEARSREF.DIVITEM5.LOCN.ACT,DISP=SHR
//SORTOUT DD DSN=@#OSM02.OMRSBRDG.TORIM2,DISP=SHR
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(3,7,A,10,3,A,13,5,A)
JOINKEYS FILE=F2,FIELDS=(109,7,A,70,3,A,73,5,A)
JOIN UNPAIRED,F1,ONLY
REFORMAT FIELDS=(F1:1,112)
SORT FIELDS=COPY
/*

Now i am working on updation of trailer count.Does IFTRAIL parameter works for sync sort? If not can you please suggest alternative?

Please let me know if you have any queries or suggestions.

Regards
siva

Bill Woodger · Posted: Wed Apr 24, 2013 10:23 pm

No, I don't think SyncSort has IFTRAIL.

Perhaps append a sequence number to each record. Use IFTHEN=(WHEN=(trailerstart,trailerlength,CH,EQ,C'traileridentification') and OVERLAY the sequence number over the trailer count. You'll need to drop the extended sequence number. Since you have an IFTHEN, you could use IFOUTLEN=122.

Bill Woodger · Posted: Wed Apr 24, 2013 11:10 pm

If your trailer is just identifier plus count (or other "constant" data) you could OMIT it in the JNF1CNTL (so you have an up-to-date SyncSort (1.4.x)?) and use OUTFIL, TRAILER1 with REMOVECC and create your trailer with the TRAILER1.

sivaprasad.gadhi · New User Joined: 21 Aug 2012 Posts: 17 Location: india

Hi Bill ,

Thanks for your help.

I just executed the following code and it looks will work for my requirement.

Bill Woodger · Posted: Fri Apr 26, 2013 4:36 pm

OK.

Please use the Code tags.

I see you can't use the JNFnCNTL. You might want to ask SyncSort about that for the future.

Are your input files already in key order?