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
 

 

Understanding Merge concept

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
TAPAS PATEL

New User


Joined: 03 Apr 2009
Posts: 47
Location: CHENNAI

PostPosted: Mon Oct 19, 2009 2:40 pm    Post subject: Understanding Merge concept
Reply with quote

Hi All,
I am trying to understand how merge works using DFSORT. I have taken an example for that and tried executing the sort step. I have landed with below results.

Input file details:

SORTIN01 - LREC(80) and RECFM(FB)

Dataset: TDMS.TAPAS.TEST1

0005 VFRD 12344 MAN THIS IS
0009 UTUY OP664 FIRST FILE
0909 VFRD HHH44 HEY MAN HI
0934 AJHJ IOIOO MAKK KOK SO
1234 ABCD HELLO HOW ARE YOU
1234 OOPD PLOLO OIOIOIO YOU
1415 TRRY 099LO LLKLLRE YOU
4565 NVHD 12344 HEY MAN HI
4589 DFAD 10104 HEY MAN HI
5476 MNMN H099O KAKAOIIOYOU
5565 KJKJ 0990B HEY MAN HI
7787 IQPO BOSSO SO YOU ARE

______________________________________________
SORTIN02 - LREC(80) and RECFM(FB)

Dataset: TDMS.TAPAS.TEST2

0009 UTUY OP664 KOKKKON HI
0934 AJHJ IOIOO MAKK KOK SO
1234 ABCD HELLO SECOND FILE
1234 OOPD PLOLO OIOIOIO YOU
4565 NVHD 12344 HEY MAN HI
4565 VFRD 12344 MAN THIS IS
4589 DFAD 10104 HEY MAN HI
5476 MNMN H099O KAKAOIIOYOU
5565 KJKJ 0990B HEY MAN HI
8885 TRRY 099LO LLKLLRE YOU
8885 VFRD HHH44 HEY MAN HI
9894 IQPO BOSSO SO YOU ARE

__________________________________________________

Merge JCL:
Code:
                                                             
//STEP05R  EXEC PGM=SORT
//SORTIN01 DD  DSN=TDMS.TAPAS.TEST1,DISP=SHR                     
//SORTIN02 DD  DSN=TDMS.TAPAS.TEST2,DISP=SHR                     
//SORTOUT  DD  DSN=TDMS.TAPAS.TEST3,DISP=SHR
//SYSIN    DD   *                                                 
  MERGE FIELDS=(6,4,CH,A,1,4,CH,A)                               
