Is it possible to compare two datasets matching with the key and write all differences as following into new files:
- New file 1: rows, where the key only is in infile 1
- New file 2: rows, where the key only is in infile 2
- New file 3: rows from infile 2 where the key (infile1/infile2) is matching and something differing in the rest of the row
the best solution would even be to write all information into one file containing the information
n = new (for rows with keys only in file 2)
d = deleted (for rows with keys only in file 1)
c = changed (for rows with same key in file 1 and 2 but different rest).
comparing files with icetool
Moderators: Frank Yaeger, Moderator Group
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
Yes, it is possible with DFSORT's JOINKEYS.
For complete information on DFSORT's JOINKEYS, see:
http://publibz.boulder.ibm.com/cgi-bin/ ... 0630155256
For specific examples, see the "Create files with matching and non-matching records" Smart DFSORT trick at:
http://www.ibm.com/support/docview.wss? ... g3T7000094
For complete information on DFSORT's JOINKEYS, see:
http://publibz.boulder.ibm.com/cgi-bin/ ... 0630155256
For specific examples, see the "Create files with matching and non-matching records" Smart DFSORT trick at:
http://www.ibm.com/support/docview.wss? ... g3T7000094
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
Re: comparing files with icetool
Thank you very much for the links.
I have created a sort step with the following input:
* Control statements for JOINKEYS application
JOINKEYS F1=INA,FIELDS=(1,7,A),SORTED
JOINKEYS F2=INB,FIELDS=(1,7,A),SORTED
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,13,F2:1,13,?)
* Control statements for main task (joined records)
OPTION COPY
OUTFIL FNAMES=F1ONLY,INCLUDE=(27,1,CH,EQ,C'1'),
BUILD=(1,13)
OUTFIL FNAMES=F2ONLY,INCLUDE=(27,1,CH,EQ,C'2'),
BUILD=(14,13)
OUTFIL FNAMES=BOTH,INCLUDE=(27,1,CH,EQ,C'B'),
BUILD=(14,13)
I receive now:
- in F1ONLY all of the records where the key only is in F1
- in F2ONLY all of the records where the key only is in F2
- in BOTH all of the records from F2 where the key is matching to F1 (in each case, not depending on the data in the rest of the record)
But what I want to do is, to compare the records where the key is matching, I want to have in BOTH only the records where something in the record differs, comparing F1 and F2 matching by the key. I do not want all of the records with a matching key, only the record where the hole record is the same for this key.
Example:
F1
0100201..ACM
0100401..CEF
0100701..CHK
F2
0100001..CEF
0100201..ABC
0100701..CHK
BOTH shall only contain:
0100201..ABC
and not:
0100201..ABC
0100701..CHK
because the record for key 0100701 is the same in F1 and F2.
Do I only have the possibility to take the outfile that I now have created and use it in another step to compare if something changed in one of the following fields or can I do this in one single ICETOOL or SORT-step? Which function can I use in this case?
I have created a sort step with the following input:
* Control statements for JOINKEYS application
JOINKEYS F1=INA,FIELDS=(1,7,A),SORTED
JOINKEYS F2=INB,FIELDS=(1,7,A),SORTED
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,13,F2:1,13,?)
* Control statements for main task (joined records)
OPTION COPY
OUTFIL FNAMES=F1ONLY,INCLUDE=(27,1,CH,EQ,C'1'),
BUILD=(1,13)
OUTFIL FNAMES=F2ONLY,INCLUDE=(27,1,CH,EQ,C'2'),
BUILD=(14,13)
OUTFIL FNAMES=BOTH,INCLUDE=(27,1,CH,EQ,C'B'),
BUILD=(14,13)
I receive now:
- in F1ONLY all of the records where the key only is in F1
- in F2ONLY all of the records where the key only is in F2
- in BOTH all of the records from F2 where the key is matching to F1 (in each case, not depending on the data in the rest of the record)
But what I want to do is, to compare the records where the key is matching, I want to have in BOTH only the records where something in the record differs, comparing F1 and F2 matching by the key. I do not want all of the records with a matching key, only the record where the hole record is the same for this key.
Example:
F1
0100201..ACM
0100401..CEF
0100701..CHK
F2
0100001..CEF
0100201..ABC
0100701..CHK
BOTH shall only contain:
0100201..ABC
and not:
0100201..ABC
0100701..CHK
because the record for key 0100701 is the same in F1 and F2.
Do I only have the possibility to take the outfile that I now have created and use it in another step to compare if something changed in one of the following fields or can I do this in one single ICETOOL or SORT-step? Which function can I use in this case?
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
You can use these DFSORT control statements to get what you want:
Code: Select all
* CONTROL STATEMENTS FOR JOINKEYS APPLICATION
JOINKEYS F1=INA,FIELDS=(1,7,A),SORTED
JOINKEYS F2=INB,FIELDS=(1,7,A),SORTED
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,13,F2:1,13,?)
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)
OPTION COPY
OUTFIL FNAMES=F1ONLY,INCLUDE=(27,1,CH,EQ,C'1'),
BUILD=(1,13)
OUTFIL FNAMES=F2ONLY,INCLUDE=(27,1,CH,EQ,C'2'),
BUILD=(14,13)
OUTFIL FNAMES=BOTH,INCLUDE=(27,1,CH,EQ,C'B',AND,
8,6,CH,NE,21,6,CH),
BUILD=(14,13)
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
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
You can get that with a DFSORT job like the following:the best solution would even be to write all information into one file containing the information
n = new (for rows with keys only in file 2)
d = deleted (for rows with keys only in file 1)
c = changed (for rows with same key in file 1 and 2 but different rest).
Code: Select all
//S2 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD DSN=... input file1
//INB DD DSN=... input file2
//OUT DD DSN=... output file
//SYSIN DD *
* CONTROL STATEMENTS FOR JOINKEYS APPLICATION
JOINKEYS F1=INA,FIELDS=(1,7,A),SORTED
JOINKEYS F2=INB,FIELDS=(1,7,A),SORTED
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,13,F2:1,13,?)
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)
OPTION COPY
OUTFIL FNAMES=OUT,
OMIT=(27,1,CH,EQ,C'B',AND,
8,6,CH,EQ,21,6,CH),
IFOUTLEN=14,
IFTHEN=(WHEN=(27,1,CH,EQ,C'1'),BUILD=(1,13,C'D')),
IFTHEN=(WHEN=(27,1,CH,EQ,C'2'),BUILD=(14,13,C'N')),
IFTHEN=(WHEN=(27,1,CH,EQ,C'B',AND,
8,6,CH,NE,21,6,CH),BUILD=(14,13,C'C'))
/*
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
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