Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Compare files through ICETOOL and SPLICE operator

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
vidyasaraswathi

New User


Joined: 10 May 2005
Posts: 72

PostPosted: Thu May 26, 2005 3:39 pm    Post subject: Compare files through ICETOOL and SPLICE operator
Reply with quote

Hi,
I have 2 files. I want to compare them by using a key and if the records are present in either file1 or file2 but not in both files, then I want to write those records to an output file.

I heard that that this can be done through ICETOOL and SPLICE operator.

Please any one tell me how exactly this can be done.


Thanks and Regards,
Vidya Bhat
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


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

PostPosted: Thu May 26, 2005 7:52 pm    Post subject:
Reply with quote

Yes, this can be done with the SPLICE operator of DFSORT's ICETOOL. For details, see this "Smart DFSORT Trick":

http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/srtmst02.html#t05
Back to top
View user's profile Send private message
vidyasaraswathi

New User


Joined: 10 May 2005
Posts: 72

PostPosted: Fri May 27, 2005 12:50 pm    Post subject: Re: Comparing two files
Reply with quote

Thank you Frank.

I saw this link. But I am not able to run this successfully.

1)In the following piece of code,

SPLICE FROM(T1) TO(OUT12) ON(1,10,CH) WITH(13,1) -
USING(CTL3) KEEPNODUPS

can you please explain what exactly ON(1,10,CH) , WITH(13,1) and
USING(CTL3) refer to?

2)I have 2 files with record length 133.
I want to compare 2 files based on the key Rep-No which is in the disposition 2 in the file and declared as PIC Z9(16).

Please tell me what values I have to use in SPLICE and OUTREC in the later steps?



Thanks in Advance,
Vidya Bhat
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Fri May 27, 2005 8:28 pm    Post subject:
Reply with quote

ON(1,10,CH) tells SPLICE that the key is a 10-byte character field in positions 1-10. WITH(13,1) tells SPLICE to overlay the byte at position 13 in the overlay record on to the base record at the same position. USING(CTL3) tells SPLICE to use the DFSORT control statements in the //CTL3CNTL data set.

For complete documentation on SPLICE, with lots of examples, see Chapter 6 of "z/OS DFSORT Application Programming Guide". Here's a link:

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

I don't know what PIC Z9(16) is. If you mean PIC S9(16), that's the equivalent of a 16-byte ZD field. If it starts in position 2, then the ON field would be ON(2,16,ZD).

If you need me to provide the DFSORT/ICETOOL job for you, then you need to give me more information. What is the RECFM and LRECL of the input files? What is the starting position, length and format of the key? Can input file1 have duplicates? Can input file2 have duplicates? Show me an example of the input records in each file, and what you want for the output files. Include duplicates in the example where relevent.
Back to top
View user's profile Send private message
vidyasaraswathi

New User


Joined: 10 May 2005
Posts: 72

PostPosted: Mon May 30, 2005 6:47 pm    Post subject: Re: Comparing two files
Reply with quote

Hi Frank,
The link provided by you is very informative.
But still I could not get the result what I expected.

My requirement is:
-----------------------------
1)OUT1 should contain the records which are present only in IN1, but not in IN2.

2)OUT2 should contain the records which are present only in IN2, but not in IN1.

3)OUT12 should contain the records which are present in both IN1 and IN2.


This is my JCL
------------------
Code:

