MVSFORUMS.com A Community of and for MVS Professionals
View previous topic :: View next topic
Author
Message
Magesh_J Intermediate Joined: 21 Jun 2014 Posts: 259 Topics: 54
Posted: Mon Nov 19, 2018 12:25 pm Post subject: Joinkeys to compare VB file
Hi,
I have two VB files max length=954, i need output for unpaired,f1,f2 both.
I know to do for unpaired,F1
Code:
JOINKEYS F1=MAIN,FIELDS=(18,17,A)
JOINKEYS F2=LOOKUP,FIELDS=(18,17,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,4,?,F1:05)
OPTION COPY
OUTFIL FNAMES=MATCH,INCLUDE=(5,1,CH,EQ,C'B'),BUILD=(1,4,6)
OUTFIL FNAMES=NMATC1,INCLUDE=(5,1,CH,EQ,C'1'),BUILD=(1,4,6)
Please advise, how do I add F2 in reformat fields and in outfil statement.
thanks
Magesh
Back to top
kolusu Site Admin Joined: 26 Nov 2002 Posts: 12367 Topics: 75 Location: San Jose
Posted: Mon Nov 19, 2018 3:50 pm Post subject:
Magesh_J ,
You can have 2 VB files without tails on the REFORMAT statement, however then if your files are truly VB then you will have
RDW + File-1 VB data + File-2 VB data for matched records.
Do you need that?
It is easy to extract the unmatched records.
Here is an example of joining 2 vb files without a tail. Look at the output from step0100.
Step0050 creates 2 input vb files
INA - VB file will have first 4 records
INB - VB file will have the next 4 records
Code:
//***********************************************************
//* CREATE 2 VB FILES FROM SAMPLE INPUT **
//* INA = 1-4 RECORDS **
//* INB = 5-8 RECORDS **
//***********************************************************
//STEP0050 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
B F1VBDATA
CC F1VBDATA
AAA F1VBDATA
ZZZZZZZZZZZZZ F1VBDATA
AAA F2VBDATA
B F2VBDATA
DDDDD F2VBDATA
CC F2VBDATA
//INA DD DSN=&&INA,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//INB DD DSN=&&INB,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//SYSIN DD *
OPTION COPY
INREC OVERLAY=(950:X)
OUTFIL FNAMES=INA,FTOV,VLTRIM=C' ',ENDREC=4
OUTFIL FNAMES=INB,FTOV,VLTRIM=C' ',STARTREC=5
//*
//***********************************************************
//* RUN JOINKEYS ON 2 VB FILES AND CREATE A VB OUTPUT FILE **
//***********************************************************
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD DISP=SHR,DSN=&&INA
//INB DD DISP=SHR,DSN=&&INB
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(18,17,A)
JOINKEYS F2=INB,FIELDS=(18,17,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,4,?,F1:5,F2:5)
//*
Code:
The keys to be matched are
AAA - has a matching key in file 2
B - has a matching key in file 2
CC - has a matching key in file 2
DDDDD - Unique key from file 2
ZZZZZZZZZZZZZ - Unique key from file 1
_________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu
Back to top
kolusu Site Admin Joined: 26 Nov 2002 Posts: 12367 Topics: 75 Location: San Jose
Posted: Mon Nov 19, 2018 5:17 pm Post subject:
Magesh_J ,
If you want the Matched records to only have the records from one of the file, then you can use the following JCL
Code:
//***********************************************************
//* RUN JOINKEYS ON 2 VB FILES AND CREATE A VB OUTPUT FILE **
//***********************************************************
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD DISP=SHR,DSN=&&INA
//INB DD DISP=SHR,DSN=&&INB
//BOTH DD SYSOUT=*
//ONLYF1 DD SYSOUT=*
//ONLYF2 DD SYSOUT=*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(18,17,A)
JOINKEYS F2=INB,FIELDS=(18,17,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,4,?,F1:5,F2:5)
OUTFIL FNAMES=BOTH,VLTRIM=C' ',
INCLUDE=(5,1,CH,EQ,C'B'),BUILD=(1,4,6,950)
OUTFIL FNAMES=ONLYF1,VLTRIM=C' ',
INCLUDE=(5,1,CH,EQ,C'1'),BUILD=(1,4,6,950)
OUTFIL FNAMES=ONLYF2,VLTRIM=C' ',
INCLUDE=(5,1,CH,EQ,C'2'),BUILD=(1,4,6)
//*
//JNF1CNTL DD *
INREC IFTHEN=(WHEN=(1,2,BI,LE,953),OVERLAY=(954:X))
//*
_________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu
Back to top
Magesh_J Intermediate Joined: 21 Jun 2014 Posts: 259 Topics: 54
Posted: Tue Nov 20, 2018 11:05 am Post subject:
Hi Kolusu,
Thank you very much your solution is working great, I did a small modification
LRECL=958 including RDW, sorry about that.
Code:
//SYSIN DD *
JOINKEYS F1=MAIN,FIELDS=(18,17,A)
JOINKEYS F2=LOOKUP,FIELDS=(18,17,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,4,?,F1:05,F2:05)
OPTION COPY
OUTFIL FNAMES=MATCH,INCLUDE=(5,1,CH,EQ,C'B'),BUILD=(1,4,6,954),
VLTRIM=C' '
OUTFIL FNAMES=NMATC1,INCLUDE=(5,1,CH,EQ,C'1'),BUILD=(1,4,6,954),
VLTRIM=C' '
OUTFIL FNAMES=NMATC2,INCLUDE=(5,1,CH,EQ,C'2'),BUILD=(1,4,6,954),
VLTRIM=C' '
//JNF1CNTL DD *
INREC IFTHEN=(WHEN=(1,2,BI,LE,957),OVERLAY=(958:X))
//JNF2CNTL DD *
INREC IFTHEN=(WHEN=(1,2,BI,LE,957),OVERLAY=(958:X))
Following code leads to 1912 LRECL.
Code:
OUTFIL FNAMES=ONLYF2,VLTRIM=C' ',
INCLUDE=(5,1,CH,EQ,C'2'),BUILD=(1,4,6)
So I changed it as follows
Code:
OUTFIL FNAMES=NMATC2,INCLUDE=(5,1,CH,EQ,C'2'),BUILD=(1,4,6,954),
VLTRIM=C' '
When i add above code, the job failed with short records, hence added following code too.
Code:
//JNF2CNTL DD *
INREC IFTHEN=(WHEN=(1,2,BI,LE,957),OVERLAY=(958:X))
thanks
Magesh
Back to top
Arun Raj Sort Forum Moderator Joined: 14 Jun 2017 Posts: 7 Topics: 1
Posted: Wed Nov 21, 2018 8:51 am Post subject:
Or since we are padding the file-1 record anyways, maybe we can change the REFORMAT and the ONLYF2 BUILD as follows:
Code: //SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(18,17,A)
JOINKEYS F2=INB,FIELDS=(18,17,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,4,?,F1:5,954,F2:5)
OUTFIL FNAMES=BOTH,VLTRIM=C' ',
INCLUDE=(5,1,CH,EQ,C'B'),BUILD=(1,4,6,954)
OUTFIL FNAMES=ONLYF1,VLTRIM=C' ',
INCLUDE=(5,1,CH,EQ,C'1'),BUILD=(1,4,6,954)
OUTFIL FNAMES=ONLYF2,VLTRIM=C' ',
INCLUDE=(5,1,CH,EQ,C'2'),BUILD=(1,4,960)
//*
//JNF1CNTL DD *
INREC IFTHEN=(WHEN=(1,2,BI,LE,957),OVERLAY=(958:X))
Back to top
Magesh_J Intermediate Joined: 21 Jun 2014 Posts: 259 Topics: 54
Posted: Tue Dec 11, 2018 11:34 am Post subject:
Hi Arun,
Thanks for the kind suggestion.
Regards,
Magesh
Back to top
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum