Merging matching records from two files



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Merging matching records from two files

Postby zhinghur » Sun Jan 01, 2012 3:27 pm

Hello everyone,

Wish you A Happy and Prosperous New Year at the outset.

I am going to give the exact details of keys this time, alongside other information. Because last time due to my ignorance, many ended up loosing their precious time. Sorry again, for that.


I have 2 input files of different lengths.

In File 1(80 bytes), keys starts at position 13. Key length is total 5 bytes(marked in BOLD). Every alternate line is blank.

File1
======
./ add name=AB120

./ add name=BN132

./ add name=AA100

./ add name=AB125

./ add name=AB132


File2
======
In File 2(93 bytes), keys starts at 1st position of length 2 and at 24th position of 3 length. = Total 5 bytes.

123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123
ZZ4364733476834734132liiiiiiiiiiiie8730dzz999-111111118dddd3222lolololo55555555554444422222110111111
BN166087892363362132suaemmm8730dis832-333930038dasd7232fasfasf777283232889288883998872222222
BN139687127127382132monieadic7330lis832-333998999hdka8323dasdas9999938828892858883993243535235
BN136687892363682132uytieeadci7330lis832-333998999hdka8323dasdas999993882889288588399324816247
BN188867127173682132kkekieadcc7330lis832-333998999hdka8323dasdas999993882889288583993215463464
BN111187892636844130iunssieadc7330lis832-333998999hdka8323dasdas9999938828892888853993247434737
AB111187893623682125ssaeimmm730dis832-333930038dasd7232fasfasf7772832328892888835998854737473
AB122287171273682125ssoniieadc7330lis832-333998999hdka8323dasdas999993882889288883959323737348
AA435731777711118100luimeiehe9993lid999-777777777deda3243dsdtats681331273999651121813842212222
AB107779236333682120suaemim8730dis832-333930038dasd7232fasfasf7772832328892588883995881089079
AB120333392333682120monieacc7330lis832-333998999hdka8323dasdas9999938828895288883993524899664
AB873562720991234120hunqgeey7333lis832-333333333hdka8323ttttats4444455555553333311111112434324
AB166087893633682132dasdjhas2222zis832-323123328fgkk4432ffdffff1298989232376764678466872443236
AB139687171273682132asjdashd7330lis832-333998999hdka8323dasdas9999938828892858886666664476587
AB136687923633682132uytieeadci7330lis832-66666234hdka8323dasdas9999938828892885883777744436436
AB188861271273682132kkekieacc7330lis832-3339982hdka8323dasdas999993ewe88288928858888835553456
AB166078923633682132suaemmm8730dis832-3339302121dasd7232fasfasf7772832328899999999995542342
AB139871271273682132monieadic7330lis832-3339984444hdka8323dasdas99999388255555555555555575675
AB136878923633682132uytieeadci7330lis832-333998111hdka8323dasdas999993882889444444444455487686
AB188671271273682132kkekieadcc7330lis832-33399222hdka8323dasdas999993882833333333333555543243



Write to output file only if key match, and in the order shown below.
Record from 1st file and all the key matched record from second file.
From 2nd file, output of around 61 bytes from bytes 23 to end is to be written.


Output File
===========

