Record Splicing Using DFSORT

pritamdebnath · Posted: Sun Jun 24, 2007 1:58 pm

Hi,

I have two input files, namely, employee and department -

Structure of Employee File:

Emp# Emp Name
1 Harris
2 Chris
2 Frank
3 Borris

Structure of department File:

Emp# Department
1 marketing
2 purchasing
4 merchandise

The output file should be like

Emp# Emp Name Department
1 Harris Marketing
2 Chris Purchasing
2 Frank Purchasing
3 Borris ------------
4 ------- Merchandise

Can somebody please give me a DFSORT code snippet to achieve this?

ibmmainframesyntel · Posted: Mon Jun 25, 2007 4:54 pm

LRECL,RECFM of both the files?
Field size of the all the fields?

pritamdebnath · Posted: Mon Jun 25, 2007 5:50 pm

LRECL = 80
RECFM = FB

Record Structure -

Input File 1:

Position 1,3: Emp No
Postion 4,20:Employee Name
Position 24,56: Filler

Input File 2:

Position 1,3: Emp No
Position 4,20: Department
Position 24,56: Filler

Frank Yaeger · Posted: Mon Jun 25, 2007 9:22 pm

Here's a DFSORT/ICETOOL job that will do what you asked for:

pritamdebnath · Posted: Wed Jun 27, 2007 8:39 am

Thanks Frank - the solution is working. But there is a slight problem here -
Let me give a scenario -

File1 - Employee

Emp# Employee
1 Frank
1 Harris
2 Chris
3 Raju
4 Nelson

File2 - Department

Emp# Department
1 Marketing
2 Purchasing
2 AP
4 Buying
5 Merchandizing

Now I want the output file to come like this -

Emp# Name Department
1 Frank Marketing
1 Harris Marketing
2 Chris Purchasing
2 Chris AP
3 Raju
4 Nelson Buying
5 Merchandizing

But when I executed the job given, I got the following output -

Emp# Name Department
1 Frank Marketing
1 Harris Marketing
2 Purchasing
2 Chris Purchasing
3 Raju
4 Nelson Buying
5 Merchandizing

So basically the n-n mapping is not coming, but n-1 or 1-n mapping is comming. Can you please help?

Frank Yaeger · Posted: Wed Jun 27, 2007 8:07 pm

In your original example, there were duplicates in the Employee file but no duplicates in the Department file. My solution was based on that setup and works for that setup. Now you've changed the requirements so that you have duplicates in both files. That's a more complicated situation and for all I know the data is even more complicated than you've shown, so I can't give you a solution.

Frank Yaeger · Posted: Tue Nov 24, 2009 2:17 am

With z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707 (Nov, 2009), you can now use JOINKEYS to do an n-n join. For example: