MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Joinkeys to compare VB file

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54
PostPosted: Mon Nov 19, 2018 12:25 pm    Post subject: Joinkeys to compare VB file Reply with quote
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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12368
Topics: 75
Location: San Jose
PostPosted: Mon Nov 19, 2018 3:50 pm    Post subject: Reply with quote
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
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12368
Topics: 75
Location: San Jose
PostPosted: Mon Nov 19, 2018 5:17 pm    Post subject: Reply with quote
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
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54
PostPosted: Tue Nov 20, 2018 11:05 am    Post subject: Reply with quote
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
View user's profile Send private message
Arun Raj
Sort Forum Moderator
Sort Forum Moderator


Joined: 14 Jun 2017
Posts: 7
Topics: 1
PostPosted: Wed Nov 21, 2018 8:51 am    Post subject: Reply with quote
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
View user's profile Send private message
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54
PostPosted: Tue Dec 11, 2018 11:34 am    Post subject: Reply with quote
Hi Arun,

Thanks for the kind suggestion.

Regards,
Magesh
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group