Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Matching records from both the input files

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
i413678
Currently Banned

Active User


Joined: 19 Feb 2005
Posts: 112
Location: chennai

PostPosted: Fri Jun 22, 2007 7:22 pm    Post subject: Matching records from both the input files
Reply with quote

I have a requirement like following...

1. I have two input files

and I want 3 output files as follows.....

1. first file should contain the matching records from both the input files.
2. second file should contain the records which are present in first input file and not present in the second input file.
3. third file should contain the records which are present in second input file but not in the first input file.

pavan
Back to top
View user's profile Send private message

superk

Moderator Team Head


Joined: 26 Apr 2004
Posts: 4648
Location: Raleigh, NC, USA

PostPosted: Fri Jun 22, 2007 7:26 pm    Post subject: Re: matching 2 files
Reply with quote

pavan, I know you've been a Forum Member for a while now, so you should know what is required for a post such as this.

You need to specify:

The input dataset RECFM and LRECL.
The output datasets required RECFM and LRECL.
The criteria necessary for determing the matching records.
How you want duplicates (if any) handled.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Fri Jun 22, 2007 7:35 pm    Post subject:
Reply with quote

See the "Create files with matching and non-matching records" Smart DFSORT Trick at:

http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Fri Jun 22, 2007 7:46 pm    Post subject: Re: matching 2 files
Reply with quote

Accoding to below JCL,
I/p file ,LRECL=20,FB
Code:

//TESTICTL  EXEC PGM=ICETOOL                                         
//SYSPRINT  DD SYSOUT=*                                               
//TOOLMSG   DD SYSOUT=*                                               
//DFSMSG    DD SYSOUT=*                                               
//SYSABEND  DD   SYSOUT=*                                             
//UT01      DD   DSN=I/P FIle1(LRECL=20,FB),           
//    DISP=SHR                                                       
//UT02     DD   DSN=I/P FIle2(LRECL=20,FB),                       
//   DISP=SHR                                                         
//T1        DD DISP=(,PASS),                                         
//          SPACE=(TRK,(2,5),RLSE),                                   
//          DCB=(RECFM=FB,LRECL=85,BLKSIZE=8500)                     
 
         
//UT11  DD  DSN=O/P FIle1(LRECL=20,FB), DISP=(,CATLG), 
//      SPACE=(TRK,(2,5),RLSE),                                       
//      DCB=(RECFM=FB,LRECL=20,BLKSIZE=2000)                           
//UT12  DD  DSN=O/P FIle2(LRECL=20,FB), ,DISP=(,CATLG),   
//     SPACE=(TRK,(2,5),RLSE),                                         
//     DCB=(RECFM=FB,LRECL=20,BLKSIZE=2000)                             
//UT13  DD  O/P FIle3(LRECL=20,FB), DISP=(,CATLG),   
//     SPACE=(TRK,(2,5),RLSE),                                         
//     DCB=(RECFM=FB,LRECL=20,BLKSIZE=2000)                             
//TOOLIN    DD *                                                       
  SORT FROM(UT01) TO(T1) USING(CTL1)                                   
  SORT FROM(UT02) TO(T1) USING(CTL2)                                   
  SORT FROM(T1) USING(CTL3)                                       
//CTL1CNTL DD  *                                                       
  INREC FIELDS=(1,20,C'00001')                                         
  SORT FIELDS=(1,12,CH,A)                                               
//CTL2CNTL  DD  *                                                       
  INREC FIELDS=(1,20,C'00002')                                         
  SORT FIELDS=(1,12,CH,A)                                               
//CTL3CNTL  DD  *                                                       
  SORT FIELDS=(1,12,CH,A)                                               
  SUM FIELDS=(21,5,ZD)                                     
  OUTFIL FNAMES=UT11,INCLUDE=(21,5,CH,EQ,C'00001'),       
                       OUTREC=(1,20)                       
  OUTFIL FNAMES=UT12,INCLUDE=(21,5,CH,EQ,C'00002'),       
                       OUTREC=(1,20)                       
  OUTFIL FNAMES=UT13,INCLUDE=(21,5,CH,EQ,C'00003'),       
                       OUTREC=(1,20)                       
