Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Match records in two files and write the output in third.

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

New User


Joined: 14 Sep 2005
Posts: 74
Location: Kentucky, USA

PostPosted: Mon Aug 21, 2006 10:12 am    Post subject: Match records in two files and write the output in third.
Reply with quote

Hi,

I have two files File1 and File2 both having same record structure.
I have to match the records in both the files on he basis fo first two fields

ws-field1 pic 9(13)
ws-field2 pic 9(13)

and the write the output to a third file File3 if the records dont match.

Please can you give me a sort step to do the above task.
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 Aug 21, 2006 8:19 pm    Post subject:
Reply with quote

Please show an example of the records in each input file and what you expect for the output records. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example. Also, give the RECFM and LRECL of each input file.
Back to top
View user's profile Send private message
diwa_thilak

Active User


Joined: 13 Jul 2006
Posts: 205
Location: At my desk

PostPosted: Thu Nov 09, 2006 1:55 pm    Post subject: Re: Match records in two files and write the output in third
Reply with quote

Frank,

Both the files have same record structure

Record Format : FB
Record Length : 143
Block Size : 6149

Record 1 :

B,0054,0010,0000003491,0000003647,0000004307,0000005491,0000004435,000000363,0000003491,0000003647,0000004307,0000005491,0000004435,000000363

Record 2 :
B,0054,0010,0000002786,0000003647,0000004307,0000005491,0000004435,00000036300000003491,0000003647,0000004307,0000005491,0000004435,000000363

In this
1.B,0054,0010 is unique.
2.No Duplicates in both the files.

Now my requirement is to identify the records in file 1 which differ with file-2, if there is any change in the values in rest of fields.

kindly note that first value in both the files are different. so this record has to be the o/p of file 3.

Also the number of records in both the files may not be equal.

Is this possible thru DFSORT ?

Thanks,
Diwakar.D

I dont believe in the possiblities of Defeat. Bcos they dont exists
Back to top
View user's profile Send private message
callkris_cit

New User


Joined: 22 Apr 2006
Posts: 44

PostPosted: Mon Nov 13, 2006 2:49 pm    Post subject: Re: Match records in two files and write the output in third
Reply with quote

Assume,

ws-field1 pic 9(13)
ws-field2 pic 9(13)

Please try this below JCL: The below JCL work only
1. First file doesn't have the duplicates in the above 26 bytes
2. Second file doesn't have the duplicates in the above 26 bytes


//STEP30 EXEC PGM=ICETOOL
//SYSPRINT DD SYSOUT=*
//INP1 DD DISP=SHR,DSN= File name 1
// DD DISP=SHR, DSN= File name 2
//OUT1 DD DSN=Output file name,DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(20,30),RLSE),
// DCB=*.INP1
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(INP1) TO(OUT1) ON(1,26,CH) EQUAL(1)
/*
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 Nov 13, 2006 11:31 pm    Post subject:
Reply with quote

Diwakar.D,

It's still not clear what you want.

Do you want to compare the records on positions 13-143 or on some other positions?

I don't think you understood what I meant when I asked "if file1 can have duplicates within it". I don't mean duplicates between file1 and file2 (I know you have those) - I mean when you look at just file1, does it have duplicate records within it. Likewise for file2. I also asked you to "show an example of the records in each input file and what you expect for the output records". You only showed one record for each file. That doesn't help much. Please show an example with enough records in each input file to show all the variations you expect (matching records, non-matching records, duplicate records within file1 (if any), duplicate records within file2 (if any), etc. And show the expected output records.

Depending on what exactly your input records look like and what you want for output, you may need to use the SELECT or SPLICE operators of DFSORT's ICETOOL, so in order to help you, I need to understand in detail what you're trying to do.

Alternatively, you can see if the "Create files with matching and non-matching records" Smart DFSORT Trick will do what you want:

http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/

Or you can read all about SELECT and SPLICE yourself and try to figure it out.

Complete details on SELECT are at:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.11?DT=20060615185603

Complete details on SPLICE are at:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.13?DT=20060615185603
Back to top
View user's profile Send private message
diwa_thilak

Active User


Joined: 13 Jul 2006
Posts: 205
Location: At my desk

PostPosted: Wed Nov 15, 2006 7:32 pm    Post subject:
Reply with quote

Frank,

Please find the input for File-1

Q,0004,0010,0000027322,0000018886,0000025725,0000028674,0000017552,0000020740,0000022628,0000014909,0000024905,0000044621,0000021086,0000017080
Q,0004,0011,0000046283,0000049319,0000050179,0000067167,0000052085,0000053915,,0000045548,0000032650,0000036373,0000066072,0000024472,0000043541
Q,0004,0012,0000019644,0000015924,0000015873,0000020108,0000014193,0000013303,0000020297,0000011805,0000018399,0000033729,0000017015,0000020231
Q,0004,0014,0000023177,0000017534,0000026111,0000055910,0000041131,0000030924,0000024795,0000016135,0000034161,0000131205,0000009471,0000026398
Q,0004,0016,0000038489,0000024418,0000031728,0000050354,0000046313,0000047202,0000043882,0000028087,0000041037,0000216480,0000063216,0000080097
Input for File - 2

Q,0004,0010,0000026522,0000018886,0000025725,0000028674,0000017552,0000020740,0000022628,0000014909,0000024905,0000000000,0000000000,0000000000
Q,0004,0011,0000044283,0000049319,0000050179,0000067167,0000052085,0000053915,0000045548,0000032650,0000036373,0000000000,0000000000,0000000000
Q,0004,0012,0000013644,0000015924,0000015873,0000020108,0000014193,0000013303,0000020297,0000011805,0000018399,0000000000,0000000000,0000000000
Q,0004,0014,0000010177,0000017534,0000026111,0000055910,0000041131,0000030924,0000024795,0000016135,0000034161,0000000000,0000000000,0000000000
Q,0004,0016,0000038489,0000024418,0000031728,0000050354,0000046313,0000047202,0000043882,0000028087,0000041037,0000216480,0000063216,0000080097

If you could notice the first 3 records in both the file are not matching with respect to 4'th coloumn(also the last 4 cols).

I need all the 3 records from file-1 as o/p.

No duplicates within the file(both file 1 and file 2). But there can be insertions in the files. (i.e) a record in file -1 may not be present in file-2.

First o/p :I need to check from 13-143. any mismatch in this section record from file1 should be the o/p.

Second o/p :The records present only in file -1

Third o/p : Records only in file -2.

Hope i could make it clear for you.

Thanks for your help in advance.

Thanks
Diwakar

Nobody is perfect. I am nobody.
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 Nov 15, 2006 11:18 pm    Post subject:
Reply with quote

Quote:
Hope i could make it clear for you.


Unfortunately, you didn't.

Quote:
If you could notice the first 3 records in both the file are not matching with respect to 4'th coloumn(also the last 4 cols).


That seems to be true for the first 4 records.

I can't figure out what you want for output. Do you want one output file or three output files?

Quote:
First o/p :I need to check from 13-143. any mismatch in this section record from file1 should be the o/p.


What does this mean? You want a record from file1 that does not have a match in file2 on 13-143 in output file1? How is that different from:

Quote:
Second o/p :The records present only in file -1


I asked you to show the expected output records, but you didn't. That probably would have helped me understand what you want.

I suspect that some variation of the first trick in the "Create files with matching and non-matching records" Smart DFSORT Trick will do what you want. But I can't show you how to do it exactly unless you can make it clear what you want to do.
Back to top
View user's profile Send private message
diwa_thilak

Active User


Joined: 13 Jul 2006
Posts: 205
Location: At my desk

PostPosted: Thu Nov 16, 2006 5:20 pm    Post subject:
Reply with quote

Quote:
I can't figure out what you want for output. Do you want one output file or three output files?


3 output files.

Output 1 :Unmatched records:

(records present in both file-1 and file-2 and unmatched in 13-143)

Q,0004,0010,0000027322,0000018886,0000025725,0000028674,0000017552,0000020740,0000022628,0000014909,0000024905,0000044621,0000021086,0000017080
Q,0004,0011,0000046283,0000049319,0000050179,0000067167,0000052085,0000053915,,0000045548,0000032650,0000036373,0000066072,0000024472,0000043541
Q,0004,0012,0000019644,0000015924,0000015873,0000020108,0000014193,0000013303,0000020297,0000011805,0000018399,0000033729,0000017015,0000020231
Q,0004,0014,0000023177,0000017534,0000026111,0000055910,0000041131,0000030924,0000024795,0000016135,0000034161,0000131205,0000009471,0000026398

Output -2
Records present only in File - 1 (i.e no matching records in File-2 based on 1-13)

Output -3
Records present only in File - 2 (i.e no matching records in File-1 based on 1 -13)

Quote:
What does this mean? You want a record from file1 that does not have a match in file2 on 13-143 in output file1?


Exactly but based on 1-13.
(i.e)
First Check is 1-13 if both the files are same then
Second check is 13-143 should be same.
if 13-143 is same then go to next record
else
Write the record (from file-1) as unmatched record(1 record for each unmatched condition)
else
check if record only file-1
if yes.. write record in second o/p
else
write record in third o/p
Hope you could help me using sample code.

Thanks,
Diwakar



[/quote]
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 Read two inputs and write into output... murali.andaluri DFSORT/ICETOOL 5 Wed Jul 26, 2017 7:35 pm
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts JES2 doesn't honor the priority of th... Aron Lendvai JCL & VSAM 7 Thu Jun 29, 2017 1:41 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us