MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Merge two files using a matching key

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Job Control Language(JCL)
View previous topic :: View next topic  
Author Message
Vallem
Beginner


Joined: 20 Feb 2003
Posts: 4
Topics: 2
PostPosted: Tue Mar 25, 2003 4:38 pm    Post subject: Merge two files using a matching key Reply with quote
I have a large file of 561 bytes and i need to fill it with data from a small file of 80 bytes using a matching key field.

File1: 561 bytes, key 7 bytes from position 2.
File2: 80 bytes, Key 7 bytes from position 1.

key is alphanumeric

Also for each key there are duplicates in 1st file as it is actually a partial key.


File1:
Code:

xAAAAAAABBBBBBCCCCCC33333333333333333333333333333333333
xAAAAAAADDDDDDCCCCCC3333333333333333333333333333333333
xSSSSSSSMMMMMMNNNNNN33333333333333333333333333333333333

File2:
Code:

AAAAAAAZZZZZZQQQQQQ
SSSSSSSPPPPPPPTTTTTTTT

Output Desired:
Code:

xAAAAAAABBBBBBCCCCCCZZZZZZQQQQQQ3333333333333333333333
xAAAAAAADDDDDDCCCCCCZZZZZZQQQQQQ333333333333333333333
xSSSSSSSMMMMMMNNNNNNPPPPPPTTTTTT333333333333333333333333
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
PostPosted: Tue Mar 25, 2003 4:49 pm    Post subject: Reply with quote
vallem,

Is easytrieve available at your shop?? If not what type of sort product(DFSORT,SYNCSORT, CA-SORT) is available ?

Also please specify the start and end positions of the data to be replaced from the short file.

Let me know and I will post the solution

Hope this helps...

cheers

kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Tue Mar 25, 2003 5:32 pm    Post subject: Reply with quote
Vallem,

If you have DFSORT R14 PTF UQ90053 (Feb, 2003) installed, you can use the new SPLICE operator of DFSORT's ICETOOL to do this and other kinds of joins. Below is the DFSORT/ICETOOL job to do what you described.

For complete information on DFSORT's new SPLICE operator, including explanations and examples of the base/overlay technique I've used here, see:

http://www.storage.ibm.com/software/sort/mvs/uq90053/online/srtmutol.html#spl

If you have DFSORT, but do not have PTF UQ90053 installed, ask your System Programmer to install it (it's free).

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//F1 DD DSN=...  input File1
//F2 DD DSN=...  input File2
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CONCT DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,PASS)
//      DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
//OUT DD DSN=...  output file
//TOOLIN DD *
* Reformat File2 records for splicing and add 'B' (base) id
  COPY FROM(F2) TO(T2) USING(CTL2)
* Add 'V' (overlay) id to File1 records
  COPY FROM(F1) TO(T1) USING(CTL1)
* Splice matching T2 (base) and T1 (overlay) records together to
* interleave fields.
  SPLICE FROM(CONCT) TO(OUT) ON(2,7,CH) WITHALL -
   WITH(1,1) WITH(9,12) WITH(33,529) USING(CTL3)
/*
//CTL2CNTL DD *
* Reformat File2 records and add 'B' id
  OUTREC FIELDS=(2:1,7,21:8,12,562:C'B')
/*
//CTL1CNTL DD *
* Add 'V' id to File1 records
  OUTREC FIELDS=(1,561,C'V')
/*
//CTL3CNTL DD *
* Delete spliced records with 'V' id since they represent
* records in File2 without a match in File1.
* Remove the id.
  OUTFIL FNAMES=OUT,OMIT=(562,1,CH,EQ,C'V'),
    OUTREC=(1,561)
/*

_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Vallem
Beginner


Joined: 20 Feb 2003
Posts: 4
Topics: 2
PostPosted: Tue Mar 25, 2003 6:06 pm    Post subject: Reply with quote
Kolusu,

We do not have Easytrieve. We have DFSORT utility. I need data from position 8 to position 19 in second file.

Frank, I tried your solution but SPLICE is not working in our installation.

Thanks,
Vallem
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Tue Mar 25, 2003 6:41 pm    Post subject: Reply with quote
Vallem,

Since you have DFSORT, ask your System Programmers to install DFSORT PTF UQ90053 (Feb, 2003) so you can use DFSORT's new SPLICE operator and the other new features available with this PTF as described at:

http://www.storage.ibm.com/software/sort/mvs/uq90053/index.html

Tell them you need the PTF installed to improve your productivity. 8)
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Job Control Language(JCL) All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group