/*
//SYSOUT   DD   SYSOUT=*                                         


Merge Output:

0005 VFRD 12344 MAN THIS IS
0009 UTUY OP664 FIRST FILE <---- Query 1
0009 UTUY OP664 KOKKKON HI <----- Query 1
0909 VFRD HHH44 HEY MAN HI
0934 AJHJ IOIOO MAKK KOK SO
0934 AJHJ IOIOO MAKK KOK SO
1234 ABCD HELLO SECOND FILE <-----Query 2
1234 ABCD HELLO HOW ARE YOU <-----Query 2
1234 OOPD PLOLO OIOIOIO YOU
1234 OOPD PLOLO OIOIOIO YOU
1415 TRRY 099LO LLKLLRE YOU
4565 NVHD 12344 HEY MAN HI
4565 NVHD 12344 HEY MAN HI
4565 VFRD 12344 MAN THIS IS
4589 DFAD 10104 HEY MAN HI
4589 DFAD 10104 HEY MAN HI
5476 MNMN H099O KAKAOIIOYOU
5476 MNMN H099O KAKAOIIOYOU
5565 KJKJ 0990B HEY MAN HI
5565 KJKJ 0990B HEY MAN HI
7787 IQPO BOSSO SO YOU ARE
8885 TRRY 099LO LLKLLRE YOU
8885 VFRD HHH44 HEY MAN HI
9894 IQPO BOSSO SO YOU ARE

As per the output above, for the records where the merge key is same in both first file and the second file, record tagged with Query 1 has the record from the first file coming first and then the record from the second file. But this is oppposite when it comes to record tagged with Query 2. Please explain me why is this difference. As per my understanding of merge, when the merge keys match then record from the first file will be written first and the second file record. Please correct me if I am wrong.
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Oct 19, 2009 9:02 pm    Post subject:
Reply with quote

Quote:
As per my understanding of merge, when the merge keys match then record from the first file will be written first and the second file record. Please correct me if I am wrong.


That is only true if EQUALS is in effect for the run (if NOEQUALS is in effect, then records with duplicate keys can be written in any order). Since you didn't show the //SYSOUT messages, I can't tell if EQUALS or NOEQUALS is in effect (but I suspect it's NOEQUALS). To be sure that EQUALS is in effect, you can add this to SYSIN:


Code:

   OPTION EQUALS
Back to top
View user's profile Send private message
TAPAS PATEL

New User


Joined: 03 Apr 2009
Posts: 47
Location: CHENNAI

PostPosted: Tue Oct 20, 2009 4:54 pm    Post subject: but I suspect it's NOEQUALS
Reply with quote

Hi Frank,
Thanks for your reply. Yes you are correct, I checked in SYSOUT and found that option UNEQUALS is in effect. After giving the OPTION EQUALS, I got the desired result.
Back to top
View user's profile Send private message
dneufarth

Active User


Joined: 27 Apr 2005
Posts: 241
Location: Cincinnati OH USA

PostPosted: Tue Oct 20, 2009 7:46 pm    Post subject:
Reply with quote

MERGE FIELDS=(6,4,CH,A,1,4,CH,A)

merge works even though primary field not in any sequence?
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Wed Oct 21, 2009 3:13 am    Post subject:
Reply with quote

Hi Dave,

merge will NOT work if the records are not sorted by the same control fields.


Gerry
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Oct 21, 2009 3:30 am    Post subject:
Reply with quote

Quote:

MERGE FIELDS=(6,4,CH,A,1,4,CH,A)

merge works even though primary field not in any sequence?


It's not clear what you're asking.

In order for that control statement to work, each input file (SORTINxx) must already be sorted by 6,4,CH,A,1,4,CH,A. Otherwise, you'll get an out of sequence error.
Back to top
View user's profile Send private message
dneufarth

Active User


Joined: 27 Apr 2005
Posts: 241
Location: Cincinnati OH USA

PostPosted: Wed Oct 21, 2009 3:39 am    Post subject:
Reply with quote

If I'm reading it correctly, example data of original post not sequenced as stated in MERGE card.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Oct 21, 2009 4:56 am    Post subject:
Reply with quote

Oh, I see what you're saying. I didn't really look at the poster's data since he said it merged ok. With the data as shown and that MERGE statement, he would get an ICE068A out of sequence error. With the following MERGE statement, it would work ok:

Code:

   MERGE FIELDS=(1,4,CH,A,6,4,CH,A)


So you're right that something he's telling us "does not compute".
Back to top
View user's profile Send private message
TAPAS PATEL

New User


Joined: 03 Apr 2009
Posts: 47
Location: CHENNAI

PostPosted: Thu Oct 22, 2009 2:31 pm    Post subject: So you're right that something he's telling us "does no
Reply with quote

Hi All,
Yes we need to have the merge fields as given by Frank. Infact I had executed the step with the same merge statement, But as I was playing around with the sort and trying to understand how it processes I pasted the wrong JCL. Sorry for the confusion.
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am
No new posts Need Help in understanding what is th... mainakdalal PL/I & Assembler 9 Mon Oct 03, 2016 8:03 pm
No new posts SQL Merge error -254 Robin Sulsona DB2 8 Wed Jun 01, 2016 8:28 pm
No new posts SAS MERGE to DFSORT Musab Ahmed DFSORT/ICETOOL 6 Mon Feb 01, 2016 5:44 pm
No new posts Merge two files having two trailer re... R.Manivannan DFSORT/ICETOOL 7 Wed Nov 25, 2015 9:18 pm


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