Page 1 of 1

Join Key for matched records two VB files

PostPosted: Tue Oct 11, 2011 9:04 pm
by Naveen@Uppi
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

Re: Join Key for matched records two VB files

PostPosted: Tue Oct 11, 2011 11:45 pm
by Frank Yaeger
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.

Re: Join Key for matched records two VB files

PostPosted: Wed Oct 12, 2011 7:12 pm
by Naveen@Uppi
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
/*

Re: Join Key for matched records two VB files

PostPosted: Wed Oct 12, 2011 10:38 pm
by Frank Yaeger
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.

Re: Join Key for matched records two VB files

PostPosted: Mon Oct 17, 2011 2:15 pm
by Naveen@Uppi
Thanks for the solution.

Regards,
Naveen N