IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

JCL Utility to Compare Two Files


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
ramp_senti

New User


Joined: 17 Mar 2006
Posts: 26

PostPosted: Mon Jul 24, 2006 12:21 pm
Reply with quote

Hai..,
I need to know is there any utility in jcl which can compare a column of one file with the column of another file and writes the record in a third file if the values of the columns are same.
Back to top
View user's profile Send private message
shreevamsi

Active User


Joined: 23 Feb 2006
Posts: 305
Location: Hyderabad,India

PostPosted: Mon Jul 24, 2006 1:40 pm
Reply with quote

Hi,

Use
PGM=ISRSUPC,
PARM=(DELTAL,LINECMP,
'',
'',
'')

Pass the Coloum thru SYSIN as follows

//SYSIN DD *
COMPARE TYPORG=PS
CMPCOLM 7:72 >>Specify the coloums to be checked for
/*


Please seacrh the form for ISRSUPC
~Vamsi
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Mon Jul 24, 2006 3:44 pm
Reply with quote

//SORTTOOL EXEC PGM=SORT
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=FIRST.FILE,DISP=SHR
//SORTJNF2 DD DSN=SECOND.FILE,DISP=SHR
//SORTOUT DD DSN=THIRD.FILE,DISP=SHR
//SYSIN DD *
SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(1,10,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A)
REFORMAT FIELDS=(F1:1,80)
/*

The key I compared starts at 1 and has length of 10 bytes, change this job to suit your requirement.

Please let me know if this worked for you.
Back to top
View user's profile Send private message
harish_mf

New User


Joined: 31 Jul 2005
Posts: 18
Location: bangalore

PostPosted: Mon Jul 24, 2006 4:48 pm
Reply with quote

Hi,

what does it mean,



REFORMAT FIELDS=(F1:1,80)

Please give mean the solution for this ,

thanks in advance.
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Mon Jul 24, 2006 4:54 pm
Reply with quote

If the key matches it will write the record from first file to the third file. What is your requirement?
Is it to copy the first file's record to third or second file's record to the third on finding the match.
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Mon Jul 24, 2006 6:20 pm
Reply with quote

If you want only the matching part , you may code REFORMAT FIELDS=(F1:1,10) instead
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Mon Jul 24, 2006 9:26 pm
Reply with quote

You can do this with the SPLICE operator of DFSORT's ICETOOL. For an example, see the "Create files with matching and non-matching records (without and with duplicates)" Smart DFSORT Trick at:

www.ibm.com/servers/storage/support/software/sort/mvs/tricks/index.html

If you need more specific help, give more details of what you want to do exactly. Show an example of the records in each input file and what you want for output. If their are duplicates within input file1, show that in the example. If there are duplicates within input file2, show that in the example. Give the starting position, length and format of the relevant fields. Give the RECFM and LRECL of the input files.
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Tue Jul 25, 2006 12:00 am
Reply with quote

Thanks Frank , I was about to suggest the same.
Back to top
View user's profile Send private message
ramp_senti

New User


Joined: 17 Mar 2006
Posts: 26

PostPosted: Wed Jul 26, 2006 9:07 pm
Reply with quote

Thanks Frank!

I tried the icetool jcl which was available in the link .I t worked well.
Can you please clarify a few things

* Can you please explain the splice step( particularly the keyword 'with')
* In case the starting postion of the value which is compared varies in both files
eg:
Vicky in first file starts at 1st position of the record
and Vicky in second file starts at no 5 position
what needs to be done
* The Outrec field in the outfil statement allows us to write only one field..can we have more than one field .
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Wed Jul 26, 2006 9:45 pm
Reply with quote

ramp_senti wrote:
Thanks Frank!

I tried the icetool jcl which was available in the link .I t worked well.
Can you please clarify a few things

* Can you please explain the splice step( particularly the keyword 'with')


For a complete explanation of DFSORT's SPLICE operator, see Chapter 6 of "z/OS DFSORT Application Programming Guide". Here's a link to that section:

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA10/6.13?DT=20050222160456

Quote:

* In case the starting postion of the value which is compared varies in both files
eg:
Vicky in first file starts at 1st position of the record
and Vicky in second file starts at no 5 position
what needs to be done


This is just an example. You can extend it in many ways. If the field to be compared is not in the same place in both files, you would reformat the records of one file to have the field in the same place as in the other file. In your example, you could reformat the first file field to position 5, or the second file field to position 1.

Quote:

* The Outrec field in the outfil statement allows us to write only one field..can we have more than one field .


OUTFIL can be used to write as many fields as you like.

Again, if you need more specific help, give more details of what you want to do exactly. Show an example of the records in each input file and what you want for output. If their are duplicates within input file1, show that in the example. If there are duplicates within input file2, show that in the example. Give the starting position, length and format of the relevant fields. Give the RECFM and LRECL of the input files.

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

Use [URL] BBCode for External Links
Back to top
View user's profile Send private message
raak

Active User


Joined: 23 May 2006
Posts: 166
Location: chennai

PostPosted: Fri Jul 28, 2006 3:35 pm
Reply with quote

hi,
just check out this one....

//*
//COMPARE EXEC PGM=COMPAREX
//SYSPRINT DD SYSOUT=Q
//OUTPUTDD OUTPUT PRMODE=PAGE,PAGEDEF=ONEUPO,FORMDEF=ONEUPO
//*YSPRINT DD SYSOUT=(Q,,STDO),OUTPUT=*.OUTPUTDD
//SYSUT1 DD DSN=BKTEST.W.CTS.PDR908.UT.UNLOADU.PMTTRAN.TEST,
// DISP=SHR
//SYSUT2 DD DSN=BKTEST.W.CTS.PDR908.UT.LOAD.OUTPUT,
// DISP=SHR
//SYSIN DD *
CONTINUE
FORMAT=16
DASH=C'-'
CASE=MIXED
/*
Back to top
View user's profile Send private message
ramp_senti

New User


Joined: 17 Mar 2006
Posts: 26

PostPosted: Sat Aug 05, 2006 1:26 am
Reply with quote

Thanks Frank!

I went through the link you mentioned.Now my requirement is to compare a column value of one variable length file with the column of a second file which is of fixed length.
The output file must have a particular column of the first file for those records whose column value matches with that of the column of the second file.

eg..,
Highligted fileds in the files are to be compared , and the output should have the vlaues from file 1 for the matching records.

Record length of file1 ( VB) is 1024
record length of file2 (FB) is 100
Thanks in Advance!
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Sat Aug 05, 2006 11:18 pm
Reply with quote

You'll need to convert the FB file to a VB file with the column to be matched in the same place as for the VB input file. You can use OUTFIL FTOV to do that. It will add a 4 byte RDW to each FB input record to create a VB record.

In your example, I can't really tell which file is which or what you want for output. If you need more help, show a better example (without the screen shots) of the records in each input file and the expected output, and indicate the starting position, length and format of each relevant field in the input files.
Back to top
View user's profile Send private message
ramp_senti

New User


Joined: 17 Mar 2006
Posts: 26

PostPosted: Mon Aug 07, 2006 1:29 pm
Reply with quote

Input file 1: Record length -- 1024
Input file 2 :
Record length --- 100
Output file : Record length --1024 ( same as first input file)

(Matching record starting position -- 53
length -12)
i.e., record starting from position 53 and length 12 of the input file1 has to be matched with the record starting from postion 53 and length 12 of the input file 2.


Output file when the record matches should contain records from input file 1 strating from position 10 to 20 ,30 to 35, and 53 to 65.

Example
Input file 1:

MDA70NM249008504R70568031000S57980174S98508118080 999775
MDB21G2539009751R21140471000S57980174S97060114040 999775
MDA408041900932CB40005560000S57980174SCBBK1114040 999775

Input file 2 :

XXXXXX YYYYYYYYYYY ZZZZZZZZZZZZZZ UUUUUUU0114040 999775

Column to be matched is in bold and starting at position 53 ,length12 in both input file1 and input file2

Output file :

9008504R705 579801 999775

Output file when the record matches should contain records from input file 1 strating from position 10 to 20 ,30 to 35, and 53 to 65.

Thanks in advance!
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Mon Aug 07, 2006 9:47 pm
Reply with quote

I don't understand. You show three records with 999775 in file1 and one record with 999775 in file2, but for output you only show the first record from file1 with 999775? Do you only want the first record from file1 for each match, or do you want all of the records from file1 with each match?

Also, you say the matching field is 12 bytes long, but in your example you show 999775 which is only 6 bytes long.

It would help if you showed a more complete example including records in file1 with and without a match in file2, and the complete expected output, and explained the "rules" you want to use to get from input to output.
Back to top
View user's profile Send private message
ramp_senti

New User


Joined: 17 Mar 2006
Posts: 26

PostPosted: Mon Aug 07, 2006 10:11 pm
Reply with quote

Hai Frank!
Very sorry for the confusions!

We want all the three records ,but we just showed a single record .
Actually the output will be exactly like the given below.

And the length of the matching field is 12 ,with 6 spaces.

Example
Input file 1:

MDA70NM249008504R70568031000S57980174S98508118080 999775
MDB21G2539009751R21140471000S57980174S97060114040 999775
MDA408041900932CB40005560000S57980174SCBBK1114040 999765
MDB21G2539009751R21140471000S57980174S97060114040 999745

Input file 2 :

XXXXXX YYYYYYYYYYY ZZZZZZZZZZZZZZ UUUUUUU0114040 999775
XXXXXX YYYYYYYYYYY ZZZZZZZZZZZZZZ UUUUUUU0114040 999745

Output file :

9008504R705 579801 999775
9009751R211 579801 999775
9009751R211 579801 999745

Hope this one is clear
Thank You
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Tue Aug 08, 2006 1:14 am
Reply with quote

ramp_senti,

Here's a DFSORT/ICETOOL job that will do what I think you're asking for. Note that the output records will be sorted by the key (e.g. 999745 record before 999775 records). If you want the output records in their original order, we'll need to add a sequence number and do another sort on it. But try this version first to see whether or not it gives you the output records you want without worrying about their sequence yet.

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1 (VB/1024)
//IN2 DD DSN=...  input file2 (FB/100)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS),
// LRECL=1026
//OUT DD DSN=...  output file (VB/1024)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(26,12,CH) -
  WITHALL WITH(5,1) WITH(7,31) USING(CTL3)
/*
//CTL1CNTL DD *
  OUTFIL FNAMES=T1,FTOV,OUTREC=(C'BB',22:53,12)
/*
//CTL2CNTL DD *
  OUTREC FIELDS=(1,4,C'VV',7:14,11,19:34,6,26:57,12)
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,
    INCLUDE=(5,2,CH,EQ,C'VB'),
    OUTREC=(1,4,5:7)
/*
Back to top
View user's profile Send private message
ramp_senti

New User


Joined: 17 Mar 2006
Posts: 26

PostPosted: Thu Aug 10, 2006 8:16 pm
Reply with quote

Thanks a lot ! Frank

Its working . In case of doubt we will get back .
Back to top
View user's profile Send private message
ramp_senti

New User


Joined: 17 Mar 2006
Posts: 26

PostPosted: Fri Aug 11, 2006 3:12 pm
Reply with quote

Sorry for disturbing you again Frank!

Our requirements has changed a bit.In the output we need unmatched records available in file1 also in addition to the matched records.

for eg;
Input file 1:
MDA70NM249008504R70568031000S57980174S98508118080 999775
MDB21G2539009751R21140471000S57980174S97060114040 999775
MDA408041900932CB40005560000S57980274SCBBK1114040 999765
MDA408041900932CB40005560000S57980374SCBBK1114040 999765
MDB21G2539009751R21140471000S57980174S97060114040 999745

Input file 2 :

XXXXXX YYYYYYYYYYY ZZZZZZZZZZZZZZ UUUUUUU0114040 999775
XXXXXX YYYYYYYYYYY ZZZZZZZZZZZZZZ UUUUUUU0114040 999745

Output file 1: ( matching records)

9008504R705 579801 999775
9009751R211 579801 999775
9009751R211 579801 999745

Output file 2: (unmatching records from file 1)
900932CB400 579802 999765
900932CB400 579803 999765

We tried using the following cntlcard3

CTL3CNTL DD *
OUTFIL FNAMES=OUT1,
INCLUDE=(5,2,CH,EQ,C'VB'),
OUTREC=(1,4,5:7)
OUTFIL FNAMES=OUT2,
INCLUDE=(5,2,CH,EQ,C'VV'),
OUTREC=(1,4,5:7)

We have no problems in the matched records output(out1).
but in the output of unmatching records (out2) the first record is missing, and only the second record gets displayed

our output looks like the one below

Output file 2:

900932CB400 579803 999765


Kindly help us in the case

thanks!
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Fri Aug 11, 2006 8:58 pm
Reply with quote

You're on the right track. You just need to add KEEPBASE and KEEPNODUPS to the SPLICE operator, and use the CTL3CNTL statements you show:

Code:

SPLICE FROM(T1) TO(OUT) ON(26,12,CH) -
  WITHALL WITH(5,1) WITH(7,31) USING(CTL3) -
  KEEPBASE KEEPNODUPS
Back to top
View user's profile Send private message
Dapitu

New User


Joined: 16 Aug 2006
Posts: 1
Location: USA

PostPosted: Wed Aug 16, 2006 4:29 am
Reply with quote

To early.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts REASON 00D70014 in load utility DB2 6
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
Search our Forums:

Back to Top