IBM Mainframe Forum

by **Aaron Chessell** » Tue Jul 22, 2008 5:23 am

Hi,

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

Both files contain duplicates.

I have got the example in the "Smart Tricks" guide to work. However I am having some difficulty translating it into something that will work for my scenario.

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

Any help would be much appreciated.

Regards,
Aaron

The JCL I am using follows:

Select all

//ICECULL EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//DEFOUT DD SYSOUT=*
//TR DD DSN=APD1.MR.DATA.ATO.TEMP.VISA,DISP=SHR
//PR DD DSN=APD1.MR.DATA.ATO.PERM.VISA,DISP=SHR
//F1 DD DSN=APD1.MR.DATA.ATO.F1,DISP=MOD
//T1 DD DSN=APD1.MR.DATA.ATO.T1,DISP=MOD
//OUT12 DD DSN=EXITAC.ATO.BOTH,DISP=SHR BOTH
//OUT1 DD DSN=APD1.MR.DATA.ATO.TEMP.NOPERM.VISA,DISP=SHR TR ONLY
//OUT2 DD DSN=EXITAC.ATO.ONLYPR,DISP=SHR PR ONLY
//*SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(1000,200))
//*SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(1000,200))
//*SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(1000,200))
//*SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(1000,200))
//*SORTWK05 DD UNIT=SYSDA,SPACE=(CYL,(1000,200))
//*SORTWK06 DD UNIT=SYSDA,SPACE=(CYL,(1000,200))
//TOOLIN DD *
* GET FIRST RECORD OF EACH UNIQUE PID FROM TR
SELECT FROM(TR) TO(F1) ON(1,4,BI) FIRST
* GET FIRST RECORD OF EACH UNIQUE PID FROM PR
SELECT FROM(PR) TO(F1) ON(1,4,BI) FIRST
* GET ONE RECORD WITH EACH PID ONLY IN TR OR ONLY IN PR AND ADD
* 'UU' IDENTIFIER
SELECT FROM(F1) TO(T1) ON(1,4,BI) NODUPS USING(CTL2)
* ADD '11' IDENTIFIER FOR TR RECORDS
COPY FROM(TR) TO(T1) USING(CTL3)
* SPLICE TO MATCH UP RECORDS AND WRITE THEM TO THEIR APPROPRIATE
* OUTPUT FILES
* SPLICE FROM(T1) TO(OUT1) ON(1,4,BI) -
* WITHALL WITH(1,92) USING(CTL5)
/*
//CTL2CNTL DD *
* MARK RECORDS WITHOUT TR/PR MATCH WITH 'UU'
OUTFIL FNAMES=T1,OVERLAY=(93:C'UU')
/*
//CTL3CNTL DD *
* MARK TR RECORDS WITH '11'
OUTFIL FNAMES=T1,OVERLAY=(93:C'11')
/*
//CTL5CNTL DD *
* MARK TR ONLY RECORDS TO OUT1 FILE. REMOVE ID
OUTFIL FNAMES=OUT1,INCLUDE=(93,2,CH,EQ,C'1U'),
BUILD=(1,92)
/*

Output from the job follows:

Select all

ICE600I 0 DFSORT ICETOOL UTILITY RUN STARTED

ICE650I 0 VISIT http://www.ibm.com/storage/dfsort FOR ICETOOL PAPERS, EXAMPLES A

ICE632I 0 SOURCE FOR ICETOOL STATEMENTS: TOOLIN

ICE630I 0 MODE IN EFFECT: STOP

* GET FIRST RECORD OF EACH UNIQUE PID FROM TR
SELECT FROM(TR) TO(F1) ON(1,4,BI) FIRST
ICE627I 0 DFSORT CALL 0001 FOR SORT FROM TR TO F1 COMPLETED
ICE628I 0 RECORD COUNT: 000000000088531
ICE638I 0 NUMBER OF RECORDS RESULTING FROM CRITERIA: 000000000035191
ICE602I 0 OPERATION RETURN CODE: 00

* GET FIRST RECORD OF EACH UNIQUE PID FROM PR
SELECT FROM(PR) TO(F1) ON(1,4,BI) FIRST
ICE627I 0 DFSORT CALL 0002 FOR SORT FROM PR TO F1 COMPLETED
ICE628I 0 RECORD COUNT: 000000000047178
ICE638I 0 NUMBER OF RECORDS RESULTING FROM CRITERIA: 000000000018054
ICE602I 0 OPERATION RETURN CODE: 00

* GET ONE RECORD WITH EACH PID ONLY IN TR OR ONLY IN PR AND ADD
* 'UU' IDENTIFIER
SELECT FROM(F1) TO(T1) ON(1,4,BI) NODUPS USING(CTL2)
ICE606I 0 DFSORT CALL 0003 FOR SORT FROM F1 TO T1 USING CTL2CNTL COM
ICE628I 0 RECORD COUNT: 000000000053245
ICE638I 0 NUMBER OF RECORDS RESULTING FROM CRITERIA: 000000000030113
ICE602I 0 OPERATION RETURN CODE: 00

* ADD '11' IDENTIFIER FOR TR RECORDS
COPY FROM(TR) TO(T1) USING(CTL3)
ICE606I 0 DFSORT CALL 0004 FOR COPY FROM TR TO T1 USING CTL3CNTL TER
ICE602I 0 OPERATION RETURN CODE: 16

ICE630I 2 MODE IN EFFECT: SCAN

* SPLICE TO MATCH UP RECORDS AND WRITE THEM TO THEIR APPROPRIATE
* OUTPUT FILES
* SPLICE FROM(T1) TO(OUT1) ON(1,4,BI) -
* WITHALL WITH(1,92) USING(CTL5)

ICE601I 0 DFSORT ICETOOL UTILITY RUN ENDED - RETURN CODE: 16

by **skolusu** » Tue Jul 22, 2008 5:57 am

The following DFSORT/ICETOOL JCL will give you the desired results. This output of this job is all the records which do not have a matching record in file2. A brief explanation of the job.

1. The first select gets the unique record on 1,4, bI from the 14 byte file and while writing out we also add the key field at the end of the record to suit file 1 LRECL
2. We pad 4 spaces at the end of every record in 92 byte file.
3. step 1 and 2 write out to the same output file T1. Make sure to have MOD Disp parameter
4. Now we splice the key from file2 on to all the records in file1. Using an OMIT condition on the OUTFIL we omit all records which have a matching key from file2

Select all

//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=your 92 byte file,
// DISP=SHR
//IN2 DD DSN=your 14 byte file,
// DISP=SHR
//T1 DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE)
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN2) TO(T1) ON(1,4,BI) FIRST USING(CTL1)
COPY FROM(IN1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,4,BI) KEEPBASE KEEPNODUPS -
WITHALL WITH(1,92) USING(CTL3)
//CTL1CNTL DD *
OUTFIL FNAMES=T1,BUILD=(1,4,93:1,4)
//CTL2CNTL DD *
OUTFIL FNAMES=T1,OVERLAY=(93:4X)
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,BUILD=(1,92),
OMIT=(1,4,BI,EQ,93,4,BI,OR,5,87,CH,EQ,C' ')
/*

Hope this helps...

Cheers

by **Aaron Chessell** » Tue Jul 22, 2008 6:45 am

Fantastic. Thankyou very much.

It works like a charm.

Cheers,
Aaron

by **muthulakshmi.m** » Tue Mar 30, 2010 12:09 pm

I have two files file1 contains record count for ex : 12 (file1 have LREC of 15 digits in file1 that contains only record count)

File2 contains the record count 15(file2 have LREC of 15 digits in file2 that contains only record count)

Now I want to compare file1 and file2

If file1 < file2 then I need to set RC=4

If file1 > file2 then I need to set RC=6.

Could you please suggest the code in JCL through Sort utility.

by **Frank Yaeger** » Tue Mar 30, 2010 10:33 pm

You should have started a new topic for this question instead of posting in a 2-year old topic.

What is the RECFM and LRECL of each input file?

If file1 < file2 then I need to set RC=4
If file1 > file2 then I need to set RC=6.

You mean the count in file1 and the count in file2 - right?

DFSORT cannot set the combination of RC=4 or RC=6.
Would one of these combinations be ok:
RC=0 or RC=4?
RC=0 or RC=12?

What do you want to do if the count in file1 = the count in file2?

You really need to do a better job of explaining exactly what you want. An example of your input records and expected output for the various cases would help.

IBM Mainframe Forum

Compare files and create file with non-matching records

Compare files and create file with non-matching records

Re: Compare files and create file with non-matching records

Re: Compare files and create file with non-matching records

please guide me to compare files in jcl through sort utility

Re: Compare files and create file with non-matching records