Merge records from two file, one by one.

IBM's Sort Product, ICETOOL, ICEMAN and ICEGENER.
Post Reply
Imran Lamba
Registered Member
Posts: 13
Joined: Tue Jan 19, 2016 11:49 am

Merge records from two file, one by one.

Post by Imran Lamba »

Hi,

I need to create a temporary file for a testing purpose. But creating it is somewhat tricky. I am assuming some SORT expert can help me on this.

I basically want to merge values from two input files but the values from input should come in alternate way in the final output file.

File 1:

Code: Select all

AAA 
BBB
CCC
File 2:

Code: Select all

9999
8888
7777
Output File:

Code: Select all

AAA 
9999
BBB
8888
CCC
7777
Please advise on any logic and I shall try to work it out.
Last edited by Anuj Dhawan on Tue Sep 06, 2016 9:53 pm, edited 1 time in total.
Reason: Added code tags.
User avatar
Magesh_j
Registered Member
Posts: 33
Joined: Sun Sep 04, 2016 8:50 pm

Re: Merge records from two file, one by one.

Post by Magesh_j »

Imran Lamba,

Assuming LRECL=80.

Code: Select all

//SORT010    EXEC PGM=SORT                              
//SORTIN  DD *                                          
BEGIN                                                   
//        DD DSN=FIRST.FIL,DISP=SHR                     
//        DD *                                          
BEGIN                                                   
//        DD DSN=SECOND.FIL,DISP=SHR                    
//SORTOUT DD SYSOUT=*                                   
//SYSOUT DD SYSOUT=*                                    
//SYSIN DD *                                            
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,5,CH,EQ,C'BEGIN'),  
                           PUSH=(81:SEQ=8))             
  SORT FIELDS=(81,8,CH,A)                               
  OUTFIL INCLUDE=(1,5,CH,NE,C'BEGIN'),BUILD=(1,80)      
 
you may remove the "Begin" record in DD Statement, if you have any unique way of identifying 1st and 2nd file. Then you need to adjust IFTHEN=(WHEN=GROUP,BEGIN/KEYBEGIN ?

Regards,
Magesh
User avatar
Magesh_j
Registered Member
Posts: 33
Joined: Sun Sep 04, 2016 8:50 pm

Re: Merge records from two file, one by one.

Post by Magesh_j »

Also you may use JOINKEYS, but consumes three pass without SORTing, where my previous solution consumes sorting with two pass.

Code: Select all

//S1   EXEC  PGM=SORT                                 
//SYSOUT DD SYSOUT=*                                  
//INA DD DSN=FILE1,DISP=SHR                       
//INB DD DSN=FILE2,DISP=SHR                       
//SORTOUT  DD SYSOUT=*                                
//SYSIN    DD *                                       
  OPTION COPY                                         
  JOINKEYS F1=INA,FIELDS=(81,08,A),SORTED,NOSEQCK     
  JOINKEYS F2=INB,FIELDS=(81,08,A),SORTED,NOSEQCK     
  REFORMAT FIELDS=(F1:1,80,F2:1,80)                   
  OUTFIL BUILD=(1,80,/,81,80)                         
//JNF1CNTL DD *                                       
  INREC OVERLAY=(81:SEQNUM,8,ZD)                      
//JNF2CNTL DD *                                       
  INREC OVERLAY=(81:SEQNUM,8,ZD)                      
 
nicc
Global Moderator
Global Moderator
Posts: 691
Joined: Wed Apr 23, 2014 8:45 pm

Re: Merge records from two file, one by one.

Post by nicc »

As there is only one record in each dataset (not file) why are you sorting at all?
Regards
Nic
User avatar
Magesh_j
Registered Member
Posts: 33
Joined: Sun Sep 04, 2016 8:50 pm

Re: Merge records from two file, one by one.

Post by Magesh_j »

Sorry Nic, I couldn't understand,
Nic wrote: As there is only one record in each dataset (not file) why are you sorting at all?
As per the requirement, the input and output dataset has multiple records.
Imran Lamba wrote:

Code: Select all

File 1: 
AAA 
BBB
CCC

File 2: 
9999
8888
7777

Output File: 
AAA 
9999
BBB
8888
CCC
7777
Thanks
Magesh
nicc
Global Moderator
Global Moderator
Posts: 691
Joined: Wed Apr 23, 2014 8:45 pm

Re: Merge records from two file, one by one.

Post by nicc »

Sorry - I am in the wrong topic!
Regards
Nic
User avatar
Magesh_j
Registered Member
Posts: 33
Joined: Sun Sep 04, 2016 8:50 pm

Re: Merge records from two file, one by one.

Post by Magesh_j »

No problem Nic.

Thanks
Magesh
Imran Lamba
Registered Member
Posts: 13
Joined: Tue Jan 19, 2016 11:49 am

Re: Merge records from two file, one by one.

Post by Imran Lamba »

Magesh_j wrote: you may remove the "Begin" record in DD Statement, if you have any unique way of identifying 1st and 2nd file. Then you need to adjust IFTHEN=(WHEN=GROUP,BEGIN/KEYBEGIN ?
Thanks Magesh. If I know from which file I want the first record in output and keep that file as first in the SORT, then also I would not need BEGIN?
Imran Lamba
Registered Member
Posts: 13
Joined: Tue Jan 19, 2016 11:49 am

Re: Merge records from two file, one by one.

Post by Imran Lamba »

Sorry no it won't work. How would I know where to start second sequence...hmm
User avatar
Magesh_j
Registered Member
Posts: 33
Joined: Sun Sep 04, 2016 8:50 pm

Re: Merge records from two file, one by one.

Post by Magesh_j »

Imran Lamba,

I think You missed my second solution. I would prefer second solution because it just do a copy and no sorting.

Still if you prefer my first solution, On sort fields, please add EQUALS to preserve the order of the same key, because DFSORT comes with EQUALS=NO by default.

Code: Select all

SORT FIELDS=(81,8,CH,A),EQUALS
Also you have to consider what happen if you have different number of records, Say first file is having 3 records and second file is having 4 records.

Second solution will fails, if you have difference in number of records, because you dont have a matching record. You can make second solution work by handling

Code: Select all

JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,80,F2:1,80,?)
OUTFIL IFTHEN=(WHEN=(161,1,CH,EQ,C'B'),BUILD=(1,80,/,81,80)),
       IFTHEN=(WHEN=(161,1,CH,EQ,C'1'),BUILD=(1,80)),
       IFTHEN=(WHEN=NONE,BUILD=(81,80))
Chandan Yadav
Website Team
Website Team
Posts: 70
Joined: Wed Jul 31, 2013 10:19 pm

Re: Merge records from two file, one by one.

Post by Chandan Yadav »

Magesh_j wrote: Imran Lamba,

Assuming LRECL=80.

Code: Select all

//SORT010    EXEC PGM=SORT                              
//SORTIN  DD *                                          
BEGIN                                                   
//        DD DSN=FIRST.FIL,DISP=SHR                     
//        DD *                                          
BEGIN                                                   
//        DD DSN=SECOND.FIL,DISP=SHR                    
//SORTOUT DD SYSOUT=*                                   
//SYSOUT DD SYSOUT=*                                    
//SYSIN DD *                                            
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,5,CH,EQ,C'BEGIN'),  
                           PUSH=(81:SEQ=8))             
  SORT FIELDS=(81,8,CH,A)                               
  OUTFIL INCLUDE=(1,5,CH,NE,C'BEGIN'),BUILD=(1,80)      
 
