To compare two files (SSN fields)

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

vikasbs
Member
Posts: 8
Joined: Mon Apr 17, 2006 5:17 pm

To compare two files (SSN fields)

Post by vikasbs » Mon Apr 17, 2006 5:27 pm

I have a requirement to compare two files .The field i am trying to compare is SSN .

The requirement is : There are two files FILEA and FILEB

I need to compare FILEA with FILEB where in the output file i need the SSN's found on FILEA not on FILEB

I am trying to use COMPAREX but not able to get the desired result.
Can somebody help me out in this.

Regards
Vikas

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Mon Apr 17, 2006 8:24 pm

Can you use DFSORT instead of COMPAREX? If so, you can use a variation of the Smart DFSORT Trick discussed at:

http://www.ibm.com/servers/storage/supp ... 2.html#t05
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

Guest

To compare two files (SSN fields)

Post by Guest » Mon Apr 17, 2006 11:16 pm

Hi Frank

Kinldy confirm do you want me to use the trick discussed for the topic
"Create files with matching and non-matching records (without and with duplicates)"

Request you to confirm .

Thanks and Regards
Vikas

Guest

To compare two files (SSN fields)

Post by Guest » Mon Apr 17, 2006 11:39 pm

Hi Frank

This is my exact requirement.

In the production file (FILE A) i've the data like this

SSN STATUS
---------------------------
123456789 ACTIVE
884347737 WAITING
937384999 ACTIVE

In the second file (FILE B) i've have data like this

POLICY NUMBER SSN LNAME FNAME ADDRESS (...etc)
---------------------------------------------------------------


Now the LRECL for both the files are 423

The out put file should have the

1)SSN on FILEA but not on FILEB and in the next step it is the other way round

2)SSN on FILEB but not on FILEA.

Please let me know if you want me to be more clear of the requirement.

Regards
Vikas

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Tue Apr 18, 2006 12:33 am

Vikas,

Here's a DFSORT/ICETOOL job that will do what I think you asked for. I assumed the SSN is in positions 1-9 in input file1 and in positions 19-27 in input file2, and that you just wanted the SSN in the output files. I also assumed that there are no duplicate SSNs within input file1, and no duplicate SSNs within input file2.

