comparing files with icetool

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

Post Reply
floh
Member
Posts: 5
Joined: Wed Aug 03, 2011 1:12 pm

comparing files with icetool

Post by floh » Wed Aug 03, 2011 1:31 pm

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).
:?:

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Wed Aug 03, 2011 10:40 pm

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
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

floh
Member
Posts: 5
Joined: Wed Aug 03, 2011 1:12 pm

Post by floh » Thu Aug 04, 2011 2:27 pm

thanks :)
very usefull links!

floh
Member
Posts: 5
Joined: Wed Aug 03, 2011 1:12 pm

Re: comparing files with icetool

Post by floh » Thu Aug 04, 2011 10:40 pm

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?

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Thu Aug 04, 2011 11:18 pm

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

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Thu Aug 04, 2011 11:21 pm

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).
You can get that with a DFSORT job like the following:

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

floh
Member
Posts: 5
Joined: Wed Aug 03, 2011 1:12 pm

Post by floh » Fri Aug 05, 2011 12:24 pm

Thank you for the solutions!
Both of them are very helfull :)

Post Reply

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



Other References
Mainframe Tools and others