Problem joining fields from two files on a key

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

Post Reply
papzz
Member
Posts: 5
Joined: Fri Jun 03, 2011 10:49 pm

Problem joining fields from two files on a key

Post by papzz » Fri Jun 03, 2011 11:23 pm

Hi ,

My requirement is to join two files based on a key.
I am using the following job

//JK1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=File 1,DISP=SHR
//SORTJNF2 DD DSN=File 2,DISP=SHR
//SORTOUT DD DSN=File 3,
// DISP=(NEW,CATLG,DELETE)
//
//*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,7,A)
JOINKEYS FILES=F2,FIELDS=(1,7,A)
REFORMAT FIELDS=(F1:1,58,F2:7,138)
SORT FIELDS=COPY
OUTFIL FNAMES=SORTOUT,BUILD=(1,58,X,59,138)
/*


File 1 and 2 are both VB files
File 1, LRECL=58
File 2, LRECL=145

example::
if file1
001 AAAAA
002 CCCCC
003 EEEEE
004 GGGGG

file2
001 BBBBB
003 DDDDD
004 FFFFF
005 HHHHH

file 3 should be
001 AAAAA BBBBB
003 EEEEE DDDDD
004 GGGGG FFFFF

In my case the key is a 7 digit packed decimal on both the files starting from position 1 .
I have to join the 2nd file based on the key to the end of the 1st file.

When I use the above JCL, all i get is an empty file inspite of matched records.
Could someone please help?? :cry:

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Sat Jun 04, 2011 1:02 am

You have several problems. You say your input files are VB, but you don't seem to be treating them that way. If the key is a 7-digit PD value, that would take 4 bytes. So your records would look like this:

rrrrppppdatan...

rrrr is the 4-byte RDW.
pppp is the 4-byte PD value.
data is the rest of the data in your record.

If you want to create an FB record with the following:

ppppdata1...bdata2...

you would use a DFSORT JOINKEYS job something like this:

Code: Select all

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  input file1 (VB/58)
//IN2 DD DSN=...  input file2 (VB/145)
//SORTOUT DD DSN=...  output file (FB/192)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(5,4,A)
  JOINKEYS F2=IN2,FIELDS=(5,4,A)
  REFORMAT FIELDS=(F1:1,58,F2:9,137)
  OPTION COPY
  OUTFIL BUILD=(5,54,X,59,137)
/*
If that's NOT what you want, then you need to do a better job of explaining what you do want.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

papzz
Member
Posts: 5
Joined: Fri Jun 03, 2011 10:49 pm

Post by papzz » Mon Jun 06, 2011 9:27 am

Hi Frank,

Thanks for thr response.
I want the output file also in VB.
How could I do that?

papzz
Member
Posts: 5
Joined: Fri Jun 03, 2011 10:49 pm

Post by papzz » Mon Jun 06, 2011 11:36 am

Hi,

I could do it.

//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(5,7,A)
JOINKEYS FILES=F2,FIELDS=(5,7,A)
REFORMAT FIELDS=(F1:1,58,F2:13)
OPTION COPY
/*

Thanks :D

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Mon Jun 06, 2011 10:31 pm

I want the output file also in VB.
How could I do that?
That would depend on what exactly the input records look like and what exactly you want the output records to look like.

Your input example only shows fixed fields with the VB records (that is, each record is the same length) - are they really fixed or are they actually variable (that is, records are different lengths)?

If you want more help, you need to show a better example of what the input records in each VB file look like (length/data) and what you want for the expected output records (length/data).
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others