Compare files and create file with non-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 non-matching records

Postby 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:

//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:

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
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 non-matching records

 

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

Postby 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

//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
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: Compare files and create file with non-matching records

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

Fantastic. Thankyou very much.

It works 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

please guide me to compare files in jcl through sort utility

Postby 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.
muthulakshmi.m
 
Posts: 1
Joined: Mon Mar 29, 2010 5:47 pm
Has thanked: 0 time
Been thanked: 0 time

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

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


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post