you may remove the "Begin" record in DD Statement, if you have any unique way of identifying 1st and 2nd file. Then you need to adjust IFTHEN=(WHEN=GROUP,BEGIN/KEYBEGIN ?

Regards,
Magesh
Not sure If I am missing anything here., correct me if I am wrong

With this solution will we have sequence number populated for first file because my grouping will being when I find a record with BEGIN

As per my understanding only second file will have the sequence number populated and out put will be both files concatenated.

I tried this and its not giving me the expected result

It worked for me when I changed IFTHEN as below

Code: Select all

INREC IFTHEN=(WHEN=GROUP,END=(1,5,CH,EQ,C'BEGIN'),  
                           PUSH=(81:SEQ=8))
Thanks,
Chandan
Imran Lamba
Registered Member
Posts: 13
Joined: Tue Jan 19, 2016 11:49 am

Re: Merge records from two file, one by one.

Post by Imran Lamba »

JOIN worked for me. As of now I have equal number of records.
User avatar
Magesh_j
Registered Member
Posts: 33
Joined: Sun Sep 04, 2016 8:50 pm

Re: Merge records from two file, one by one.

Post by Magesh_j »

Chandan Yadav wrote: Not sure If I am missing anything here., correct me if I am wrong

With this solution will we have sequence number populated for first file because my grouping will being when I find a record with BEGIN

As per my understanding only second file will have the sequence number populated and out put will be both files concatenated.

I tried this and its not giving me the expected result

It worked for me when I changed IFTHEN as below

CODE: SELECT ALL
INREC IFTHEN=(WHEN=GROUP,END=(1,5,CH,EQ,C'BEGIN'), 
                           PUSH=(81:SEQ=8))
You might have missed first BEGIN record in your testing, but still if you have a solution without having first begin statement, then you are good.

Regards,
Magesh
Chandan Yadav
Website Team
Website Team
Posts: 70
Joined: Wed Jul 31, 2013 10:19 pm

Re: Merge records from two file, one by one.

Post by Chandan Yadav »

Magesh_j wrote: You might have missed first BEGIN record in your testing, but still if you have a solution without having first begin statement, then you are good.

Regards,
Magesh
Yup you are correct..my bad..I overlooked the first begin statement

[ Post made via Android ] Image
Imran Lamba
Registered Member
Posts: 13
Joined: Tue Jan 19, 2016 11:49 am

Re: Merge records from two file, one by one.

Post by Imran Lamba »

Thanks Magesh for your help.
Post Reply

Create an account or sign in to join the discussion

You need to be a member in order to post a reply

Create an account

Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute

Register

Sign in

Return to “IBM DFSort, ICETOOL, ICEMAN, ICEGENER.”