Page 1 of 1

SyncSort Problem

PostPosted: Thu Jul 14, 2016 5:21 pm
by JohnDev
Hi All,

I have a task that sounds incredibly simple but I cannot get to work. I have 2 input files, FB, LRECL 80. They both have a composite key (1,8,A,17,9,A).

I have to merge these two files into a single output. In the event of a duplicate keys then only the input from file 2 should be written to output.

This card outputs the duplicates from file 2, but not the unmatched records. Any help or advice would be greatly appreciated

//SYSIN    DD *                          
  JOINKEYS FILE=F1,FIELDS=(1,8,A,17,9,A)
  JOINKEYS FILE=F2,FIELDS=(1,8,A,17,9,A)
  JOIN UNPAIRED,F2                      
  REFORMAT FIELDS=(F2:1,80)              
  SORT FIELDS=COPY                      
/*                                      


Thanks

Coded for you - please do it yourself next time

Re: SyncSort Problem

PostPosted: Thu Jul 14, 2016 6:26 pm
by Aki88
Hello,

Purely out of curiosity, have you tried 'MERGE FIELDS' coupled with a 'SUM FIELDS=NONE'; if I have understood the problem correctly, that should give you what you're looking for.
Can you please, also share some representational data for input/s and output.

Re: SyncSort Problem

PostPosted: Thu Jul 14, 2016 6:35 pm
by JohnDev
Hi Aki,

Thanks for getting back to me. I have tried 'MERGE FIELDS' coupled with a 'SUM FIELDS=NONE' but it doesn't keep the required duplicates from the second output. As an example

F1:
xxxxxxx FUL_USER <--- Key matches first record in F2. This record should be discarded
xxxxxxx GD1_USER115304594FWA
yyyyyyy FUL_USER

F2:
xxxxxxx GBL_USER <--- Key matches first record in F1. This record should be kept
LLLLLLL GD1_USER115303234FWA
ZZZZZZ FUL_USER

Desired Output
xxxxxxx GBL_USER
xxxxxxx GD1_USER115304594FWA
yyyyyyy FUL_USER
LLLLLLL GD1_USER115303234FWA
ZZZZZZ FUL_USER

Hope this helps!

Re: SyncSort Problem

PostPosted: Thu Jul 14, 2016 7:22 pm
by Aki88
Hello,

Add EQUALS to it and see if it works for you; here is what I tried:

File 1:

BUDDY GUY          
JIMMY FALLON        
STEVE123VAI        
1MACKERALSHAUN      
123JIMIBID          
1234HENDRIX        
123456              
123456              
123456THORN        
146RUPERT              
 


File 2:

FUNTIPERT                            
JIM4573ALLON                        
STEVE123VAI          DONKEY KONG    
1M37547ALSHAUN                      
12GAIJINID                          
1234HENDRIX                          
123456                              
1245727                              
245JIMIBID                          
658DY GUY                            
 


SYSIN:

MERGE FIELDS=(1,2,CH,A,6,2,CH,A),EQUALS
SUM FIELDS=NONE                        
 


When the IN1 DS is FILE-1, SORTOUT:


BUDDY GUY                
FUNTIPERT                
JIMMY FALLON              
JIM4573ALLON              
STEVE123VAI              
1MACKERALSHAUN            
1M37547ALSHAUN            
1234HENDRIX              
12GAIJINID                
123JIMIBID                
1245727                  
123456                    
123456THORN              
146RUPERT                
245JIMIBID                
658DY GUY                
 


When the IN1 DS is FILE-2, SORTOUT:


BUDDY GUY                          
FUNTIPERT                          
JIMMY FALLON                      
JIM4573ALLON                      
STEVE123VAI          DONKEY KONG  
1MACKERALSHAUN                    
1M37547ALSHAUN                    
1234HENDRIX                        
12GAIJINID                        
123JIMIBID                        
1245727                            
123456                            
123456THORN                        
146RUPERT                          
245JIMIBID                        
658DY GUY                          
 


Hope this helps.

Edit: Apologies for the tacky input records, cooked them while listening to some good ol' Blues.

Re: SyncSort Problem

PostPosted: Thu Jul 14, 2016 8:00 pm
by JohnDev
Thanks Aki.

That did work, the only issue is that my inputs are unsorted so to use this approach I would need 2 sort steps before the merge. I can achieve what I need with 2 steps and a merge using joinkeys as well. Is there anyway to achieve this in one step?

Thanks again for your time and help

Re: SyncSort Problem

PostPosted: Thu Jul 14, 2016 8:14 pm
by Aki88
Hello,

I want to avoid that JOINKEYS ;)
Okies, here goes nothing (my input DS were unsorted this time; I simply shuffled the records):

Input:

//IN       DD DISP=(OLD,DELETE,DELETE),DSN=&&TEMP2
//         DD DISP=(OLD,DELETE,DELETE),DSN=&&TEMP1
 


Using:

//TOOLIN   DD *                                    
 SELECT FROM(IN) TO(OUT) ON(1,2,CH) ON(6,2,CH) FIRST
/*                                                  
 


Gives:

BUDDY GUY                          
FUNTIPERT                          
JIMMY FALLON                        
JIM4573ALLON                        
STEVE123VAI          DONKEY KONG    
1MACKERALSHAUN                      
1M37547ALSHAUN                      
1234HENDRIX                        
12GAIJINID                          
123JIMIBID                          
1245727                            
123456                              
123456THORN                        
146RUPERT                          
245JIMIBID                          
658DY GUY                          
 


Whereas, with:

//IN       DD DISP=(OLD,DELETE,DELETE),DSN=&&TEMP1
//         DD DISP=(OLD,DELETE,DELETE),DSN=&&TEMP2
 


Gives:

BUDDY GUY            
FUNTIPERT            
JIMMY FALLON        
JIM4573ALLON        
STEVE123VAI          
1MACKERALSHAUN      
1M37547ALSHAUN      
1234HENDRIX          
12GAIJINID          
123JIMIBID          
1245727              
123456              
123456THORN          
146RUPERT            
245JIMIBID          
658DY GUY            
 


Hope this is what you're looking for.

Edit: This was tested on ICETOOL; so I guess SYNCTOOL should work for you; can you test it once please.

Re: SyncSort Problem

PostPosted: Thu Jul 14, 2016 9:27 pm
by JohnDev
Aki,

That ran with no issue using SYNCTOOL and produced the required output. Thanks again for your quick replies and considered effort.