Compare files and create file with matching records



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

Compare files and create file with matching records

Postby Aaron Chessell » Thu Jul 24, 2008 4:32 am

Hi,

Thanks for the help with my last problem.

Now I have a new problem.

I need to compare 2 files and output records from file 1 that appear in file 2 as well.

File 1 contains duplicates. The output file needs to contain these duplicate records.

I also need to append the data from the matching record on file 2 to all the matching records in file 1.

Here is an example of what I need to do.

File 1

1234Aaron
1234Peter
1234Geoff
2345Adam
3456Paul
4567Trevor
4567David
5678Alan

File 2

1234Chessell
2345Richards
3456Galvin
5678Jones
6789Edwards

Output file

1234Aaron Chessell
1234Peter Chessell
1234Geoff Chessell
2345Adam Richards
3456Paul Galvin
5678Alan Jones

Specific file details include:

Match on a binary 4 field.
Record lengths and formats are different for each file: File 1 LRECL=92, File2 LRECL=14, however both have a binary 4 as the first field in each file. The output file has a LRECL=102 (basically the length of both records minus the key from file 2).

Any help would be much appreciated.

Regards,
Aaron
Aaron Chessell
 
Posts: 13
Joined: Tue Jul 22, 2008 4:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Compare files and create file with matching records

 

Re: Compare files and create file with matching records

Postby Frank Yaeger » Thu Jul 24, 2008 5:09 am

Aaron,

My complements on your excellent description of the requirement (we don't see a lot of that). :)

Here's a DFSORT/ICETOOL job that will do what you asked for:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/92)
//IN2 DD DSN=...  input file2 (FB/14)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/102)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,4,BI) WITHALL -
  WITH(1,12) WITH(24,80) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(1,4,13:5,10,103:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(103:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(103,2,CH,EQ,C'VB'),
    BUILD=(1,102)
/*
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
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Compare files and create file with matching records

Postby Aaron Chessell » Thu Jul 24, 2008 10:32 am

Thanks Frank and Dick.

Worked like a charm.

Cheers,
Aaron
Aaron Chessell
 
Posts: 13
Joined: Tue Jul 22, 2008 4:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Compare files and create file with matching records

Postby dick scherrer » Thu Jul 24, 2008 12:41 pm

Hi Aaron,

Good to hear it is working.

Thank you for letting us know :)

d
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Compare files and create file with matching records

Postby Aaron Chessell » Fri Jul 25, 2008 6:49 am

Something further to this, is it possible in this instance to sort the output file by multiple keys at the same time that you are creating it.

For example, once the output file is in the format that I need it in, I need to sort it by the B4 key that the original files were matched on (which it will be sorted by anyway) and other key values within the record to get it into the final order that I need.

Is it possible and how would I do it if it is? Or do I need to do a completely new sort on the output file after it is created?

Not sure if I'm making myslef clear.

Any help very much appreciated.

Cheers,
Aaron
Aaron Chessell
 
Posts: 13
Joined: Tue Jul 22, 2008 4:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Compare files and create file with matching records

Postby Frank Yaeger » Fri Jul 25, 2008 8:40 pm

I'd need more details in order to tell you whether you could do what you want in one pass. What is that starting position, length and format of the other keys you want to sort on? What is the final order you need? Show an example of your input records and what you expect for output.
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
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Compare files and create file with matching records

Postby Aaron Chessell » Tue Jul 29, 2008 5:04 am

Hi Frank,

We have worked out that we don't need to do a sort by extra fields. So don't need to know how to do this just yet.

I will start another thread if I do need to in the future though.

Cheers,
Aaron
Aaron Chessell
 
Posts: 13
Joined: Tue Jul 22, 2008 4:29 am
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post