How to Merge records in 2 ps files alternately into 3rd?



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

Re: How to Merge records in 2 ps files alternately into 3rd?

Postby Frank Yaeger » Tue Jan 10, 2012 1:31 am

FWIW, here's another way to do it using DFSORT's JOINKEYS function:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD *
RECORD1
RECORD2
RECORD3
//IN2 DD *
RECORD4
RECORD5
RECORD6
//SORTOUT DD SYSOUT=*
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(81,8,A),SORTED,NOSEQCK
  JOINKEYS F2=IN2,FIELDS=(81,8,A),SORTED,NOSEQCK
  JOIN UNPAIRED,F1,F2,ONLY
  OPTION COPY
  OUTREC BUILD=(1,80)
//JNF1CNTL DD *
  INREC OVERLAY=(81:SEQNUM,8,ZD,START=1,INCR=2)
//JNF2CNTL DD *
  INREC OVERLAY=(81:SEQNUM,8,ZD,START=2,INCR=2)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: How to Merge records in 2 ps files alternately into 3rd?

Postby Nik22Dec » Wed Jan 11, 2012 10:39 am

BillyBoyo wrote:
//SORTIN01 DD *
RECORD1
RECORD2
RECORD3
//SORTIN02 DD *
RECORD4
RECORD5
RECORD6
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *
  INREC BUILD=(1,7,X,SEQNUM,7,ZD)
  MERGE FIELDS=(9,7,CH,A),NOEQUALS
  OUTREC BUILD=(1,7)



Hi Billy,

I was browsing through the forum & came across your solution to this problem. I tried understanding the logic of the sort card which you have used. Can you please let me know why will the sequence numbering always happen in the alternate fashion on both the files. I could see the output in the below fashion.

RECORD1 0000001
RECORD4 0000002
RECORD2 0000003
RECORD5 0000004
RECORD3 0000005
RECORD6 0000006
Thanks,
Nik
User avatar
Nik22Dec
 
Posts: 68
Joined: Mon Dec 26, 2011 6:38 pm
Has thanked: 2 times
Been thanked: 0 time

Re: How to Merge records in 2 ps files alternately into 3rd?

Postby dick scherrer » Wed Jan 11, 2012 11:38 am

Hello,

Because this is what you requested. . .

If we understood your request, you wanted the records from both written to the output interleaving records from the 2 files.

If that is not what you wanted, you need to clarify what is actually wanted.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: How to Merge records in 2 ps files alternately into 3rd?

Postby Nik22Dec » Wed Jan 11, 2012 11:43 am

Hi Dick,

Thanks so much for your reply.

Apologies, I guess I wasn't clear enough. I am not asking this question from the requirement purpose but, from the understanding purpose. I couldn't fathom why did sort pick up records alternately from the input files & not the way Merge usually picks the records i.e. first copying all the records from file1 & then copying all the records from file2. Why did it happen in the alternate fashion with this sort card.
Thanks,
Nik
User avatar
Nik22Dec
 
Posts: 68
Joined: Mon Dec 26, 2011 6:38 pm
Has thanked: 2 times
Been thanked: 0 time

Re: How to Merge records in 2 ps files alternately into 3rd?

Postby enrico-sorichetti » Wed Jan 11, 2012 11:57 am

Merge usually picks the records i.e. first copying all the records from file1 & then copying all the records from file2.

it would be wise to review Your understanding of merge,
the output of a merge will contain the records from <all> the input files <merged :D> <in proper sequence> according to the <key>

I would reword the question as ....

why SEQUENCING flips flops between the inputs ?
but the only person able to reply in depth will be Frank

I just went thru the manuals and the only hint I could find was in the INREC paragraph
The sequence numbers are assigned in the order in which the records are received for INREC processing.

so feeding records to INREC works in <ROTATE> mode

just tested with 3 an 4 input files ( after that I got bored ) :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: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: How to Merge records in 2 ps files alternately into 3rd?

Postby Nik22Dec » Wed Jan 11, 2012 12:38 pm

Hi Enrico,