./ add name=AB120
120suaemim8730dis832-3222222222dasd7232fasfasf7772832328892588883995881
120monieacc7330lis832-333333333hdka8323dasdas9999938828895288883993524
120hunqgeey7333lis832-311111111hdka8323ttttats4444455555553333311111112
./ add name=BN132
132suaemmm8730dis832-333930038dasd7232fasfasf77728323288928888399887
132monieadic7330lis832-333998999hdka8323dasdas999993882889285888399324
132uytieeadci7330lis832-333998999hdka8323dasdas999993882889288588399324
132kkekieadcc7330lis832-333998999hdka8323dasdas99999388288928858399321
./ add name=AA100
100luimeiehe9993lid999-777777777deda3243dsdtats681331273999651121813842
./ add name=AB125
125ssaeimmm730dis832-5353136535dasd7232fasfasf777283232889288883599885
125ssoniieadc7330lis832-5315336337hdka8323dasdas999993882889288883959323
./ add name=AB132
132dasdjhas2222zis832-323123328fgkk4432ffdffff12989892323767646784668724
132asjdashd7330lis832-333998999hdka8323dasdas99999388288928588866666644
132uytieeadci7330lis832-66666234hdka8323dasdas9999938828892885883777744
132kkekieacc7330lis832-3339982hdka8323dasdas999993ewe882889288588888355
132suaemmm8730dis832-3339302121dasd7232fasfasf77728323288999999999955
132monieadic7330lis832-3339984444hdka8323dasdas999993882555555555555555
132uytieeadci7330lis832-333998111hdka8323dasdas9999938828894444444444555
132kkekieadcc7330lis832-33399222hdka8323dasdas9999938828333333333335555
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

 

Re: Merging matching records from two files

Postby enrico-sorichetti » Sun Jan 01, 2012 3:53 pm

if You hadn' t stupidly asked to delete Your previous topic
You would have found the solutions there,
repeated and tested three times
and the thanks &deity final requirement could have been satisfied with one simple modification to the control cards

but you were too lazy to try to understand so you asked to delete good replies.
that' s what I call wasting people times

and ... what is that You do not understand in posting data using the code tags ? :twisted:

and... in IT you need to use exact measures ...
around 61 bytes

it means that also ..., 59, 60, ...62, 63 ,... could be ok :geek:
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2643
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Merging matching records from two files

Postby BillyBoyo » Sun Jan 01, 2012 3:56 pm

It is best to use the Code tags to present your data and code, as this preserves the formatting. The Bolding means nothing inside the Code tags, so don't use the bolding for data/code please.

Can you post the ICE201I message from one of your jobs so that we can see the DFSORT level you are using, please?

I'm not sure the original code will help, due to the need to drop unmatched keys. I'd look at Joinkeys. Due to the key-split on the second file, you'll need to define the key for the first file as being in two parts. You'll need a sequence number appended to get the output back into the original order. Drop the blanks from file one. You'll probably need an OUTREC with the Slash (/) Operator to get the /ADD and the file two record at the same time.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Merging matching records from two files

Postby enrico-sorichetti » Sun Jan 01, 2012 4:34 pm

follow on for a finger check ....
.
Because last time due to my ignorance, many ended up loosing their precious time. Sorry again, for that.


the real problem is not the ignorance or lack of skills, those things can be taken care of with training and help

the real problem is the attitude ...
the <unwillingness> to try to understand the suggestions You were given and to reply to the questions You were asked
also ... posting it didn' t work did not help You to get a better answer
those are the things that make people loose not time but the temper and the good helping mood
( loosing the helping mood is much worse for You )

the impression was that we were talking and You were listening to you iPod :D

anyway a bit of bashing never hurt anybody ;)
(there are forum where people get bashed for much less )

happy new year anyway !
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2643
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Merging matching records from two files

Postby zhinghur » Sun Jan 01, 2012 8:34 pm

wow... Thank you :)
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

Postby enrico-sorichetti » Sun Jan 01, 2012 9:05 pm

keep following on...
wouldn' t it be faster to get rid of file1
and build from scratch a ./ ADD NAME=XXYYY whenever the <key changes> on file 2
?
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2643
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Merging matching records from two files

Postby BillyBoyo » Sun Jan 01, 2012 10:09 pm

Still have that tetchy selection to get around, not everything on the second file is needed :-)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Merging matching records from two files

Postby enrico-sorichetti » Sun Jan 01, 2012 10:24 pm

