View previous topic :: View next topic
|
Author |
Message |
Ron Klop
New User
Joined: 28 Sep 2012 Posts: 28 Location: holland
|
|
|
|
Hai
Daily we get a file with around 10 million records. It contains a snapshot (full). We only want to process the mutations (delta). What is a quick way to sort out the mutations??
Thanx
Ron
Example
File A previous day
Code: |
Recordnr code name
1 A Always
2 D Direct
7 F Forever
8 L Long |
File B current day
Code: |
Recordnr code name
1 A All times
2 D Direct
3 H Hold
7 F Forever |
Output C
Code: |
Recordnr code name
1 A All times
3 H Hold |
Output C contains record 1, because the name has changed.
It does not contain record 2 and 7, because they are not changed.
It contains record 3, because it is a new one.
It does not contain record 8, because we are not interested in deletes (although if there is a way to make them recognizable in the output, it might come in handy in the future)
Code'd |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Please use the Code tags to preserve spacing and use the Preview button to check before posting.
Please don't use tabs in a document and then paste them in. I attempted to demangle things to get them to line up, but the presence of tabs consumed even more time than usual, so I abandoned it.
Are the record-numbers present in the data, or just for illustration? Do you have a key? Are keys unique? If not, how would any matching go? Have you looked at SUPERCE to see if it can give you what you want? |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
Quote: |
We only want to process the mutations (delta).
|
define how to identify mutations |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
And that makes 10,000, enrico :-) |
|
Back to top |
|
|
Ron Klop
New User
Joined: 28 Sep 2012 Posts: 28 Location: holland
|
|
|
|
hai Bill
sorry, I am a newbie
recordnumbers are for illustration
code-field (A, D, F etc) is the key and they are unique
Haven't looked at SUPERCE (yet).
Enrico: what do you mean wíth 'define how to identify mutations'?? We get the data from another system. That system will not change the layout of there records, for instance by adding a mutation-indicator
greetings
Ron |
|
Back to top |
|
|
Ron Klop
New User
Joined: 28 Sep 2012 Posts: 28 Location: holland
|
|
|
|
Hai Bill
can SUPERCE also be performed in batch, cause I am looking for a job / jcl that can be performed daily in production
greetings |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Yes.
You'd get a listing you'd have to post-process if it is for machine rather than human consumption.
Your task is a simple JOINKEYS. If no-one beats my to it, I'll find an example, there are lots here. You will be able to identify deletes if you want. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
As Bill said above, it is a simple joinkeys, next time please search on this forum and then post as you would find lots of examples.
If you care for the whole record and if anything is changes per record then catch that then you can use this .
Code: |
JOINKEYS F1=IN1,FIELDS=(1,your total rec length,A)
JOINKEYS F2=IN2,FIELDS=(1,your total rec length,A)
REFORMAT FIELDS=(F1:1,your total rec length)
JOIN UNPAIRED,F1,ONLY
OPTION COPY |
F1 = File B current day
F2 = File A previous day |
|
Back to top |
|
|
Ron Klop
New User
Joined: 28 Sep 2012 Posts: 28 Location: holland
|
|
|
|
Thnx Rohit
what does this mean:
FIELDS(1,your total rec length,A) ?
suppose my total record length is 80 |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
On the JOINKEYS, the FIELDS parameter identifies the key for the join. You have two options, since your key is first: specify the entire record as the key - which is useful if you want to list changes as add/delete pairs; specify the key only, then use an IFTHEN=(WHEN=(logicalexpression) in the main task to check whether matched keys contain the same data. |
|
Back to top |
|
|
Ron Klop
New User
Joined: 28 Sep 2012 Posts: 28 Location: holland
|
|
|
|
I really appreciate your help. Been reading about JOINKEYS in the manual, bur still find it difficult in my example.
In my example the records look like this:
"A Always". So a code with a description.
If I want to compare file B with A and want to know what has been added, updated and deleted today when comparing with yesterday, how does my JOINKEY look like then if:
1. I want to compare on the whole record
2. I want to compare on the key (which is code) and use IFTHEN=(WHEN= in the main task to see if the descripton has change
Is somebody willing to write it out for me??
thnx
Ron |
|
Back to top |
|
|
boyti ko
New User
Joined: 03 Nov 2014 Posts: 78 Location: Malaysia
|
|
|
|
Maybe you could try doing it at least. They already spent their time explaining things to you. But in return you just want the whole code to be given to you. |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Code: |
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(1,1,A)
JOINKEYS F2=IN2,FIELDS=(1,1,A)
REFORMAT FIELDS=(F1:1,80,F2:5,76)
JOIN UNPAIRED,F1
OPTION COPY
OUTFIL OMIT=(5,76,CH,EQ,81,76,CH),
BUILD=(1,80)
//IN1 DD *
A ALL TIMES
D DIRECT
H HOLD
F FOREVER
//IN2 DD *
A ALWAYS
D DIRECT
F FOREVER
L LONG |
You show your data as unsorted. It would be much better if the data was sorted, otherwise it will always be sorted twice (yesterday and today).
If it were in order prior to this step, you'd specify SORTED,NOSEQCK on the JOINKEYS statements.
If you don't have an existing SORT, you could output the SORTed "today's" with an additional OUTFIL and adding the match-marker (?) to the REFORMAT and selecting all the 1's. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3049 Location: NYC,USA
|
|
|
|
Quote: |
Is somebody willing to write it out for me?? |
Start here |
|
Back to top |
|
|
Ron Klop
New User
Joined: 28 Sep 2012 Posts: 28 Location: holland
|
|
|
|
Thnx all
Data is sorted when received. I will start working on this one of these days |
|
Back to top |
|
|
|