//*-----------------------------------------------------------------   
//S1 EXEC PGM=ICETOOL                                                   
//*-----------------------------------------------------------------   
//TOOLMSG DD SYSOUT=*                                                   
//DFSMSG DD SYSOUT=*                                                   
//IN1 DD DSN=B052VAP.REP.SRTX1,DISP=SHR                                 
//IN2 DD DSN=B052VAP.REP.SRTX2,DISP=SHR                                 
//OUT12 DD DSN=B052VAP.OUT12,                                           
//          DISP=(OLD,CATLG),UNIT=SYSDA,                               
//          VOL=SER=US3003,                                             
//          SPACE=(TRK,(500,250),RLSE),                                 
//          DCB=(B0.DSCB,RECFM=FB,LRECL=16,BLKSIZE=0)                   
//OUT1 DD   DSN=B052VAP.OUT1,                                           
//          DISP=(OLD,CATLG),UNIT=SYSDA,                               
//          VOL=SER=US3003,                                             
//          SPACE=(TRK,(500,250),RLSE),                                 
//          DCB=(B0.DSCB,RECFM=FB,LRECL=16,BLKSIZE=0)                   
//OUT2 DD   DSN=B052VAP.OUT2,                                           
//          DISP=(OLD,CATLG),UNIT=SYSDA,                               
//          VOL=SER=US3003,                                             
//          SPACE=(TRK,(500,250),RLSE),                                 
//          DCB=(B0.DSCB,RECFM=FB,LRECL=16,BLKSIZE=0)                   
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5)),                         
//  DISP=(MOD,PASS)                                                     
//TOOLIN DD *                                                           
* ADD '11' IDENTIFIER FOR FILE1 RECORDS.                               
COPY FROM(IN1) TO(T1) USING(CTL1)                                       
* ADD '22' IDENTIFIER FOR FILE2 RECORDS.                               
COPY FROM(IN2) TO(T1) USING(CTL2)                                       
* SPLICE TO MATCH UP RECORDS AND WRITE THEM TO THEIR                   
* APPROPRIATE OUTPUT FILES.                                             
SPLICE FROM(T1) TO(OUT12) ON(8,11,ZD) WITH(20,2) -                     
USING(CTL3) KEEPNODUPS       
/*                                                           
//CTL1CNTL DD *                                               
* MARK FILE1 RECORDS WITH '11'                               
  OUTREC FIELDS=(8,11,20:C'11')                               
/*                                                           
//CTL2CNTL DD *                                               
* MARK FILE2 RECORDS WITH '22'                               
  OUTREC FIELDS=(8,11,20:C'22')                               
/*                                                           
//CTL3CNTL DD *                                               
* MARK FILE3 RECORDS WITH '12'                               
  OUTREC FIELDS=(8,11,20:C'12')                               
* WRITE MATCHING RECORDS TO OUT12 FILE. REMOVE ID.           
  OUTFIL FNAMES=OUT12,INCLUDE=(20,2,CH,EQ,C'12'),OUTREC=(2,16)
* WRITE FILE1 ONLY RECORDS TO OUT1 FILE. REMOVE ID.           
  OUTFIL FNAMES=OUT1,INCLUDE=(20,2,CH,EQ,C'11'),OUTREC=(2,16)
* WRITE FILE2 ONLY RECORDS TO OUT2 FILE. REMOVE ID.           
  OUTFIL FNAMES=OUT2,INCLUDE=(20,2,CH,EQ,C'22'),OUTREC=(2,16)




IN1 and IN2 contain valid input data.
Can you please tell me what is wrong with this JCL?

Thanks,
Vidya Bhat
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Mon May 30, 2005 9:01 pm    Post subject:
Reply with quote

No, I can't tell you what's wrong with the JCL, because I don't know what your data looks like.

In my previous post, I asked:

Quote:
What is the RECFM and LRECL of the input files? What is the starting position, length and format of the key? Can input file1 have duplicates? Can input file2 have duplicates? Show me an example of the input records in each file, and what you want for the output files. Include duplicates in the example where relevent.


I can't help you until you provide this information.
Back to top
View user's profile Send private message
vidyasaraswathi

New User


Joined: 10 May 2005
Posts: 72

PostPosted: Tue May 31, 2005 10:18 am    Post subject: Re: Comparing two files
Reply with quote

Hi,

RECFM and LRECL of both input files are same.Also both files do not contain duplicate records.

RECFM=FB
LRECL= 133
starting position of the key:8
Length of the key:11
Data type of the key :numeric (PIC 9)


Input files contain the following data:

IN1
====
00000006319452271 0000000000000000 010 03/25/05 88.00
00000006321701130 4308517430358832 010 03/28/05 30.80
00000006321704385 4308517410068641 010 03/29/05 25.20
00000006321857033 4308517431316193 010 03/29/05 3.15
00000006321897787 4308517430280473 010 04/01/05 49.00
00000006321922625 4308517431185820 010 03/26/05 50.45
00000006321945560 4308517430273544 010 03/30/05 65.00
00000006321964611 4308517430048086 010 03/26/05 22.50
00000006322486179 4308517430161897 010 04/15/05 12.62
00000006322646052 4308517430103063 010 03/24/05 63.00


IN2
====
00000006321922625 4308517431185820 010 03/26/05 50.45
00000006321945560 4308517430273544 010 03/30/05 65.00
00000006321964611 4308517430048086 010 03/26/05 22.50
00000006322486179 4308517430161897 010 04/15/05 12.62
00000006322646052 4308517430103063 010 03/24/05 63.00
00000006322742450 4308517430190342 010 04/11/05 50.99
00000006322760270 4308517410017887 010 03/26/05 50.00
00000006322768794 4308517430275630 010 03/24/05 17.25
00000006322796542 4308517430741227 010 04/04/05 9.98
00000006322884360 0000000000000000 010 04/22/05 185.00

I dont want to include duplicates in the output file.


Thank you,
Vidya Bhat
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Tue May 31, 2005 9:12 pm    Post subject:
Reply with quote

You didn't show what you wanted the output to look like, as I asked.

Offhand, I see lots of problems with your job such as:

1) You say you have an 11-byte ZD key, but actually the last byte is always blank. It looks like a 10-byte ZD key to me.
2) You have OUTREC FIELDS=(8,11,20:C'11') which moves the key to positions 1-11. But then you use ON(8,11,ZD) instead of ON(1,11,ZD).
3) You use WITH(20,2) instead of WITH(21,1)
4) For some unknown reason, you added:

* MARK FILE3 RECORDS WITH '12'
OUTREC FIELDS=(8,11,20:C'12')

5) You use OUTREC=(2,16) which eliminates the first byte of the key.

I don't know if you want the entire record in the output file or just the key. You showed an example of the IN1 and IN2 records. Now, if you show me what the records should look like in OUT12, OUT1 and OUT2, I can show you the correct DFSORT/ICETOOL job.
Back to top
View user's profile Send private message
vidyasaraswathi

New User


Joined: 10 May 2005
Posts: 72

PostPosted: Wed Jun 01, 2005 8:03 pm    Post subject: Re: Comparing two files
Reply with quote

Hi Frank,

I want just the key in the output file:
Key Length is = 10


1)OUT1 should contain the records which are present only in IN1, but not in IN2.

That is,

6319452271
6321701130
6321704385
6321857033
6321897787


2)OUT2 should contain the records which are present only in IN2, but not in IN1.
6322742450
6322760270
6322768794
6322796542
6322884360


3)OUT12 should contain the records which are present in both IN1 and IN2.
6321922625
6321945560
6321964611
6322486179
6322646052

Please give me the correct DFSORT/ICETOOL job.

Thanks,
Vidya Bhat
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Wed Jun 01, 2005 9:55 pm    Post subject:
Reply with quote

Here's the DFSORT/ICETOOL job to do what you asked for:

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input file1
//IN2 DD DSN=... input file2
//OUT12 DD DSN=...  output file (F1/F2)
//OUT1 DD DSN=...   output file (F1 only)
//OUT2 DD DSN=...   output file (F2 only)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5)),
//  DISP=(MOD,PASS)
//TOOLIN DD *
* ADD '11' IDENTIFIER FOR FILE1 RECORDS.
COPY FROM(IN1) TO(T1) USING(CTL1)
* ADD '22' IDENTIFIER FOR FILE2 RECORDS.
COPY FROM(IN2) TO(T1) USING(CTL2)
* SPLICE TO MATCH UP RECORDS AND WRITE THEM TO THEIR
* APPROPRIATE OUTPUT FILES.
SPLICE FROM(T1) TO(OUT12) ON(1,10,ZD) WITH(12,1) -
  KEEPNODUPS USING(CTL3)
/*
//CTL1CNTL DD *
* MARK FILE1 RECORDS WITH '11'
  OUTREC FIELDS=(8,10,11:C'11')
/*
//CTL2CNTL DD *
* MARK FILE2 RECORDS WITH '22'
  OUTREC FIELDS=(8,10,11:C'22')
/*
//CTL3CNTL DD *
* WRITE MATCHING RECORDS TO OUT12 FILE. REMOVE ID.
  OUTFIL FNAMES=OUT12,INCLUDE=(11,2,CH,EQ,C'12'),OUTREC=(1,10)
* WRITE FILE1 ONLY RECORDS TO OUT1 FILE. REMOVE ID.
  OUTFIL FNAMES=OUT1,INCLUDE=(11,2,CH,EQ,C'11'),OUTREC=(1,10)
* WRITE FILE2 ONLY RECORDS TO OUT2 FILE. REMOVE ID.
  OUTFIL FNAMES=OUT2,INCLUDE=(11,2,CH,EQ,C'22'),OUTREC=(1,10)
/*
Back to top
View user's profile Send private message
vidyasaraswathi

New User


Joined: 10 May 2005
Posts: 72

PostPosted: Thu Jun 02, 2005 7:07 pm    Post subject: Re: Comparing two files
Reply with quote

Hi Frank,
Thank you very much for your help.
I ran the Job and got the expected results.

Thanks,
Vidya Bhat
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Splice more than 50 occurances rajatbagga DFSORT/ICETOOL 1 Wed Oct 11, 2017 9:36 am
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts File Aid tool to compare numeric data balaji81_k Compuware & Other Tools 2 Tue Sep 26, 2017 3:35 am
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm
No new posts Comparing 2 Files using Current time arunsoods SYNCSORT 9 Fri Sep 22, 2017 6:00 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us