Compare files and omit duplicates from one



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

Compare files and omit duplicates from one

Postby Aadil » Fri Aug 29, 2008 4:55 am

Hello everyone,

I am new to this site so please forgive any mistakes or incorrect etiquette.

I am new to DFSORT as well and am still busy reading the DFSORT Getting started guide as recommended by Frank. I am faced with an issue at present wherein I have two files, one is RECFM=FB,LRECL=80. This file continues unique values, sorted in ascending sequence and looks something like this:

1111111
1111112
1111123
1222222
1333321
1444211
........

The second file is: RECFM=VB, LRECL=32756. This file is also sorted and
(1) contains multiple instances of records that match the "keys" in the first file, and
(2) the corresponding "key values" begin at byte 11 (4 bytes for RDW and 6 bytes of useless data); eg: (RDW not shown)

aajhsh1111111followed by some data
aabhsa1111111followed by data
...z.a..1111112followed by data
ajhjad1111112followed by data
.,mzmn1111113followed by data
/././.1222222followed by data
khajah1234567followed by data

The matching fields which are bytes 1-7 in FB file and bytes 11-17 (after RDW and 6 bytes) is a packed decimal field.

The records in file 2 has to be matched against those in file 1 and if a match is found, then those records in file 2 must be discarded. So for example, all the records in file 2 with the following values in columns 11-17 must be dropped: 111111; 1111112 ; 1222222
(Five of the seven records in file 2 as per my example above). The remaining records are the ones I am interested in. Would be a nice to have to write out the "dropped" records to another file but this is not necessary at the moment.

Is there a way this can be done using ICETOOL? Any assistance will be appreciated.

Thanks.

Aadil
Aadil
 
Posts: 6
Joined: Fri Aug 29, 2008 4:02 am
Has thanked: 1 time
Been thanked: 0 time

Re: Compare files and omit duplicates from one

Postby Frank Yaeger » Fri Aug 29, 2008 5:52 am

Here's a DFSORT/ICETOOL job that will do what you asked for. It will write the non-matching records to OUT1 and the "dropped" records to OUT2. However, due to some limitations on the length of records, this will only work for records up to 32754 bytes long. If you actually have records that are 32755 or 32756 bytes long, the job will terminate.

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file (FB/80)
//IN2 DD DSN=...  output file (VB/32756)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS),
// LRECL=32756
//OUT1 DD DSN=...  output file1 (VB/32754)
//OUT2 DD DSN=...  output file2 (VB/32754)
//TOOLIN DD *
COPY FROM(IN1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT1) ON(13,7,PD) WITHALL VLENOVLY -
  KEEPNODUPS KEEPBASE WITH(6,32747) USING(CTL3)
/*
//CTL1CNTL DD *
  OUTFIL FNAMES=T1,FTOV,BUILD=(1:C'BB',9:1,7)
/*
//CTL2CNTL DD *
  INREC BUILD=(1,4,5:C'VV',7:5)
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT1,INCLUDE=(5,2,CH,EQ,C'VV'),
    BUILD=(1,4,5:7)
  OUTFIL FNAMES=OUT2,INCLUDE=(5,2,CH,EQ,C'BV'),
    BUILD=(1,4,5:7)
/*
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: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Compare files and omit duplicates from one

Postby Aadil » Fri Aug 29, 2008 1:44 pm

Thank you Frank, much appreciated.
Aadil
 
Posts: 6
Joined: Fri Aug 29, 2008 4:02 am
Has thanked: 1 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post