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 two files and write all rec if there is any change

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

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Thu May 01, 2014 2:12 pm    Post subject: compare two files and write all rec if there is any change
Reply with quote

Hi

I have two files of same fields and LRECL (100), F1(previous day file) and F2(current day file). I have a account number field (first 3 char, ex AAA in F1 file) and beneficiaries field along with other data fields in both files. There are multiple records for a particular account upto number of beneficiaries present on this policy.

Now I want to compare these two files on full length and write record from current day file i.e from F2 if there is a change from F1 file. criteria is , if any of the record on a particular account is changed in F2 than from F1,then write all records available on that account from F2 file in output file.

I have used JOIN UNPAIRED,F2,ONLY, which is only sending the unmatched record on any account.

How we can send all the records of a particular account from F2 if there is a change on any record on this account.

EX:


Code:
 
F1 File:
 AAA Mr Mark 01012014 25
 AAA Mr Rose 02012014 75
 BBB Mr Mike 01012014 50
 BBB Mr Luke 01012014 50

F2 File
 AAA Mr Mark  01012014 25
 AAA Mr Rose  02012014 75 
 BBB Mr Mike  01012014 25
 BBB Mr Luke  01012014 50
 BBB Mr Hawk  04012014 25

So output should be (AAA records are exactly matched so we will not write records of AAA)
 BBB Mr Mike 01012014 25
 BBB Mr Luke 01012014 50
 BBB Mr Hawk 04012014 25
 


Please help. Thank you.
Back to top
View user's profile Send private message

Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Thu May 01, 2014 3:30 pm    Post subject:
Reply with quote

UNPAIRED must give you record
BBB Mr Hawk 04012014 25

now in the second pass, take its account no. BBB and write all duplicates/matches from first file.

Your output then will have all 3 records of BBB.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Thu May 01, 2014 3:41 pm    Post subject: Reply to: compare two files and write all rec if there is an
Reply with quote

And what if two BBBs show differences?
Back to top
View user's profile Send private message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Thu May 01, 2014 4:08 pm    Post subject:
Reply with quote

Bill,
yes, this is my requirement. F2 file will always have the changed record from the previous day F1 file if there are any changes in any record. So, we need to pick up these changed record and also other un changed available records of BBBs and write these. If all the records of any account is exactly matched then we should totally skip all records of this account.

Thanks
Back to top
View user's profile Send private message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Thu May 01, 2014 6:59 pm    Post subject:
Reply with quote

Hey Dave
Can you elaborate on second part that you suggested. I tried but not getting the desired result.
Thanks
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Thu May 01, 2014 7:24 pm    Post subject: Reply to: compare two files and write all rec if there is an
Reply with quote

He means it is a two-stage process. First, find the differences. Second, de-duplicate the differences (possibly, up to you, we don't know...) and extract those groups of records which contain a difference.

First stage you SORT and match on the whole record. Second stage de-dupe and match one the key, SORTing if you don't have your key starting in position one on the record and being contiguous.
Back to top
View user's profile Send private message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Thu May 01, 2014 8:00 pm    Post subject:
Reply with quote

Bill,
I did not get that. I have the changed records that are in file F2. Now I also want other records of this account which are not changed.
I used JOIN UNPAIRED,F2,ONLY which results in only difference/changed record.
I have written another SORT step which takes the F1 file and new file created in prev step (file with changed record).
Can you pls let me know how I should move ahead from here. Thanks
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Thu May 01, 2014 8:23 pm    Post subject: Reply to: compare two files and write all rec if there is an
Reply with quote

You match on entire records to find the differences. The differences would show in two ways: unmatched records from F1; unmatched records from F2.

If you want all the records from F2 which have any unmatched records from that group, then you need UNMATCHED,F2 to an output dataset.

Then another step, or separate process, which de-duplicates that file, and uses the de-duplicated file to match, by key, on F2.

If that is what you want. Unclear to me.

If still stuck, put representative sample data for F1 and F2 and expected output from the process.
Back to top
View user's profile Send private message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Thu May 01, 2014 8:47 pm    Post subject:
Reply with quote

Bill
I given the sample data and expected output in my first post. Thanks
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Thu May 01, 2014 8:53 pm    Post subject: Reply to: compare two files and write all rec if there is an
Reply with quote

So that is representative? Nothing other than those two situations to cater for?

Inserts only, no amendments, no deletes?

One JOINKEYS to get the inserts. Second JOINKEYS to match the inserts to the F2 to get all the records of the same key. Simple. Really simple. Just as Dave wrote earlier. What was that, hours ago?
Back to top
View user's profile Send private message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Thu May 01, 2014 9:24 pm    Post subject:
Reply with quote

'Second JOINKEYS to match the inserts to the F2 to get all the records of the same key' - this is where I am stuck. Since I am new to joinkeys, i dont have much idea of inserting from one file to other. I tried reading some material but didnt get example. Thanks
Back to top
View user's profile Send private message
faizm

New User


Joined: 13 Apr 2012
Posts: 59
Location: India

PostPosted: Thu May 01, 2014 9:50 pm    Post subject:
Reply with quote

Thank you. It is working now.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Thu May 01, 2014 10:19 pm    Post subject: Reply to: compare two files and write all rec if there is an
Reply with quote

Good for you. Half an hour well-spent. If you'd like to post your solution, it may help others.
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 Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
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 Error IEC161I 052(009,XXXRS00)-084 wh... amitc23 JCL & VSAM 10 Wed Oct 12, 2016 5:00 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