a bit off topic maybe, but here is a snippet wich works for my previous question...
( I am not a great sort practitioner, probably Frank might find a better solution )

 ****** ***************************** Top of Data ******************************
 000001 //ENRICO1  JOB NOTIFY=&SYSUID,                                         
 000002 //             MSGLEVEL=(1,1),CLASS=A,MSGCLASS=X                       
 000003 //*                                                                     
 000004 //ICE1    EXEC PGM=SORT                                                 
 000005 //SYSPRINT  DD SYSOUT=*                                                 
 000006 //SYSOUT    DD SYSOUT=*                                                 
 000007 //TOOLMSG   DD SYSOUT=*                                                 
 000008 //DFSMSG    DD SYSOUT=*                                                 
 000009 //SORTIN    DD *                                                       
 000010 AB000000 07 0000001 123                                                 
 000011 AB000000 07 0000002 123                                                 
 000012 AB000000 07 0000003 123                                                 
 000013 BC000000 08 0000001 234                                                 
 000014 BC000000 08 0000002 234                                                 
 000015 BC000000 08 0000003 234                                                 
 000016 BC000000 08 0000004 234                                                 
 000017 MS000000 09 0000001 345                                                 
 000018 MS000000 09 0000002 345                                                 
 000019 MS000000 09 0000003 345                                                 
 000020 AB000000 07 X000002 123                                                 
 000021 AB000000 07 X000003 123                                                 
 000022 MS000000 09 0000004 345                                                 
 000023 MS000000 09 0000005 345                                                 
 000024 MS000000 09 0000006 345                                                 
 000025 MS000000 09 0000007 345                                                 
 000026 //SORTOUT   DD SYSOUT=*,DCB=(RECFM=FB,LRECL=80)                         
 000027 //SYSIN     DD *                                                       
 000028   OPTION EQUALS                                                         
 000029   INREC  OVERLAY=(81:1,2,21,3)                                         
 000030   SORT  FIELDS=(81,5,CH,A)                                             
 000031   OUTREC OVERLAY=(86:SEQNUM,5,ZD,RESTART=(81,5))                       
 000032   OUTFIL IFTHEN=(WHEN=(86,5,ZD,EQ,1),                                   
 000033          BUILD=(C'./ ADD NAME=',81,5,/1,80)),                           
 000034          IFTHEN=(WHEN=NONE,                                             
 000035          BUILD=(1,80))                                                 
 ****** **************************** Bottom of Data ****************************


and the result

********************************* TOP OF DATA **********************************
./ ADD NAME=AB123                                                               
AB000000 07 0000001 123                                                         
AB000000 07 0000002 123                                                         
AB000000 07 0000003 123                                                         
AB000000 07 X000002 123                                                         
AB000000 07 X000003 123                                                         
./ ADD NAME=BC234                                                               
BC000000 08 0000001 234                                                         
BC000000 08 0000002 234                                                         
BC000000 08 0000003 234                                                         
BC000000 08 0000004 234                                                         
./ ADD NAME=MS345                                                               
MS000000 09 0000001 345                                                         
MS000000 09 0000002 345                                                         
MS000000 09 0000003 345                                                         
MS000000 09 0000004 345                                                         
MS000000 09 0000005 345                                                         
MS000000 09 0000006 345                                                         
MS000000 09 0000007 345                                                         
******************************** BOTTOM OF DATA ********************************


it will work for the TS data by reworking the build section for the data part
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2643
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Merging matching records from two files

Postby BillyBoyo » Mon Jan 02, 2012 5:22 am

zhinghur wrote:wow... Thank you :)


Instead of cultivating an air of enigma, why can't you tell us what version of DFSORT you are using? The information will be necessary. If the hard-working DFSORT guys provide a solution for you and then you can't get it to work because you don't have an appropriate level of DFSORT, you will have wasted more of other's time. You were asked this on the previous topic (from memory, more than once), and again on this one. The ICE201I message is all that you need to locate, from any sort job that you have.

If you are going with enrico's solution, let us know. If you are coding something yourself, let us know how it goes.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 7:22 pm

Hi Enrico,

Your soln. looks good. But just missed out the key matched part. It is running ok now, but the non-required fields are coming too, as files has around 500,000 records. Can you please add the matching condition for keys, only for those key matches give the output.

Thank You.
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post