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
 

 

Compare 2 files and extract records between header & tra

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
rz061m

New User


Joined: 03 Mar 2006
Posts: 48
Location: Chennai

PostPosted: Tue Aug 20, 2013 10:55 pm    Post subject: Compare 2 files and extract records between header & tra
Reply with quote

I have 2 files like below. I need an output file that would give me all detail records including header & footer from the second file only if some of the detail records were missing in the first file. The file sizes are FB 80 bytes & fields to compare are between 5 to 20.


I/P File 1:
-------------
010|379339 | |
020|379339 |A2B |
020|379339 |A2C |
030|379339 | |
010|444333 | |
020|444333 |A2B |
020|444333 |A2C |
030|444333 | |

I/P File 2:
------------
010|379339 | |
020|379339 |A2B |
020|379339 |A2C |
020|379339 |A2D |
020|379339 |A2E |
030|379339 | |
010|444333 | |
020|444333 |A2B |
020|444333 |A2C |
030|444333 | |


O/P File:
-----------
010|379339 | |
020|379339 |A2B |
020|379339 |A2C |
020|379339 |A2D |
020|379339 |A2E |
030|379339 | |

I tried 2 file comparison to extract the missing records from second file but need logic to extract all the details including the corresponding header & footer.
Back to top
View user's profile Send private message

mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Aug 21, 2013 11:19 am    Post subject:
Reply with quote

Here is my understanding. Please correct me if I'm wrong?
1. Records Starting with 010 is the Header.
2. Records Starting with 020 is the Data.
3. Records Starting with 030 is the Trailer.

Now you have group of records in both the Input files and you want only those group of records from 2nd Input file, which does not eaxctly matches with First Input File.

For ex:
Code:
010|379339 | |
020|379339 |A2B |
020|379339 |A2C |
030|379339 | |
and
Code:
010|379339 | |
020|379339 |A2B |
020|379339 |A2C |
020|379339 |A2D |
020|379339 |A2E |
030|379339 | |

do not match so you want the 2nd set of records in Output.

Code:
010|444333 | |
020|444333 |A2B |
020|444333 |A2C |
030|444333 | |
and
Code:
010|444333 | |
020|444333 |A2B |
020|444333 |A2C |
030|444333 | |

matches so you don't want it in output.

Can you post your SORT job that you have treid and SYSOUT output, using Code tags.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Wed Aug 21, 2013 1:13 pm    Post subject: Reply to: Compare 2 files and extract records between header
Reply with quote

It's a very simple two-step process. JOINKEYS, with de-duplicated output to get a list of "groups" that you want to extract. Then JOINKEYS to extract.
Back to top
View user's profile Send private message
rz061m

New User


Joined: 03 Mar 2006
Posts: 48
Location: Chennai

PostPosted: Wed Aug 21, 2013 5:10 pm    Post subject:
Reply with quote

mistah kurtz wrote:
Here is my understanding. Please correct me if I'm wrong?
1. Records Starting with 010 is the Header.
2. Records Starting with 020 is the Data.
3. Records Starting with 030 is the Trailer.

Now you have group of records in both the Input files and you want only those group of records from 2nd Input file, which does not eaxctly matches with First Input File.

For ex:
Code:
010|379339 | |
020|379339 |A2B |
020|379339 |A2C |
030|379339 | |
and
Code:
010|379339 | |
020|379339 |A2B |
020|379339 |A2C |
020|379339 |A2D |
020|379339 |A2E |
030|379339 | |

do not match so you want the 2nd set of records in Output.

Code:
010|444333 | |
020|444333 |A2B |
020|444333 |A2C |
030|444333 | |
and
Code:
010|444333 | |
020|444333 |A2B |
020|444333 |A2C |
030|444333 | |

matches so you don't want it in output.

Can you post your SORT job that you have treid and SYSOUT output, using Code tags.



Yes your understanding is correct..... I used SPLICE to extract only the mismatch records, I do not know how to pull all the set starting from header, detail & Footer
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Wed Aug 21, 2013 6:57 pm    Post subject: Reply to: Compare 2 files and extract records between header
Reply with quote

So, instead of struggling with SPLICE, did you look at what I suggested?

How many records can there be in a group?
Back to top
View user's profile Send private message
rz061m

New User


Joined: 03 Mar 2006
Posts: 48
Location: Chennai

PostPosted: Wed Aug 21, 2013 7:16 pm    Post subject: Re: Reply to: Compare 2 files and extract records between he
Reply with quote

Bill Woodger wrote:
So, instead of struggling with SPLICE, did you look at what I suggested?

How many records can there be in a group?


Each group can have maximum of 15 detail records. I am yet to start the JOINKEYS options. I will try it out today.
Back to top
View user's profile Send private message
rz061m

New User


Joined: 03 Mar 2006
Posts: 48
Location: Chennai

PostPosted: Wed Aug 21, 2013 9:07 pm    Post subject: Re: Reply to: Compare 2 files and extract records between he
Reply with quote

Bill Woodger wrote:
So, instead of struggling with SPLICE, did you look at what I suggested?

How many records can there be in a group?


I could achieve what was needed with the below steps, not sure if there is a better way of doing it.

1. SPLICE the 2 files and extract records that are missing in the second file.
2. Remove duplicates based on key fields from the file from Step1
3. Join keys between first file and file from Step2 and extract all the records from first file matching the key. This would give me header/detail/footer.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Wed Aug 21, 2013 9:39 pm    Post subject: Reply to: Compare 2 files and extract records between header
Reply with quote

Use JOINKEYS for the initial matching.

Only include the detail records.

De-duplicate, in the Main Task of the JOINKEYS (various ways to do this).

A second JOINKEYS step to take your de-duped "tickler file" and extract for hits.

That is not the end of the story, because you need to know how you want to match the keys. If you have a "hit" only because SORTing the data changes the order, is that OK?
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Wed Aug 21, 2013 11:47 pm    Post subject:
Reply with quote

rz061m.

Your match on just the 379339 and 444333 will give you a Cartesian join which you don't need. try this

1. Use JOINKEYS to match on the full key i.e first 16 bytes as you have shown with JOIN UNPAIRED,F2,ONLY that will bring out just unmatched records from file2. Use REFORMAT FIELDS to just write out 379339, 444333.. from F2. i.e REFORMAT FIELDS=(F2:5,6). Make sure you have a COPY operation for the main task and remove the duplicates using OUTFIL with SECTIONS and TRAILER3.

2. Now use another JOINKEYS to match file 2 and the output from above with matching on just the key at pos 5 for 6 bytes. Also the output from above is already SORTED, so make sure you have SORTED and NOSEQCK on the file you referencing in the JOINKEYS.
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 -> DFSORT/ICETOOL 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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 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 How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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