Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Comparing 2 files

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
sivaprasad.gadhi

New User


Joined: 21 Aug 2012
Posts: 17
Location: india

PostPosted: Tue Apr 23, 2013 2:35 pm    Post subject: Comparing 2 files
Reply with quote

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.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Apr 23, 2013 3:01 pm    Post subject: Reply to: Comparing 2 files
Reply with quote

Do you have access to both DFSORT and SyncSort? Which is the one used by default?
Back to top
View user's profile Send private message
sivaprasad.gadhi

New User


Joined: 21 Aug 2012
Posts: 17
Location: india

PostPosted: Tue Apr 23, 2013 3:05 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Apr 23, 2013 3:20 pm    Post subject: Reply to: Comparing 2 files
Reply with quote

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.
Back to top
View user's profile Send private message
sivaprasad.gadhi

New User


Joined: 21 Aug 2012
Posts: 17
Location: india

PostPosted: Tue Apr 23, 2013 4:21 pm    Post subject: Reply to: Comparing 2 files
Reply with quote

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.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Apr 23, 2013 5:48 pm    Post subject: Reply to: Comparing 2 files
Reply with quote

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.
Back to top
View user's profile Send private message
sivaprasad.gadhi

New User


Joined: 21 Aug 2012
Posts: 17
Location: india

PostPosted: Tue Apr 23, 2013 9:36 pm    Post subject: Reply to: Comparing 2 files
Reply with quote

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.

Code:
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  INPUT FILE1 (FB/112)
//IN2 DD DSN=...  INPUT FILE2 (FB/150)
//OUT DD DSN=...  OUTPUT FILE (FB/112)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(3,15,A)
  JOINKEYS F2=IN2,FIELDS=(1,15,A)
  JOIN UNPAIRED,F1,ONLY
  REFORMAT FIELDS=(F1:1,112)
  SORT FIELDS=COPY
 
/*
//JNF2CNTL DD *
  INREC BUILD=(1:109,7,8:70,3,11:73,5)
/*

Please let me know if the above code works for my requirement.

Thanks for your help

Regards
siva

Code'd
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Apr 23, 2013 9:45 pm    Post subject: Reply to: Comparing 2 files
Reply with quote

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.
Back to top
View user's profile Send private message
sivaprasad.gadhi

New User


Joined: 21 Aug 2012
Posts: 17
Location: india

PostPosted: Tue Apr 23, 2013 11:02 pm    Post subject: Reply to: Comparing 2 files
Reply with quote

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
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Apr 24, 2013 12:40 am    Post subject: Reply to: Comparing 2 files
Reply with quote

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.
Back to top
View user's profile Send private message
sivaprasad.gadhi

New User


Joined: 21 Aug 2012
Posts: 17
Location: india

PostPosted: Wed Apr 24, 2013 6:03 pm    Post subject: Reply to: Comparing 2 files
Reply with quote

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
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Apr 24, 2013 10:23 pm    Post subject: Reply to: Comparing 2 files
Reply with quote

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.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Apr 24, 2013 11:10 pm    Post subject: Reply to: Comparing 2 files
Reply with quote

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.
Back to top
View user's profile Send private message
sivaprasad.gadhi

New User


Joined: 21 Aug 2012
Posts: 17
Location: india

PostPosted: Fri Apr 26, 2013 4:20 pm    Post subject: Reply to: Comparing 2 files
Reply with quote

Hi Bill ,

Thanks for your help.

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

Code:
//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)
  OPTION COPY
  OUTREC IFOUTLEN=112,
  IFTHEN(WHEN=INIT,OVERLAY=(113:SEQNUM,5,PD)),
  IFTHEN=(WHEN=(1,3,BI,EQ,X'FFFFFF'),
    OVERLAY=(108:113,5,PD,SUB,+2,PD,LENGTH=5))
/*


Regards
siva

Code'd
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Apr 26, 2013 4:36 pm    Post subject: Reply to: Comparing 2 files
Reply with quote

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?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us