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
 

 

compare 2 files with 3 different keys and append data

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

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Thu May 10, 2007 9:55 pm    Post subject: compare 2 files with 3 different keys and append data
Reply with quote

[img]
File 1: LRECL=42
----+----1----+----2----+----3----+----4--
011114111415024A 310? 0AE36913142 1210
021114111415024A 310` |0AE36928142 1210
031114111415024A 310` |0AE36924142 1210
021114111415024A 310` |0AE36925142 1210

File 2: LRECL=26
----+----1----+----2----+-
5024A01310Y7008275524
5024A02310Y700827008770058
5024A03310N

Output File: LRECL=58
----+----1----+----2----+----3----+----4----+----5----+---
011114111415024A 310? 0AE36913142 1210Y7008275524
021114111415024A 310` |0AE36928142 1210Y700827008770058
031114111415024A 310` |0AE36924142 1210N
021114111415024A 310` |0AE36925142 1210Y700827008770058[/img]
[/img]
Can SPLICE option in ICETOOL can compare the file1 with file2 in the three keys from(given in red,blue and green). when the match is found The values from 11 thru 26 in file 2 has to be appeneded in output file from 43 to 58.

Can somebody help me out?
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 10, 2007 10:30 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file (FB/42)
//IN2 DD DSN=...  input file (FB/26)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/58)
//TOOLIN   DD    *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(1,2,CH) ON(12,5,CH) ON(18,3,CH) -
  WITHALL WITH(1,39) WITH(59,8)
