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
 

 

comparing two files using ICE TOOL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
souji

New User


Joined: 28 Aug 2008
Posts: 9
Location: Bangalore

PostPosted: Tue Feb 24, 2009 11:27 am    Post subject: comparing two files using ICE TOOL
Reply with quote

Hi I have not worked on ICE TOOL. I need to compare 2 files with 2 fields and write the unmatched record in the 3 rd files.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Feb 24, 2009 11:30 am    Post subject:
Reply with quote

Hello,

There are multiple examples in the forum. Pick one that is most similar to your requirement and go from there.
Back to top
View user's profile Send private message
souji

New User


Joined: 28 Aug 2008
Posts: 9
Location: Bangalore

PostPosted: Tue Feb 24, 2009 12:02 pm    Post subject:
Reply with quote

Hi d.sch.

This is for a RI process(reference integrity)
I have 2 files of differnt LRECL say FIN File and REF file and this needs to be compared and the missing data needs to be written to the 3 file. This would happen for the Current month. similarly everymonth FIN data gets loaded to the prior month table.Now the all the prior month data needs to be compared with REF file.

for example the CM, for the RI compare it with the reference data via a ICETOOL solution .Before you do that you can merge the CM data with the PM/PY RI records to see if there are any new ones. If there aren´t you don´t need to process the RI again.

Is it possible to merget the CM data with PM records and see the new records.
Back to top
View user's profile Send private message
nelson.pandian

Active User


Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ

PostPosted: Tue Feb 24, 2009 2:41 pm    Post subject:
Reply with quote

Hi Soujanya,

Please give some Sample input data and the expected output date. Specify the RECFM, LRECL, Position and length of the Key field and give the rules.
Back to top
View user's profile Send private message
souji

New User


Joined: 28 Aug 2008
Posts: 9
Location: Bangalore

PostPosted: Tue Feb 24, 2009 7:26 pm    Post subject:
Reply with quote

FILE A has LRECL =31 with following fields

X1 CHAR(003)
X2 CHAR(012)
X3 CHAR(008)
X4 CHAR(008)

This is compared with FILE B with LRECL = 15 with the following fields

Y1 CHAR(003)
Y2 CHAR(012)

The comparing is done on X1 of A file with Y1 of file B and X3 of FILE A with Y2 of file B. Here X3 is 8 char while we compare with Y2 which is 12 char..only the first 8 should be matched... The missing records in FILE B should be written to output file C.

Can you please provide the JCL for thsi using ICETOOL.
Thanks in advance...
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Feb 24, 2009 10:24 pm    Post subject:
Reply with quote

Quote:
Can you please provide the JCL for thsi using ICETOOL.


Not until you provide the needed information.

You showed the layout of the records, but you didn't give an example of the records in the input file and what you expect for output. Show such an example with values for the relevant fields. Give the RECFM and LRECL of the input file. Explain the "rules" for getting from input to output with reference to the example. If input file1 can have duplicates within it, show that in your example. If input file2 can have duplicates within it, show that in your example.
Back to top
View user's profile Send private message
souji

New User


Joined: 28 Aug 2008
Posts: 9
Location: Bangalore

PostPosted: Wed Feb 25, 2009 6:36 pm    Post subject:
Reply with quote

Hello Frank,


Input file A

X1 X2 X3 X4
=============================================
111PPPPPPPPPPPPBBBBBBBBCCCCCCCC
222GGGGGGGGGGGGRRRRRRRRSSSSSSSS
333AAAAAAAAAAAAPPPPPPPPQQQQQQQQ
444XXXXXXXXXXXXYYYYYYYYZZZZZZZZ


FILE A will not have duplicate records,FILE B can have duplicate records or other records which not not present in FILE A.FILE B is like a reference file it can have any records. But FILE A should not contain any records that are missing in FILE B. The missing records should be copied to FILE C.

Input file B

Y1 Y2
===============================================
111BBBBBBBBBBBB
222PPPPPPPPPPPP


Compare FILE A and FILE B. on the KEYS X1 VS Y1 and X3 vs Y2. Note that X3 is 8 char while Y2 is 12 char so campare only the first 8 char,The missing records in FILE C corresponding records from FILE A should be written in FILE C

FILE C

Z1 Z2
===============================================
222RRRRRRRR
333YYYYYYYY

Can you please provide the JCL withb IECTOOL.

This is the 1 st part .
The above example also has a 2 nd request also which is bit complex.Not sure if this is possible.

