MVSFORUMS.com :: View topic - Merge two files using a matching key

through

[^/[Print]\]

MVSFORUMS.com -> Job Control Language(JCL)

#1: Merge two files using a matching key Author: Vallem,

Posted: Tue Mar 25, 2003 4:38 pm
—
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

#2: Author: kolusu, Location: San Jose

Posted: Tue Mar 25, 2003 4:49 pm
—
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

#3: Author: Frank Yaeger, Location: San Jose

Posted: Tue Mar 25, 2003 5:32 pm
—
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)
/*

#4: Author: Vallem,

Posted: Tue Mar 25, 2003 6:06 pm
—
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

#5: Author: Frank Yaeger, Location: San Jose

Posted: Tue Mar 25, 2003 6:41 pm
—
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)

MVSFORUMS.com -> Job Control Language(JCL)

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1