IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Matching records from both the input files
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
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
 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

Global Moderator


Joined: 26 Apr 2004
Posts: 4652
Location: Raleigh, NC, USA
PostPosted: Fri Jun 22, 2007 7:26 pm
 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
 Reply with quote
See the "Create files with matching and non-matching records" Smart DFSORT Trick at:

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
 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
 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
 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
 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
 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
 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
 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
 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
 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
 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: 1436
Location: Bangalore,India
PostPosted: Thu Jun 28, 2007 5:11 pm
 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
 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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts COBOL sorting, with input GDG base COBOL Programming 7
No new posts 3 files concatenated to 1 DFSORT/ICETOOL 2
No new posts JCL sort to compare dates in two file... DFSORT/ICETOOL 2
No new posts combine multiple unique records into ... DFSORT/ICETOOL 2
No new posts Concatenate 2 input datasets and give... JCL & VSAM 2
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us