Join Two records



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

Join Two records

Postby abhaykothiyal » Wed Mar 22, 2017 11:47 pm

Hi I want to join two files and then want to keep the unpaired records into third files using sort Join JCL.

File1 has record :
0000000000001111111111111222222222222


File2 has record:
CALLLL    



I ran below JCL :

//STEP04   EXEC PGM=SORT                                              
//SYSPRINT DD SYSOUT=*                                                
//SYSOUT  DD SYSOUT=*                                                
//SORTJNF1 DD DSN=T5355.ABHAY.RAVI.INPUTF1,DISP=SHR                  
//SORTJNF2 DD DSN=T5355.ABHAY.RAVI.INPUTF2,DISP=SHR                  
//SORTOUT DD DSN=T5355.ABHAY.RAVI.OUTPUT,                            
//           DISP=(NEW,CATLG,DELETE),                                
//           SPACE=(CYL,(100,200),RLSE),                              
//           DCB=(LRECL=240,RECFM=FB,BLKSIZE=0)                                                                      
//SYSIN DD *                                                          
   JOINKEYS FILES=F1,FIELDS=(1,5,A)                                  
   JOINKEYS FILES=F2,FIELDS=(1,5,A)                                  
   JOIN UNPAIRED,F1,F2,ONLY                                          
   REFORMAT FIELDS=(F1:1,38,F2:1,6)                                  
   SORT FIELDS=COPY                                                  



But I received below data in third file :

                                                                  CALLLL    
0000000000001111111111111222222222222
 

the output I want is :
0000000000001111111111111222222222222CALLLL    



Can someone please let me know how to do it?


Thanks
abhaykothiyal
 
Posts: 3
Joined: Wed Mar 22, 2017 11:37 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join Two records

Postby NicC » Thu Mar 23, 2017 12:03 am

Not by posting in the JCL section when you are asking a sort question. I will move it to DFSort.

You should also post your code and data and anything else that appears on your mainframe screen using the code tags. I will do this for you this time.

And note that they are NOT files but data sets.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Join Two records

Postby BillyBoyo » Thu Mar 23, 2017 12:18 am

To get a join, data in the keys has to match. In the key you specify (position one of a length of five for both files) there is no match, so you don't get a match.

Perhaps you can explain more clearly what you are trying to do?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Join Two records

Postby abhaykothiyal » Thu Mar 23, 2017 12:21 am

Hi,

I do not want matched records. I want only unmatched records. Since both files have single and unmatched records, both records will be fetched into output file.

My requirement is that I want these two records into one record not two records. I need these two records joined one after another.
abhaykothiyal
 
Posts: 3
Joined: Wed Mar 22, 2017 11:37 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join Two records

Postby Aki88 » Thu Mar 23, 2017 5:21 pm

Hello,

There is incorrect usage of terminology here.
When you say:
abhaykothiyal wrote:Hi I want to join two files and then want to keep the unpaired records into third files using sort Join JCL. ....

Then the phrase "join two files" means something entirely different in DFSORT terminology.

Aside, if we understand the requirement right, if DS-A has a record as '1234' and DS-B has a record '5678', the output you're looking for is: '12345678'; correct me if this understanding is wrong.

If you are looking for the above output, then:
a. Are the datasets in question, of fixed length or variable?
b. What is the length of data per record in question; meaning, say LRECL is 80 bytes, but actual data is only of 30 bytes?
c. Is the data ALWAYS AND ONLY AND ONLY of fixed number of bytes, say 38 and 6 bytes, as you've shown? Or it can in some cases be more, such as 40 bytes in one DS and 10 bytes in other?
d. Is the mapping of data between the two datasets always going to be one-to-one, i.e. 1st record from DS-1 needs to be appended to 1st record of DS-2, and so on?
e. Will the two datasets always have only one record, or can it be more in one dataset and less in another? If so then how do you want the data appended?

Going purely by the example you've shown AND modifying your JCL slightly (assuming LRECL=80, RECFM=FB for both input datasets):


//SORTJNF1 DD *                              
0000000000001111111111111222222222222        
/*                                          
//SORTJNF2 DD *                              
CALLLL                                      
/*                                          
//SORTOUT  DD SYSOUT=*                      
//SYSIN DD *                                
 JOINKEYS FILES=F1,FIELDS=(39,1,A)          
 JOINKEYS FILES=F2,FIELDS=(7,1,A)            
 REFORMAT FIELDS=(F1:1,37,F2:1,6)            
 SORT FIELDS=COPY                            
/*                                          
 


You can also look at RESIZE operator of ICETOOL if there is only 1 record in both the DS. A simple DD concatenation of your input DS and RESIZE, will do the trick.
Btw, this is very similar to this post.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Join Two records

Postby abhaykothiyal » Thu Mar 23, 2017 6:17 pm

Thank for providing this solution. But this jcl is applicable to extract only common/paired records. My requirement is to join and extract unpaired records into third file into single record.

You can take any type of file vf or fb. Any assumptions you can hav but the requirement is to join two file and keep unpaired records into third file as a single record.

Here In my example I took only one record and different record in each file to keep it easy so that both records should be pulled into third file as a single record.
abhaykothiyal
 
Posts: 3
Joined: Wed Mar 22, 2017 11:37 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join Two records

Postby NicC » Thu Mar 23, 2017 7:02 pm

When describing a requirement you must provide full information. This includes LRECL, RECFM, representative sample input and the expected output. Solutions can vary depending on whether the data set is FB or VB. The solution can vary depending on whether there will always be one record in each data set (note: not file) or there can be several and whether they have keys and will aways/never/sometimes have matching records in the second data set.

So, fully describe the attributes of your input data sets and the required attributes of your output data sets. And provide representative examples of your input and expected output.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Join Two records

Postby Aki88 » Thu Mar 23, 2017 8:31 pm

Hello,

abhaykothiyal wrote:Thank for providing this solution. But this jcl is applicable to extract only common/paired records. My requirement is to join and extract unpaired records into third file into single record. ...


Kindly read and understand the solution in my post again.
The solution takes the input records You'd shown, as-is, in input and appends DS-2's record to DS-1's record. It FORCEFULLY pairs the records from both the datasets, by considering a column which has spaces in both datasets. You can use INREC in JOINKEYS and pad spaces or a sequence number at the beginning of record, JOIN on this number, drop it in OUTFIL processing.

abhaykothiyal wrote:...You can take any type of file vf or fb. Any assumptions you can hav but the requirement is to join two file and keep unpaired records into third file as a single record....


You really need to understand that the requirement is YOURS, and only YOU know the rules that will eventually govern the solution. Of course we can code anything for that matter, and going by your reply maybe you will use it as-is without understanding it and later complain that it did not comply to a scenario which we never were aware of to begin with.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post