Join Key for matched records two VB files



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

Join Key for matched records two VB files

Postby Naveen@Uppi » Tue Oct 11, 2011 9:04 pm

We have different match techniques using JoinKeys mentioned below:
Match, FB, keys in same place, no duplicates
Match, key in different places, no duplicates
No match, FB, key in different places, no duplicates
No match, VB, key in different places, no duplicates
Match and no match, FB, key in same place, no duplicates
Match, FB, keys in different places, duplicates
No match, FB, keys in different places, duplicates
No match, VB, key in different places, duplicates

I wanted to know if there is code to fetch the mated records for the VB files:

Match, VB, keys in same place, no duplicates

Regards,
Naveen N
Naveen@Uppi
 
Posts: 36
Joined: Wed Jul 23, 2008 6:49 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join Key for matched records two VB files

Postby Frank Yaeger » Tue Oct 11, 2011 11:45 pm

You need to give more details. Please show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
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
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Join Key for matched records two VB files

Postby Naveen@Uppi » Wed Oct 12, 2011 7:12 pm

Hi Frank,

Sorrty for the incomplete information.

Input file 1 is VB file with Key starting at 43 length 10 and Record length of 32756.
Input file 2 is VB file with Key starting at 03 length 10 and Record length of 32756

Input file 1 has duplicates on key 43 and lenth 10.

The output i need it in File 2 format for the matching records between file 1 and 2.

I executed the below JCL and output file had zero records.

//STEP01 EXEC PGM=SORT
//SORTJNF1 DD DSN=ZJ00.SXXXX.XXXXX.COPY, //File 1 VB block
// DISP=SHR
//SORTJNF2 DD DSN=ZJ00.XXXX.XXXXX.COPY1, //File 2 VB block
// DISP=SHR
//SORTOUT DD DSN=ZJ00.PMCD516S.SEP29.MATCH, Output in VB
// DISP=(NEW,CATLG,DELETE),
// DATACLAS=DCALNL,
// MGMTCLAS=MCTSO,
// DCB=(SYS1.MODEL,RECFM=VB,LRECL=32756,BLKSIZE=0,BUFNO=99)
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(47,10,A) //Key start at 43 (since it VB added plus 4)
JOINKEYS FILE=F2,FIELDS=(7,10,A) //Key start at 3 (since it VB added plus 4)
REFORMAT FIELDS=(F2:1,4,32756) // Need matching records from File 2
SORT FIELDS=COPY
/*
Naveen@Uppi
 
Posts: 36
Joined: Wed Jul 23, 2008 6:49 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join Key for matched records two VB files

Postby Frank Yaeger » Wed Oct 12, 2011 10:38 pm

If I understand what you want, then an example of input and output would be:

Input file1 (VB)
rrrrF1R1                                      KEY0000002XXX
rrrrF1R2                                      KEY0000002XXXXXX
rrrrF1R3                                      KEY0000005XX
rrrrF1R4                                      KEY0000001XXXXXXXXXXXXX
rrrrF1R5                                      KEY0000003XX
rrrrF1R6                                      KEY0000003XX
rrrrF1R7                                      KEY0000003XXXXXXXXX
rrrrF1R8                                      KEY0000007XX
rrrrF1R9                                      KEY0000007XX
rrrrF1R10                                     KEY0000007XXXXXXXXX
rrrrF1R11                                     KEY0000009XXXXX


Input file2 (VB)
rrrrF2KEY0000004  F2R1 XXXXX
rrrrF2KEY0000005  F2R2 XXXXXXX
rrrrF2KEY0000002  F2R3 XXXX
rrrrF2KEY0000007  F2R4 XXXXXXXXXXX
rrrrF2KEY0000009  F2R5 XXXXXXXXXXXXX
rrrrF2KEY0000011  F2R6 XXXXXXXXXXXXX



Output file (VB)
rrrrF2KEY0000002  F2R3 XXXX
rrrrF2KEY0000005  F2R2 XXXXXXX
rrrrF2KEY0000007  F2R4 XXXXXXXXXXX
rrrrF2KEY0000009  F2R5 XXXXXXXXXXXXX


If that's what you want, then you can use a DFSORT JOINKEYS job like the following:

//STEP01  EXEC PGM=SORT
//SYSOUT    DD SYSOUT=*
//IN1 DD DSN=...  input file1 (VB)
//IN2 DD DSN=...  input file2 (VB)
//SORTOUT DD DSN=...  output file (VB)
//SYSIN     DD *
  JOINKEYS F1=IN1,FIELDS=(47,10,A)
  JOINKEYS F2=IN2,FIELDS=(7,10,A)
  REFORMAT FIELDS=(F2:1,4,5)
  OPTION COPY
/*
//JNF1CNTL DD *
  SUM FIELDS=NONE
/*


If that's not what you want then you need to do a better job of explaining what you do want with input and output examples.
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
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Join Key for matched records two VB files

Postby Naveen@Uppi » Mon Oct 17, 2011 2:15 pm

Thanks for the solution.

Regards,
Naveen N
Naveen@Uppi
 
Posts: 36
Joined: Wed Jul 23, 2008 6:49 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post