Compare 2 files by writing all matching records to output
Moderators: Frank Yaeger, Moderator Group
Compare 2 files by writing all matching records to output
I need to compare 2 files having data as follows
File 1
AAA1000
BBB2000
AAA1000
File 2
AAA1000
BBB2000
AAA1000
Both the files are FB with LRECL 80
Toolin and Control cards:
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T2) USING(CTL2)
SPLICE FROM(CONCT) TO(OUT12) ON(1,9,CH) WITH(82,1) -
WITHALL USING(CTL3) KEEPNODUPS
//CTL1CNTL DD *
OUTREC FIELDS=(1,80,81:C'11')
/*
//CTL2CNTL DD *
OUTREC FIELDS=(1,80,81:C'22')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT12,INCLUDE=(81,2,CH,EQ,C'12'),OUTREC=(1,80)
OUTFIL FNAMES=OUT1,INCLUDE=(81,2,CH,EQ,C'11'),OUTREC=(1,80)
OUTFIL FNAMES=OUT2,INCLUDE=(81,2,CH,EQ,C'22'),OUTREC=(1,80)
I am getting output as
OUT12 - 4 records
OUT1 - 1 record
OUT2 - no records
I need the output to be OUT12 - 3 records and OUT1 and OUT2 as 0
How can this be done? Need help!
Thanks,
Nachi
File 1
AAA1000
BBB2000
AAA1000
File 2
AAA1000
BBB2000
AAA1000
Both the files are FB with LRECL 80
Toolin and Control cards:
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T2) USING(CTL2)
SPLICE FROM(CONCT) TO(OUT12) ON(1,9,CH) WITH(82,1) -
WITHALL USING(CTL3) KEEPNODUPS
//CTL1CNTL DD *
OUTREC FIELDS=(1,80,81:C'11')
/*
//CTL2CNTL DD *
OUTREC FIELDS=(1,80,81:C'22')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT12,INCLUDE=(81,2,CH,EQ,C'12'),OUTREC=(1,80)
OUTFIL FNAMES=OUT1,INCLUDE=(81,2,CH,EQ,C'11'),OUTREC=(1,80)
OUTFIL FNAMES=OUT2,INCLUDE=(81,2,CH,EQ,C'22'),OUTREC=(1,80)
I am getting output as
OUT12 - 4 records
OUT1 - 1 record
OUT2 - no records
I need the output to be OUT12 - 3 records and OUT1 and OUT2 as 0
How can this be done? Need help!
Thanks,
Nachi
Compare 2 files matching and writing all the records to op
Correction:
OUT12 - 3 records
OUT1 - 1 record
OUT2 - no records
OUT12 - 3 records
OUT1 - 1 record
OUT2 - no records
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
You need to explain what you want to do more clearly.
Are you comparing record 1 of file1 to record 1 of file2, record 2 of file1 to record 2 of file2, etc or are you comparing the records in the two files in some other way?
Which fields do you want to compare the records on (the entire record? positions 1-3? positions 4-7? or what?).
Are you comparing record 1 of file1 to record 1 of file2, record 2 of file1 to record 2 of file2, etc or are you comparing the records in the two files in some other way?
Which fields do you want to compare the records on (the entire record? positions 1-3? positions 4-7? or what?).
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Compare 2 files by writing all matching records to output
Frank,
I need to make sure whether input File1 and input File2 contain same data. (but they can be in different order).
To be more clear, I have cut pasted my sample JCL
//STEP050N EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
NACHI
FRANK
NACHI
//IN2 DD *
FRANK
NACHI
NACHI
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE),
// DISP=(MOD,PASS),DCB=(LRECL=357)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE),
// DISP=(MOD,PASS),DCB=(LRECL=357)
//CONCT DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,DELETE)
// DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,DELETE)
//OUT1 DD DSN=<OUTFILE1>,
// DISP=(NEW,CATLG,DELETE),DCB=(LRECL=80),
// UNIT=SYSDA,SPACE=(CYL,(100,10),RLSE)
//OUT2 DD DSN=<OUTFILE2>,
// DISP=(NEW,CATLG,DELETE),DCB=(LRECL=80),
// UNIT=SYSDA,SPACE=(CYL,(100,10),RLSE)
//OUT12 DD DSN=<OUTFILE3>,
// DISP=(NEW,CATLG,DELETE),DCB=(LRECL=80),
// UNIT=SYSDA,SPACE=(CYL,(100,10),RLSE)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T2) USING(CTL2)
SPLICE FROM(CONCT) TO(OUT12) ON(1,10,CH) -
WITHALL WITH(12,1) USING(CTL3) KEEPNODUPS
/*
//CTL1CNTL DD *
INREC FIELDS=(1,10,11:C'11')
/*
//CTL2CNTL DD *
INREC FIELDS=(1,10,11:C'22')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT12,OUTREC=(1,80)
OUTFIL FNAMES=OUT1,INCLUDE=(11,2,CH,EQ,C'11'),OUTREC=(1,80)
OUTFIL FNAMES=OUT2,INCLUDE=(11,2,CH,EQ,C'22'),OUTREC=(1,80)
/*
In short, I want the output OUT12 to contain
NACHI 12
FRANK 12
NACHI 12
where as, I am getting
FRANK 12
NACHI 11
NACHI 12
NACHI 12
and OUTPUT OUT1 and OUT2 to contain 0 records where as I am getting OUT1 one record and OUT2 no record
Please let me know whether the info provided is sufficient enough to help me!
Thanks,
Nachi
I need to make sure whether input File1 and input File2 contain same data. (but they can be in different order).
To be more clear, I have cut pasted my sample JCL
//STEP050N EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
NACHI
FRANK
NACHI
//IN2 DD *
FRANK
NACHI
NACHI
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE),
// DISP=(MOD,PASS),DCB=(LRECL=357)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE),
// DISP=(MOD,PASS),DCB=(LRECL=357)
//CONCT DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,DELETE)
// DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,DELETE)
//OUT1 DD DSN=<OUTFILE1>,
// DISP=(NEW,CATLG,DELETE),DCB=(LRECL=80),
// UNIT=SYSDA,SPACE=(CYL,(100,10),RLSE)
//OUT2 DD DSN=<OUTFILE2>,
// DISP=(NEW,CATLG,DELETE),DCB=(LRECL=80),
// UNIT=SYSDA,SPACE=(CYL,(100,10),RLSE)
//OUT12 DD DSN=<OUTFILE3>,
// DISP=(NEW,CATLG,DELETE),DCB=(LRECL=80),
// UNIT=SYSDA,SPACE=(CYL,(100,10),RLSE)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T2) USING(CTL2)
SPLICE FROM(CONCT) TO(OUT12) ON(1,10,CH) -
WITHALL WITH(12,1) USING(CTL3) KEEPNODUPS
/*
//CTL1CNTL DD *
INREC FIELDS=(1,10,11:C'11')
/*
//CTL2CNTL DD *
INREC FIELDS=(1,10,11:C'22')
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT12,OUTREC=(1,80)
OUTFIL FNAMES=OUT1,INCLUDE=(11,2,CH,EQ,C'11'),OUTREC=(1,80)
OUTFIL FNAMES=OUT2,INCLUDE=(11,2,CH,EQ,C'22'),OUTREC=(1,80)
/*
In short, I want the output OUT12 to contain
NACHI 12
FRANK 12
NACHI 12
where as, I am getting
FRANK 12
NACHI 11
NACHI 12
NACHI 12
and OUTPUT OUT1 and OUT2 to contain 0 records where as I am getting OUT1 one record and OUT2 no record
Please let me know whether the info provided is sufficient enough to help me!
Thanks,
Nachi
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
No, you basically repeated what you said the first time which didn't explain what you want. And you didn't answer the questions I asked in my first post.Please let me know whether the info provided is sufficient enough to help me!
You show the following for input:
//IN1 DD *
NACHI
FRANK
NACHI
//IN2 DD *
FRANK
NACHI
NACHI
If I go line by line in file1 and file2, the first and second lines are different and the third line is the same. Yet you say the expected output is:
NACHI 12
FRANK 12
NACHI 12
So how am I supposed to determine what you want from that? You need to explain the "rules" clearly. If you aren't comparing line by line, then explain what exactly you are comparing.
It doesn't help to show me YOUR JCL. What you need to do is explain to me what you're trying to do and answer my questions in my previous post.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Compare 2 files by writing all matching records to output
Frank,
I am trying to compare 2 files whether they contain the same data. And if they contain the same data, I want all the match records without eliminating the duplicates to be written to an output file. If there are 3 records in both input files, I need all 3 records in the output.
Post 1 reply:
1. Are you comparing record 1 of file1 to record 1 of file2, record 2 of file1 to record 2 of file2, etc or are you comparing the records in the two files in some other way?
No. I want the records to be sorted in both the files without eliminating the duplicates. And then compare record1 of file1 to record 1 of file 2.
2. Which fields do you want to compare the records on (the entire record? positions 1-3? positions 4-7? or what?).
I want to compare between 1 thru 10
Please let me know if you need more info
Thanks,
I am trying to compare 2 files whether they contain the same data. And if they contain the same data, I want all the match records without eliminating the duplicates to be written to an output file. If there are 3 records in both input files, I need all 3 records in the output.
Post 1 reply:
1. Are you comparing record 1 of file1 to record 1 of file2, record 2 of file1 to record 2 of file2, etc or are you comparing the records in the two files in some other way?
No. I want the records to be sorted in both the files without eliminating the duplicates. And then compare record1 of file1 to record 1 of file 2.
2. Which fields do you want to compare the records on (the entire record? positions 1-3? positions 4-7? or what?).
I want to compare between 1 thru 10
Please let me know if you need more info
Thanks,
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
Well, that's certainly an important piece of the puzzle that you didn't mention before. But I suspect your example/rules don't really cover all of the possibilities.I want the records to be sorted in both the files without eliminating the duplicates. And then compare record1 of file1 to record 1 of file 2.
In your input examples, you show the exact same number of duplicates for each key in each file (e.g. two instances of AAA1000 and one instance of BBB2000 in both files for your first example, and two instances of NACHI and one instance of FRANK in both files for your second example). Is that always the case? Or can you have different numbers of duplicates for a key in each file and/or non-duplicates. For example, could you have something like this?
Code: Select all
File1
AAA1000 1
AAA1000 2
CCC3000 3
CCC3000 4
AAA1000 5
BBB2000 6
File2
AAA1000 7
CCC3000 8
AAA1000 9
DDD2000 10
DDD2000 11
CCC3000 12
CCC3000 13
If I sort each file by 1,10, I would get:
Code: Select all
File1
AAA1000 1
AAA1000 2
AAA1000 5
BBB2000 6
CCC3000 3
CCC3000 4
File2
AAA1000 7
AAA1000 9
CCC3000 8
CCC3000 12
CCC3000 13
DDD2000 10
DDD2000 11
to these records and what would you expect for output and why?I want the records to be sorted in both the files without eliminating the duplicates. And then compare record1 of file1 to record 1 of file 2
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Compare 2 files by writing all matching records to output
Frank,
Yes. that is correct that the input files can have different number of duplicates and non-duplicates.
Sorted output from your example:
OUT12 (match between both files including duplicates)
Did I give you a clear picture? Please let me know if you need some more info?
Thanks,
Nachi
Yes. that is correct that the input files can have different number of duplicates and non-duplicates.
Sorted output from your example:
This scenario is possible and in this case, I want the output something like thisFile1
AAA1000 1
AAA1000 2
AAA1000 5
BBB2000 6
CCC3000 3
CCC3000 4
File2
AAA1000 7
AAA1000 9
CCC3000 8
CCC3000 12
CCC3000 13
DDD2000 10
DDD2000 11
OUT12 (match between both files including duplicates)
OUT1 (records that are present in file 1 do not match with file 2 )AAA1000
AAA1000
CCC3000
CCC3000
OUT2 (records that are present in file 2 do not match with file 1)AAA1000
BBB2000
The reason why I need this way, is the matching record file (OUT12) is fed as an input to another job which sums up money amounts and does a lot of other functionalities. And in theory there should not be any differences between the input files and if there are any, they should be captured and investigated whether they are valid differences or not.CCC3000
DDD2000
DDD2000
Did I give you a clear picture? Please let me know if you need some more info?
Thanks,
Nachi
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
Ok, now it's clear. Here's a DFSORT/ICETOOL job that will do what you asked for:
Code: Select all
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/80)
//IN2 DD DSN=... input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5),RLSE),
// DISP=(MOD,PASS)
//OUT12 DD DSN=... output file12 (FB/10)
//OUT1 DD DSN=... output file1 (FB/10)
//OUT2 DD DSN=... output file2 (FB/10)
//TOOLIN DD *
SORT FROM(IN1) TO(T1) USING(CTL1)
SORT FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT12) ON(1,10,CH) ON(13,8,ZD) -
KEEPNODUPS WITHALL WITH(11,1) USING(CTL3)
/*
//CTL1CNTL DD *
SORT FIELDS=(1,10,CH,A)
OUTREC BUILD=(1,10,11:C'BB',13:SEQNUM,8,ZD,RESTART=(1,10))
/*
//CTL2CNTL DD *
SORT FIELDS=(1,10,CH,A)
OUTREC BUILD=(1,10,11:C'VV',13:SEQNUM,8,ZD,RESTART=(1,10))
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT12,INCLUDE=(11,2,CH,EQ,C'VB'),BUILD=(1,10)
OUTFIL FNAMES=OUT1,INCLUDE=(11,2,CH,EQ,C'BB'),BUILD=(1,10)
OUTFIL FNAMES=OUT2,INCLUDE=(11,2,CH,EQ,C'VV'),BUILD=(1,10)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Compare 2 files by writing all matching records to output
Frank,
That worked! Thanks a lot for your help!
RESTART is the key here! If you don't have restart, its the same way what I did earlier.
Thanks,
Nachi
That worked! Thanks a lot for your help!
RESTART is the key here! If you don't have restart, its the same way what I did earlier.
Thanks,
Nachi
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
Except that I also used SORTs instead of COPYs (SORT followed by OUTREC is needed so the sequence numbers are applied to the sorted records).If you don't have restart, its the same way what I did earlier.
And I used one T1 MOD data set instead of T1 and T2 with concatenation. Note that using T1 and T2 with concatenation can result in data loss due to the system restriction described in the second bullet on this page:
http://publibz.boulder.ibm.com/cgi-bin/ ... 1007&CASE=
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Compare 2 files by writing all matching records to output
Frank,
Very useful information. I agree with you that Concatenated files may result in loss of data. Also, SORT is used instead of COPY.
Thanks again for all your help!
- Nachi
Very useful information. I agree with you that Concatenated files may result in loss of data. Also, SORT is used instead of COPY.
Thanks again for all your help!
- Nachi
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page