Part 2
-------

In the above example FILE A is compared with FILE B on following Keys
X1 VS Y1
X3 VS Y2
AND ALSO
X1 VS Y1
X4 Vs Y2

So the out put will be like this.The out put should not have the duplicates.

FILE C

Z1 Z2
===============================================
222RRRRRRRR
333YYYYYYYY
111CCCCCCCC
222SSSSSSSS
333QQQQQQQQ
444ZZZZZZZZ

Not sure if the 2 nd part is possible Please let me know if you need more information .Thanks
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Feb 25, 2009 10:21 pm    Post subject:
Reply with quote

Sigh. Your description does NOT seem to match your example.

For the output, you show:

222RRRRRRRR
333YYYYYYYY

The 1/B record appears in both files, so I see why that isn't in the output file. The 2/R record in fileA does not have a match in fileB, so I see why that is in the output file. But the 3/P and 4/Y records in fileA do not have matches in fileB, so I would expect to see them in the output file. Instead you have a 3/Y record which does not appear in fileA at all.

You need to explain where the 3/Y record comes from.
Back to top
View user's profile Send private message
souji

New User


Joined: 28 Aug 2008
Posts: 9
Location: Bangalore

PostPosted: Thu Feb 26, 2009 4:08 pm    Post subject:
Reply with quote

Thanks for correcting me Frank...i am sorry for the confusion, the Out put file should contain the 3/P and 4/Y record.. and not the 3/Y record.. sorry for this mess up..

So the O/p will be

FILE C

Z1 Z2
===============================================
222RRRRRRRR
333PPPPPPPPP
444YYYYYYYYY

IMP thing here is X3 is 8 char.. while its been compared with Y2 which is 12 char... so only the first 8 char should be matched...

If possible the out put file should not contain duplicates.... its ok if we are not able to remove the duplicates when we write the FILE C .. we can always do it in the next step...

Can you please provide the JCL for this using ICETOOL....Thanks in advance...

in the above example is the 2 nd part possible....

So the O/p for part 2 will be

FILE C

Z1 Z2
===============================================
222RRRRRRRR
333PPPPPPPP
444YYYYYYYY
111CCCCCCCC
222SSSSSSSS
333QQQQQQQQ
444ZZZZZZZZ
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Feb 27, 2009 1:57 am    Post subject:
Reply with quote

souji,

The following DFSORT/ICETOOL JCl will give you the desired results. It does both the comparisions X1X3 = Y1Y2 & X1X4 = Y1Y2 and writes out the missing records from file1. The output lrecl is 11 bytes. Make sure you have disp=(mod,pass) on the temp dataset t1

Code:

//STEP0100 EXEC PGM=ICETOOL                                 
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*
//IN1      DD DSN=Your 31 byte file,
//            DISP=SHR
//IN2      DD DSN=Your 15 byte file,
//            DISP=SHR
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)
//OUT      DD SYSOUT=*                                       
//TOOLIN   DD *                                             
  SORT FROM(IN2) USING(CTL1)                                 
  COPY FROM(IN1) USING(CTL2)                                 
  SPLICE FROM(T1) TO(OUT) ON(1,11,CH) WITH(1,11) WITHALL -   
  KEEPNODUPS KEEPBASE USING(CTL3)                           
//CTL1CNTL DD *                                             
  INREC BUILD=(1,15)                                         
  SORT FIELDS=(1,11,CH,A)                                   
  SUM FIELDS=NONE                                           
  OUTFIL FNAMES=T1,BUILD=(1,11,12:C'Y')                     
//CTL2CNTL DD *                                             
  OUTFIL FNAMES=T1,BUILD=(1,3,16,8,X,/,1,3,24,8,X)           
//CTL3CNTL DD *                                             
  OUTFIL FNAMES=OUT,OMIT=(12,1,CH,EQ,C'Y'),BUILD=(1,11)     
/*


The output from the job is sorted on the key sequence.
Code:

111CCCCCCCC
222RRRRRRRR
222SSSSSSSS
333PPPPPPPP
333QQQQQQQQ
444YYYYYYYY
444ZZZZZZZZ
Back to top
View user's profile Send private message
souji

New User


Joined: 28 Aug 2008
Posts: 9
Location: Bangalore

PostPosted: Fri Feb 27, 2009 4:39 pm    Post subject:
Reply with quote

Thanks so much Skolusu for the JCL.. icon_smile.gif
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm


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