IBM Mainframe Forum

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 :


File2 has record:



I ran below JCL :




But I received below data in third file :

the output I want is :



Can someone please let me know how to do it?


Thanks

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.

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?

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.

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):



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.

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.

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.

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.