Code: Select all

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1
//IN2 DD DSN=...  input file2
//OUT1 DD DSN=...    output with SSNs in file1 only
//OUT2 DD DSN=...    output with SSNs in file2 only
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5)),DISP=(MOD,PASS)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT1) ON(1,9,CH) WITH(10,1) -
USING(CTL3) KEEPNODUPS
/*
//CTL1CNTL DD *
  OUTREC FIELDS=(1,9,10:C'11')
/*
//CTL2CNTL DD *
  OUTREC FIELDS=(19,9,10:C'22')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT1,INCLUDE=(10,2,CH,EQ,C'11'),OUTREC=(1,9)
  OUTFIL FNAMES=OUT2,INCLUDE=(10,2,CH,EQ,C'22'),OUTREC=(1,9)
/*
Here's an example:

Input file1

Code: Select all

050000000          ACTIVE   
123456789          ACTIVE   
884347737          WAITING  
937384999          ACTIVE   
940000000          WAITING    
Input file2

Code: Select all

...               120000000  
...               123456789  
...               880000000  
...               937384999  
OUT1

Code: Select all

050000000
884347737
940000000
OUT2

Code: Select all

120000000  
880000000  
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

Guest

To compare two files (SSN fields)

Post by Guest » Tue Apr 18, 2006 2:56 pm

Hi Frank,

Thanks a ton for your assistance it worked.

I've another question

1)could you please let me know what is that i need to change to get the entire record in the output files.

Frank i've another requirement which is very similar to the one i have posted

2)The requirement is to get the records which does not match during compare.Can i use NE in the include card.Will that work.

Thanks a ton again please do the needful.

Regards
Vikas

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Tue Apr 18, 2006 9:17 pm

1) The way you described your requirement, I thought you only wanted the SSN in the output data sets. So that's what I showed you how to do.
If you want the entire record in each output data set, you can use a DFSORT/ICETOOL job like this:

Code: Select all

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/423)
//IN2 DD DSN=...  input file2 (FB/423)
//OUT1 DD DSN=... output file1 (FB/423)
//OUT2 DD DSN=... output file2 (FB/423)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5)),DISP=(MOD,PASS)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT1) ON(424,9,CH) WITH(433,1) -
USING(CTL3) KEEPNODUPS
/*
//CTL1CNTL DD *
  OUTREC OVERLAY=(424:1,9,433:C'11')
/*
//CTL2CNTL DD *
  OUTREC OVERLAY=(424:19,9,433:C'22')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT1,INCLUDE=(433,2,CH,EQ,C'11'),BUILD=(1,423)
  OUTFIL FNAMES=OUT2,INCLUDE=(433,2,CH,EQ,C'22'),BUILD=(1,423)
/*
2)The requirement is to get the records which does not match during compare.Can i use NE in the include card.Will that work.
I don't understand what you mean by this. The job above gets the records that are only in File1 and the records that are only in File2. What do you mean by "the records which does not match" if not that. Is this a different requirement altogether where you're matching fields in the SAME record of one data set? If so, you can use:

Code: Select all

   INCLUDE COND=(field1,NE,field2)
or

Code: Select all

   OMIT COND=(field1,EQ,field2)
to keep the records with unequal fields.
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

vikasbs
Member
Posts: 8
Joined: Mon Apr 17, 2006 5:17 pm

To compare two files (SSN fields)

Post by vikasbs » Tue Apr 18, 2006 10:53 pm

Hi Frank,

I just tried the new JCL but it ends with MAXCC=16 stating following message

1) OUTFIL FNAMES=OUT1,INCLUDE=(433,2,CH,EQ,C'11'),BUILD=(1,423)
$
INVALID OUTFIL STATEMENT OPERAND
OUTFIL FNAMES=OUT2,INCLUDE=(433,2,CH,EQ,C'22'),BUILD=(1,423)
$
INVALID OUTFIL STATEMENT OPERAND

I also tried using OUTREC instead of BUILD even that didn't work.

2)Frank the second requirement which i coated in my previous post was for the next step which uses one of the file created by the above step which we are currently working on .


Kindly reply and do the neeedful.

Regards
Vikas

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Tue Apr 18, 2006 11:05 pm

OVERLAY and BUILD require z/OS DFSORT V1R5 PTF UQ95214 or DFSORT R14 PTF UQ95213 (Dec, 2004). So if the $ is pointing to BUILD, that's why. Ask your System Programmer to install the appropriate PTF.
I also tried using OUTREC instead of BUILD even that didn't work.
When you say something didn't work, you have to show the control statement you used and the error message you received. Otherwise, I can't tell you what's wrong. Please put BBcode code tags around it (see BBCode link on the left under Options) so it will preserve the spaces.

I don't know what you want for 2). If what I said didn't help, then you need to explain what you're trying to do before I can help you, preferably with an example.

I can't edit posts on this board as I can on the other boards where I'm a Moderator, so I can't add the BBcode tags for you. I've asked the Administrator of this board if he'd like me to be a Moderator of this Forum, but I haven't gotten any response.
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

Guest

Post by Guest » Tue Apr 18, 2006 11:22 pm

Hi Frank,

I check in the DFSMSG we have DFSORT REL 14.0 with us not the one you've mentioned i guess.

We i guess i will come to the point 2 later once we resolve this since it would be confusing sorry for that.

We when i used OUTREC=(1,423) instead of BUILD i got the following message
"ICE027A 1 END OF FIELD BEYOND MAXIMUM RECORD LENGTH"

I forget to mention one thing
//IN1 DD DSN=... input file1 (FB/423) -- > My IN1 file has LRECL = 80
//IN2 DD DSN=... input file2 (FB/423) ---> My IN2 file has LRECL = 423
//OUT1 DD DSN=... output file1 (FB/423)
//OUT2 DD DSN=... output file2 (FB/423)

Is it ok to have IN1 LRECL = 80 ...please let me know.

Vikas

vikasbs
Member
Posts: 8
Joined: Mon Apr 17, 2006 5:17 pm

To compare two files (SSN fields)

Post by vikasbs » Wed Apr 19, 2006 12:19 am

Hi Frank

I would like to explain the requirement 2

As you know we get two output file from the step which we are working

FILEA and FILEB

Now in the next step the above mentioned file would be compared to another file FILEC

Now the compare once again will be on the SSN so i am writing out the all the information about all the three files

FILEA - LRECL - 80,SSN position 1 and the length (9) so (1,9)

FILEB - LRECL - 423,SSN position 8 and the length (9) so (8,9)
(I've given the position and LRECL assume we get all the fields associated with the SSN)

FILEC - LRECL - 1042,SSN position (11)and length (9) so (11.9 )


Frank this is the requirement ,

1)I need to compare FILEA with FILEC and write the records not found in FILEC to output file with full details to output file.

2)In the next step or in the same step i need to compare FILEB with FILEC and write the records not found in FILEC to output file with full details output file.

I Kinldy request you to help me out with this and do the needful.

Regards
Vikas

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Wed Apr 19, 2006 1:03 am

I check in the DFSMSG we have DFSORT REL 14.0 with us not the one you've mentioned i guess.
DFSORT Rel 14.0 is just the base release to which PTFs are applied. Ask your System Programmer to apply DFSORT PTF UQ95213 (Dec, 2004). Once he/she does, you'll be able to use OVERLAY and BUILD.
We when i used OUTREC=(1,423) instead of BUILD i got the following message
"ICE027A 1 END OF FIELD BEYOND MAXIMUM RECORD LENGTH"

I forget to mention one thing
//IN1 DD DSN=... input file1 (FB/423) -- > My IN1 file has LRECL = 80
//IN2 DD DSN=... input file2 (FB/423) ---> My IN2 file has LRECL = 423
//OUT1 DD DSN=... output file1 (FB/423)
//OUT2 DD DSN=... output file2 (FB/423)

Is it ok to have IN1 LRECL = 80 ...please let me know.
Well, that's an important thing and it's what's causing the error message you got. Since you told me both files had LRECL=423, that's what I set up the job for. You can do this with IN1 having LRECL=80, but the job has to be set up differently. Here's the job you need - I've also changed it to not require PTF UQ95213:

Code: Select all

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/80)
//IN2 DD DSN=...  input file2 (FB/423)
//OUT1 DD DSN=... output file1 (FB/80)
//OUT2 DD DSN=... output file2 (FB/423)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5)),DISP=(MOD,PASS)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT1) ON(424,9,CH) WITH(433,1) -
USING(CTL3) KEEPNODUPS
/*
//CTL1CNTL DD *
  OUTREC FIELDS=(1,80,424:1,9,433:C'11')
/*
//CTL2CNTL DD *
  OUTREC FIELDS=(1,423,424:8,9,433:C'22')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT1,INCLUDE=(433,2,CH,EQ,C'11'),OUTREC=(1,80)
  OUTFIL FNAMES=OUT2,INCLUDE=(433,2,CH,EQ,C'22'),OUTREC=(1,423)
/*
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
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Wed Apr 19, 2006 1:16 am

For requirement 2:

Let's call FILED the output file for FILEA vs FILEC, and FILEE the output file for FILEB vs FILEC. Do you just need FILED and FILEE, or do you need FILEA, FILEB, FILED and FILEE? In other words, do you need to keep FILEA and FILEB or are they just temporary data sets you use to get FILED and FILEE?

Also, am I right in assuming that FILED has LRECL=80 and has the records from FILEA not found in FILEC, and that FILEE has LRECL=423 and has the records from FILEB not found in FILEC?
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

vikasbs
Member
Posts: 8
Joined: Mon Apr 17, 2006 5:17 pm

To compare two files (SSN fields)

Post by vikasbs » Wed Apr 19, 2006 2:48 pm

Hi Frank

The last JCL which you gave worked fine and my profound thanks to you.

Frank your understanding is right about the requirement no 2.

Please find the answers to your questions.

We need to keep FILEA and FILEB and in the output i would just need FILED and FILEE.

The specification of FILEC are

FILEC - LRECL - 1042,SSN position (11)and length (9) so (11.9 )

Yes Frank you are right the FILED would have LRECL=80 and has the records from FILEA not found in FILEC, and that FILEE has LRECL=423 and has the records from FILEB not found in FILEC.

Frank can we do the above process in two different steps

a)one for FILEA and FILEC compare

b)another for FILEB and FILEC compare

Kindly do the needful.

Regards
Vikas

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Wed Apr 19, 2006 10:10 pm

Vikas,

Here's a DFSORT/ICETOOL job that produces FILEA, FILEB, FILED and FILEE as requested in one step. If you really need it in two steps instead of one step, let me know.

Code: Select all

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/80)
//IN2 DD DSN=...  input file2 (FB/423)
//IN3 DD DSN=...  input file2 (FB/1042)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5)),DISP=(MOD,PASS)
//FILEA DD DSN=... output fileA (FB/80)
//FILEB DD DSN=... output fileB (FB/423)
//TA DD DSN=&&TA,UNIT=SYSDA,SPACE=(TRK,(5,5)),DISP=(MOD,PASS)
//TB DD DSN=&&TB,UNIT=SYSDA,SPACE=(TRK,(5,5)),DISP=(,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(TRK,(5,5)),DISP=(,PASS)
//CON DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,PASS)
//    DD DSN=*.TA,VOL=REF=*.TA,DISP=(OLD,PASS)
//    DD DSN=*.TB,VOL=REF=*.TB,DISP=(OLD,PASS)
//FILED DD DSN=... output fileD (FB/80)
//FILEE DD DSN=... output fileE (FB/423)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(FILEA) ON(424,9,CH) WITH(433,1) -
  USING(CTL3) KEEPNODUPS
COPY FROM(IN3) TO(T2) USING(CTL4)
SPLICE FROM(CON) TO(FILED) ON(424,9,CH) WITH(433,1) -
  USING(CTL5) KEEPNODUPS
/*
//CTL1CNTL DD *
  OUTREC FIELDS=(1,80,424:1,9,433:C'11')
/*
//CTL2CNTL DD *
  OUTREC FIELDS=(1,423,424:8,9,433:C'22')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=FILEA,INCLUDE=(433,2,CH,EQ,C'11'),OUTREC=(1,80)
  OUTFIL FNAMES=FILEB,INCLUDE=(433,2,CH,EQ,C'22'),OUTREC=(1,423)
  OUTFIL FNAMES=TA,INCLUDE=(433,2,CH,EQ,C'11')
  OUTFIL FNAMES=TB,INCLUDE=(433,2,CH,EQ,C'22')
/*
//CTL4CNTL DD *
  OUTREC FIELDS=(424:11,9,433:C'33')
/*
//CTL5CNTL DD *
  OUTFIL FNAMES=FILED,INCLUDE=(433,2,CH,EQ,C'11'),OUTREC=(1,80)
  OUTFIL FNAMES=FILEE,INCLUDE=(433,2,CH,EQ,C'22'),OUTREC=(1,423)
/*
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

Locked

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others