ICETOOL - compare 2 input files



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

ICETOOL - compare 2 input files

Postby bitbybit » Mon Oct 15, 2012 10:53 pm

I have 2 input files that have same record layout and need to compare the first 12 bytes of a 15 byte Packed decimal field s9(15) comp-3, starting at position 50 from the input files and if they are equal write the record from 1st input file to O/P. Can it be done with ICETOOL, any example? Thnx in advance.
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

Re: ICETOOL - compare 2 input files

Postby NicC » Mon Oct 15, 2012 10:58 pm

Joinkeys sounds like a good option to look up in the manual/forum
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: ICETOOL - compare 2 input files

Postby BillyBoyo » Mon Oct 15, 2012 11:19 pm

Is that the "key" for the file? Can there be multiple values? What are recfm/lrecl of files?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: ICETOOL - compare 2 input files

Postby skolusu » Mon Oct 15, 2012 11:22 pm

bitbybit wrote:I have 2 input files that have same record layout and need to compare the first 12 bytes of a 15 byte Packed decimal field s9(15) comp-3, starting at position 50 from the input files and if they are equal write the record from 1st input file to O/P. Can it be done with ICETOOL, any example? Thnx in advance.



Joinkeys can be used to get the desired results. However If the PD field is storing numbers you will be having a match of 1000 records for each key if you compare only the 6 bytes of input filed. Do you really want to do that? You will be creating a Huge output.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: ICETOOL - compare 2 input files

Postby bitbybit » Mon Oct 15, 2012 11:30 pm

Additional info: It's not record to record compare. If the value of acct-no (1st 12 bytes of 15 bytes PD startign at pos 50) of Record1 from file-1 exists in any record of file2 (we r checking same fld, fld acct-no), then we found a match for record1 of file1 and write that record to o/p.
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

Re: ICETOOL - compare 2 input files

Postby bitbybit » Mon Oct 15, 2012 11:36 pm

It's not the Key, but it's safe to assume, since the 1st 12 bytes of acct# are not going to repeat either in 1st or 2nd input file.
For example: Acct# could be 12345678912300 and 12345678912302, in this case 12345678912302 exists in file1 and 12345678912300 exists in file2. File1 or 2 will not have morethan 1 record that has "123456789123". If match found, I need to write File1 record to o/p.
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

Re: ICETOOL - compare 2 input files

Postby bitbybit » Mon Oct 15, 2012 11:38 pm

Format is VB and LRECL=32472
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

Re: ICETOOL - compare 2 input files

Postby skolusu » Tue Oct 16, 2012 12:06 am

bittybit,

Use the following DFSORT JCL which will give you the desired results. I assumed that you accounted the RDW when you mentioned the position of the key field to match. If you did not then change the Fields to Fields=(54,6,A) as the first 4 bytes has the RDW for VB files.


//STEP0100 EXEC PGM=SORT           
//SYSOUT   DD SYSOUT=*             
//INA      DD DSN=Your Input VB file1,DISP=SHR
//INB      DD DSN=Your Input VB file2,DISP=SHR
//SORTOUT  DD SYSOUT=*             
//SYSIN    DD *                     
  OPTION COPY                       
  JOINKEYS F1=INA,FIELDS=(50,6,A)   
  JOINKEYS F2=INB,FIELDS=(50,6,A)   
  REFORMAT FIELDS=(F1:1,4,5)       
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: ICETOOL - compare 2 input files

Postby bitbybit » Thu Oct 18, 2012 6:23 pm

Hi Kolusu,
It didn't work for me.
Here is my file1
#5 #6 +
PD 51:8 AN 59:155
<---+----1----+> <---+----1----+----2----+----3----+----4----+----5----
****** **** Top of data ****
=LGTH 96710211969104 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211969602 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211969704 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211969302 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211969406 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211969502 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211969208 .F.....F... ...{82..DRUGCRMK..... ...F.....F
****** **** End of data ****

and File2 is
#5 #6 +
PD 51:8 AN 59:155
<---+----1----+> <---+----1----+----2----+----3----+----4----+----5----
****** **** Top of data ****
=LGTH 96710211969100 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211969700 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211969800 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211969900 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211969400 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211961300 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211454400 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211969300 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211969200 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211961100 .F.....F... ...{82..DRUGCRMK..... ...F.....F
=LGTH 96710211961000 .F.....F... ...{82..DRUGCRMK..... ...F.....F
****** **** End of data ****

the field is starting at pos-51 and with RDW, it is 55, so I coded it as,
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD DSN=Your Input VB file1,DISP=SHR
//INB DD DSN=Your Input VB file2,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(55,6,A)
JOINKEYS F2=INB,FIELDS=(55,6,A)
REFORMAT FIELDS=(F1:1,4,5)
//*

My o/p file has 49 records??, it should only have records from F1 except for 96710211969502 and 96710211969602
Your help is greatly appreciated.
Thanks.
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

Re: ICETOOL - compare 2 input files

Postby BillyBoyo » Thu Oct 18, 2012 6:35 pm

Can you post again with the reference number and the key used for the join, in hex. You have multiple key values so you will get multiple records on your output.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post