/*                             


O/P file1 - Record exists only in first input file1
O/P file2 - Record exists only in second input file2
O/P file3 - Record exists both the file
update this JCL according to ur I/P files LRECL


if u hav any query..let me know
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Fri Jun 22, 2007 8:01 pm    Post subject:
Reply with quote

Note: ibmmainframesyntel's solution good if there are only 1 pair of matching records.
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Mon Jun 25, 2007 9:29 am    Post subject: Re: Matching records from both the input files
Reply with quote

Hi Krisprems,
It will work accoding to his ewquirements.

solution good if there are only 1 pair of matching records.

What is it mean?
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Mon Jun 25, 2007 11:57 am    Post subject: Re: Matching records from both the input files
Reply with quote

Yes ibmmainframesyntel, to answer your question
Quote:
solution good if there are only 1 pair of matching records.

What is it mean?

Consider the i/p's
UT01
Code:
VICKY 
FRANK 
CARRIE
HOLLY 
PAUL   

UT02
Code:
KAREN   
HOLLY   
CARRIE   
VICKY   
VICKY   
MARY     


Here, since the record "VICKY" from file-1 has two matches from the second file.
When you do
Code:
SUM FIELDS=(21,5,ZD)   

The SUM will be greater than 00003.
So, the OUTFIL statement
Code:
OUTFIL FNAMES=UT13
, will not select this particular occurance of the record VICKY.

However, the TOOLIN card, that you have suggested works for 1 pair of matching records, it can be more efficient if SORT is avoided and a COPY operator could replace it, like this

Code:
//TOOLIN   DD *                                   
  COPY FROM(UT01) TO(T1) USING(CTL1)               
  COPY FROM(UT02) TO(T1) USING(CTL2)               
  SORT FROM(T1) USING(CTL3)                       
/*                                                 
//CTL1CNTL DD  *                                   
  INREC FIELDS=(1,20,C'00001')                     
/*                                                 
//CTL2CNTL  DD  *                                 
  INREC FIELDS=(1,20,C'00002')                     
/*                                                 
//CTL3CNTL  DD  *                                 
  SORT FIELDS=(1,12,CH,A)                         
  SUM FIELDS=(21,5,ZD)                             
  OUTFIL FNAMES=UT11,INCLUDE=(21,5,CH,EQ,C'00001'),
                       OUTREC=(1,20)               
  OUTFIL FNAMES=UT12,INCLUDE=(21,5,CH,EQ,C'00002'),
                       OUTREC=(1,20)               
  OUTFIL FNAMES=UT13,INCLUDE=(21,5,CH,EQ,C'00003'),
                       OUTREC=(1,20)               


Though i413678, hasn't stated any where, saying there are 1 or more matching records, it would be better if we are giving a generalised solution.
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Mon Jun 25, 2007 12:16 pm    Post subject: Re: Matching records from both the input files
Reply with quote

Hi Kispems,
yes u r correct.
No problem i can change my control card like this.

OUTFIL FNAMES=UT13,INCLUDE=(21,5,CH,GE,C'00003'),
OUTREC=(1,20)


Hope now it will work perfectly.

thanks a lot...
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Mon Jun 25, 2007 12:48 pm    Post subject: Re: Matching records from both the input files
Reply with quote

That's great ibmmainframesyntel,
Quote:
No problem i can change my control card like this.

OUTFIL FNAMES=UT13,INCLUDE=(21,5,CH,GE,C'00003'),
OUTREC=(1,20)


Was, expecting this from you!

But, for your question
Quote:
Hope now it will work perfectly.


Take, this scenario
UT01
Code:

FRANK 
CARRIE
HOLLY 
PAUL 


UT02
Code:
KAREN   
HOLLY   
CARRIE   
VICKY   
VICKY   
MARY 


Here though the record VICKY, has no match in FILE-1, it will be listed in UT13(which should not be).
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Mon Jun 25, 2007 1:21 pm    Post subject: Re: Matching records from both the input files
Reply with quote

Hi Krisprems,
yes u r correct.
If input file contains duplicate records,then that jcl will not work.

i have to go for other logic.

Hi i413678,
let us knw weather input files contains duplicate records or not.

expecting ur reply...
Back to top
View user's profile Send private message
nankrish

New User


Joined: 11 Nov 2005
Posts: 26

PostPosted: Tue Jun 26, 2007 7:46 pm    Post subject:
Reply with quote

will the above method is applicable for VSAM files as well?
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Tue Jun 26, 2007 8:23 pm    Post subject:
Reply with quote

nankrish
check this link
http://ibmmainframes.com/viewtopic.php?t=22270
Back to top
View user's profile Send private message
i413678
Currently Banned

Active User


Joined: 19 Feb 2005
Posts: 112
Location: chennai

PostPosted: Thu Jun 28, 2007 5:09 pm    Post subject:
Reply with quote

hi,

The input files may or may not contain duplicates.

Regards,
pavan
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Thu Jun 28, 2007 5:11 pm    Post subject:
Reply with quote

pavan,

Please refer krisprems post above. That is the tested method.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Thu Jun 28, 2007 5:45 pm    Post subject:
Reply with quote

i413678
Look at Smart DFSORT Trick, as i have suggested above
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us