Thanls so much for your reply.
enrico-sorichetti wrote:
Merge usually picks the records i.e. first copying all the records from file1 & then copying all the records from file2.

it would be wise to review Your understanding of merge,
the output of a merge will contain the records from <all> the input files <merged :D> <in proper sequence> according to the <key>


Yeah, I just realized that. Thaks for pointing that out.

I would reword the question as ....

why SEQUENCING flips flops between the inputs ?
but the only person able to reply in depth will be Frank
I just went thru the manuals and the only hint I could find was in the INREC paragraph
The sequence numbers are assigned in the order in which the records are received for INREC processing.

so feeding records to INREC works in <ROTATE> mode


Yeah, the logic makes sense. Even I tried putting in multiple input files & the flip-flopping happened exactly the way you have explained. I guess we will have to wait for Frank to visit the page to confirm our understanding.
Thanks,
Nik
User avatar
Nik22Dec
 
Posts: 68
Joined: Mon Dec 26, 2011 6:38 pm
Has thanked: 2 times
Been thanked: 0 time

Re: How to Merge records in 2 ps files alternately into 3rd?

Postby BillyBoyo » Wed Jan 11, 2012 3:30 pm

If you wrote your own program to do a merge:

read 1, read 2
merging:
if key1 < key2
    copy from 1 until key1 not < key2, back to merging
if key2 < key1
    copy from 2 until key2 not < key1, back to merging
copy current records on 1 and 2
read 1 and 2
back to merging


I've ignored eof processing above to keep it simple.

It would be possible to read the entire 1 file before the 2nd record on the 2 file, depending on key values.

A simple MERGE in SORT would work approximately like this.

What is going on in the MERGE in the sort is something a bit different, in that there are different stages not outlined in the pseudo-code above.

The records are being changed before the MERGE statement. Each record will be changed before being presented to the processing for the MERGE. Intially a record will be read on each file. The amendment made to each record, and then both records will be presented to the MERGE for processing. The file 1 will have been read first, so it gets the lowest sequence number, with the file 2 record having the next. The MERGE spots this and writes out the first record from file 1, and has to therefore read a new record from that file, which will be pre-processed, getting the sequence number 3. Both current records are presented to the merge. The file 2 is now low, so will be written to output and the next file 2 record will be read, which will get the sequence number 4. Etc.

So, as long as the initial records are read in the order of the files (SORTIN01 first, SORTIN02 second) then the process will work. A quick look at the manual means I need a longer look to confirm how that part of the process is documented :-)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: How to Merge records in 2 ps files alternately into 3rd?

Postby enrico-sorichetti » Wed Jan 11, 2012 7:22 pm

well seems that the sort logic is a bit different as far as records are fed to INREC processing

for RYO merge (2 files ) the reading would not flip/flop for every record
for inequality it would keep reading from the <last> file

file1
1
2
3
6
7

file2
4
5
8
9

read file1-1/file2-4
keep reading from file1 until the key is ge 4
so the ryo logic would see the sequence as
file1-1/file2-4
file1-2
file1-3/(4)
file1-6
the ryo merge will not see the file2-5 until the file1-6 has been read

well, i hope i did not mess up people' s understanding
let' s wait for Frank
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: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: How to Merge records in 2 ps files alternately into 3rd?

Postby BillyBoyo » Wed Jan 11, 2012 7:34 pm

Without setting the merge-key to an ascending sequence-number, the flip-flopping will not occur. It is the sequence-number used as the merge key which is causing the flip-flopping. After the initial reads, which file is read next is down to the key values on the two/multiple files. If merged files happened to have alternating keys, they would appear to flip-flop. Here the alternating was forced by using the sequence-number. Yes. Let's wait for Frank.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: How to Merge records in 2 ps files alternately into 3rd?

Postby enrico-sorichetti » Wed Jan 11, 2012 8:16 pm

well, things are getting murky...
the merge takes place on the SEQNUM and merge does not know what to read yet :D
( merge would know what to read only after the seqnum has been assigned , i suppose )
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: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

PreviousNext

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post