SORT FROM(T2) TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(1:6,2,12:1,5,18:8,3,40:11,16,59:8X)
/*
//CTL2CNTL DD *
  INREC OVERLAY=(59:SEQNUM,8,ZD)
/*
//CTL3CNTL DD *
  SORT FIELDS=(59,8,ZD,A)
  OUTREC BUILD=(1,58)
/*
Back to top
View user's profile Send private message
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Thu May 10, 2007 11:00 pm    Post subject:
Reply with quote

I am not able to understand why have you given

WITH(59,8)

59:8X

59:SEQNUM,8,ZD

59,8,ZD,A

in all the above you have given 8 chars after 58th position.. But I have no data after 58th Character.

ACn you explain me please?
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 11, 2007 1:05 am    Post subject:
Reply with quote

For SPLICE we need to sort on your keys which would put the output records in key order. Since you want your output records in the original order of the input records (at least that's what you show in your example), we need to add a sequence number to each input record so we can sort the SPLICEd records on that sequence number to get them back into the order you want. The extra SORT operator sorts on the added sequence number and then removes it.
Back to top
View user's profile Send private message
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Fri May 11, 2007 1:28 am    Post subject:
Reply with quote

SPLICE FROM(T1) TO(T2) ON(1,2,CH) ON(12,5,CH) ON(18,3,CH) -
WITHALL WITH(1,39) WITH(59,8)

in this the all ON was my conditions...
but wat does that WITH(1,39) means?
can you please tell me?

But thanks for the Code...Thank you so much icon_biggrin.gif
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 11, 2007 2:15 am    Post subject:
Reply with quote

Quote:
wat does that WITH(1,39) means?


It's used to SPLICE input positions 1-39 from file1 into the output records.

For complete information on DFSORT's SPLICE operator, see:

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
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Fri May 11, 2007 5:46 pm    Post subject:
Reply with quote

Your Code is working Perfect. thanks Once again! icon_biggrin.gif

But Wat will happen to the records that dont match from file 1?

I want those files also to be displayed..

Is there any option?
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 11, 2007 8:50 pm    Post subject:
Reply with quote

I based my solution on the input and output example you showed. If you need other variations, show me a better example of the input and output records including all of the variations and I'll show you how to do what you want.
Back to top
View user's profile Send private message
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Mon May 14, 2007 11:04 am    Post subject:
Reply with quote

File 1: LRECL=42
----+----1----+----2----+----3----+----4--
011114111415024A 310? 0AE36913142 1210
021114111415024A 310` |0AE36928142 1210
031114111415024A 310` |0AE36924142 1210
021114111415024A 310` |0AE36925142 1210
021114111415056A 310` |0AE36925142 1210
File 2: LRECL=26
----+----1----+----2----+-
5024A01310Y7008275524
5024A02310Y700827008770058
5024A03310N

Output File: LRECL=58
----+----1----+----2----+----3----+----4----+----5----+---
011114111415024A 310? 0AE36913142 1210Y7008275524
021114111415024A 310` |0AE36928142 1210Y700827008770058
031114111415024A 310` |0AE36924142 1210N
021114111415024A 310` |0AE36925142 1210Y700827008770058
021114111415056A 310` |0AE36925142 1210

I have added a Fifth record, which should ceom as it is, sicne there is no match for the reccord 5 in file 1 to that one in file 2.
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 14, 2007 8:32 pm    Post subject:
Reply with quote

For that, you just have to add KEEPNODUPS to SPLICE:

Code:

SPLICE FROM(T1) TO(T2) ON(1,2,CH) ON(12,5,CH) ON(18,3,CH) -
  WITHALL WITH(1,39) WITH(59,8) KEEPNODUPS
Back to top
View user's profile Send private message
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Mon May 14, 2007 10:54 pm    Post subject:
Reply with quote

My first input file had around 8000records.

The second input file had some 1500 records..

My requirement was to put all the 8000records as it is in the output file and the searched values if found had to be appended to those 8000 resepective record, or else leave the reocrd as it is.

But when i used KEEPNODUPS i got as much as around 9500records including the including the records that were used for matching I think...

But i want only those 8000 records from the file 1. HEnce do i need to use another sort step or can this be done in the same step of SPLICE?
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 14, 2007 11:40 pm    Post subject:
Reply with quote

You keep changing the requirement! I said
Quote:
I based my solution on the input and output example you showed. If you need other variations, show me a better example of the input and output records including all of the variations and I'll show you how to do what you want.


You first showed me an example where every record in file1 had a match in file2. I gave you the solution for that. Then you showed me an example where records in file1 did not have a match in file2 and I gave you the solution for that.

Are you now saying that records in file2 do not have a match in file1? Or do you have duplicates in file2? Or some other combination?

In any case, you need to show me a good example of the input and output for all of your variations. I can only go by what you tell me.
Back to top
View user's profile Send private message
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Tue May 15, 2007 12:05 am    Post subject:
Reply with quote

Sorry, I confused too much! icon_sad.gif

Thank you for the patience in answering my queries.

All the code you gave has worked absolutely perfect.

I will ask a clear question tomorrow with clear test data of my whole requirement. icon_question.gif

Thanks in Advance icon_biggrin.gif
Back to top
View user's profile Send private message
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Tue May 15, 2007 11:27 am    Post subject:
Reply with quote

My test Data:

Code:

File 1: LRECL=42
----+----1----+----2----+----3----+----4--
011114111415024A 310?  0AE36913142    1210
021114111415024A 310` |0AE36928142    1210
031114111415024A 310` |0AE36924142    1210
021114111415024A 310` |0AE36925555    1210
021114111415056A 310` |0AE36925142    1210
021114111418919A 760` |0AE36925142    1210

File 2: LRECL=26
----+----1----+----2----+-
5024A01310Y7008275524
5024A02310Y700827008770058
5024A03310N
5026A03310N
5027A07310N
8919A02310Y76668

Output File: LRECL=58
----+----1----+----2----+----3----+----4----+----5----+---
011114111415024A 310?  0AE36913142    1210Y7008275524
021114111415024A 310` |0AE36928142    1210Y700827008770058
031114111415024A 310` |0AE36924142    1210N
021114111415024A 310` |0AE36925555    1210Y700827008770058
021114111415056A 310` |0AE36925142    1210
021114111418919A 760` |0AE36925142    1210 


File 1(around 8000 records)
has duplicates(if we consider only the positions that are to be matched)
File 2(around 5000 records)
has no duplicates(by any means)

The key I mentioned remains the same.
first 2 position from file 1 has to be matched with 6th and 7th position in file 2 and
12th to 16th position from file 1 has to be matched with 1st to 5th position in file 2 and
18th to 20th position from file 1 has to be matched with 8th to 10th position in file 2


Search on the key for first record of file 1 with all the records of file 2.

If match found then append the characters of the matched record from 11th to 26th of file 2 to 43rd to 58th position of file 1 and copy it to the output file.

If no match is found then jus copy the record from file 1 to output file.

Search on the key for second record of file 1 with file 2 and so on...

The fifth and sixth record in file 1 has to be copied to output file as it is, since there is no match for them in file 2.

The 2nd and fourth record has same keys but different data in other position. For this case I need to jus append the found values. as in the 2nd and fourth record in output file.

The last 3 records from file 2 has no match with the file 1. Hence they should not be copied to the output file.

I think I have given a clear view of my requirement. Can you please look into this and suggest me a way to do it? It will be really helpful...

Thanks...
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 15, 2007 10:57 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file (FB/42)
//IN2 DD DSN=...  input file (FB/26)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/58)
//TOOLIN   DD    *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(1,2,CH) ON(12,5,CH) ON(18,3,CH) -
  WITHALL WITH(1,42) WITH(59,9) KEEPNODUPS
SORT FROM(T2) TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(1:6,2,12:1,5,18:8,3,43:11,16,59:8X,67:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(59:SEQNUM,8,ZD,67:C'VV')
/*
//CTL3CNTL DD *
  OMIT COND=(67,2,CH,EQ,C'BB')
  SORT FIELDS=(59,8,ZD,A)
  OUTREC BUILD=(1,58)
/*
Back to top
View user's profile Send private message
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Wed May 16, 2007 10:07 am    Post subject:
Reply with quote

Code:
WITHALL WITH(1,42) WITH(59,9) KEEPNODUPS


I believe you meant to give WITH(59,10) instead of WITH(59,9).

For me all the things you gave worked perfectly..

Thank you so much for all the responses. In this way I have learnt some options in ICETOOL effectively.

Thanks once again. icon_biggrin.gif
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 May 16, 2007 8:21 pm    Post subject:
Reply with quote

No, I meant to use WITH(59,9) to get the sequence number and one byte of the two byte id so we would get VB for matched records, BB for file1 only records and VV for file2 only records. With WITH(59,10) you'll get BB for file1 only records and VV for file2 only or matched records. For your particular case, either way would work since you only want the BB records. But in the general case of matched records, file1 records and file2 records, you'd need WITH(59,9) rather than WITH(59,10).
Back to top
View user's profile Send private message
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Thu May 17, 2007 12:31 am    Post subject:
Reply with quote

Great!!!!

I dint think about the VB thing u mentioned. Now its clear. Its all perfect...

Thanks for the reply.... icon_biggrin.gif
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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm


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