MChandrasekar
New User
.jpg)
Joined: 21 May 2007
Posts: 7
Location: Chennai
|
|
|
|
Hi,
I want a JCL TO MERGE TWO FILES WITHOUT DUPLICATE COLUMNS.
The Source file with columns
File1: RNO, Name, Marks
File 2: RNO, Name, Address
I want the JCL to merge the two source files and Produce the Output File with the following columns.
File3: RNo, Name, Marks, Address
Thanks in Advance. |
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
If you want to "join" the records by a key, you can use the technique discussed in the "Join fields from two files on a key" Smart DFSORT Trick. If you want to "join" the records one-by-one, you can use the technique discussed in the "Join fields from two files record-by-record" Smart DFSORT Trick.
Join fields from two files on a key
In this section, we show some tricks for joining fields from two files in different ways using the JOINKEYS function
of DFSORT, and the SPLICE operator of DFSORT's ICETOOL. Note that these are only a couple of examples
of the many ways you can do join operations with JOINKEYS and SPLICE.
Key in same place, no duplicates
A customer asked the following question:
I have two files with a key in bytes 1-3 and data in bytes 5-9.
File A has the following records:
000 $$$$$
001 AAAAA
002 CCCCC
003 EEEEE
004 GGGGG
and File B has the following records:
001 BBBBB
003 DDDDD
004 FFFFF
005 HHHHH
I want to join the data fields for pairs of records with the same key to get the following output:
001 AAAAA BBBBB
003 EEEEE DDDDD
004 GGGGG FFFFF
Note that each file is already sorted by the key.
Can I do that using DFSORT/ICETOOL?
Below is a DFSORT JOINKEYS job that can do it.
| Code: |
//JK1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... file 1
//IN2 DD DSN=... file 2
//OUT DD DSN=... output file
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(1,3,A),SORTED
JOINKEYS F2=IN2,FIELDS=(1,3,A),SORTED
REFORMAT FIELDS=(F1:1,9,F2:5,5)
OPTION COPY
OUTFIL FNAMES=OUT,BUILD=(1,9,X,10,5)
/* |
Alternatively, you can use the following ICETOOL SPLICE job to do it. The trick is to reformat the fields of the
IN1 and IN2 files so you can join them with the SPLICE operator.
| Code: |
//SPL1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... file 1
//IN2 DD DSN=... file 2
//TMP1 DD DSN=&&TEMP1,UNIT=SYSDA,SPACE=(CYL,(5,5)),
//** USE MOD FOR T1
// DISP=(MOD,PASS)
//OUT DD DSN=... output file
//TOOLIN DD *
* For this example, the fields (p,m) are as follows:
* IN1: sort key - 1,3
* f1fld - 5,5
* IN2: sort key - 1,3
* f2fld - 5,5
*
* Reformat the IN1 data set so it can be spliced
COPY FROM(IN1) TO(TMP1) USING(CPY1)
* Reformat the IN2 data set so it can be spliced
COPY FROM(IN2) TO(TMP1) USING(CPY2)
* Splice records with matching IN1/IN2 keys
SPLICE FROM(TMP1) TO(OUT) ON(1,3,CH) WITH(11,5)
/*
//CPY1CNTL DD *
* Use OUTREC to create |key |f1fld |blank|
OUTREC BUILD=(1:1,3,5:5,5,11:5X)
/*
//CPY2CNTL DD *
* Use OUTREC to create:|key |blank |f2fld|
OUTREC BUILD=(1:1,3,11:5,5)
/* |
Join fields from two files record-by-record
A customer asked the following question:
I want to merge two files laterally record by record. For example:
If FILE1 contains:
AAAAA
BBBBB
CCCCC
and FILE2 contains:
11111
22222
33333
then my output file should contain:
AAAAA11111
BBBBB22222
CCCCC33333
Can DFSORT do this?
Normally, we're asked how to join files on a key, but in this case there is no key; we just want to join the files
record-by-record. Well, no problem, we'll just create a key we can use by adding a sequence number (1, 2, ...) to
the records in file1 and a sequence number (1, 2, to the records in file2. Then we can join the fields from the 1
records, from the 2 records, and so on using the same technique we used for joining files on a key.
Here's a DFSORT JOINKEYS job that can do this.
| Code: |
//JK1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... file1
//IN2 DD DSN=... file2
//SORTOUT DD DSN=... output file
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(6,8,A),SORTED,NOSEQCK
JOINKEYS F2=IN2,FIELDS=(6,8,A),SORTED,NOSEQCK
REFORMAT FIELDS=(F1:1,5,F2:1,5)
OPTION COPY
/*
//JNF1CNTL DD *
INREC OVERLAY=(6:SEQNUM,8,BI)
/*
//JNF2CNTL DD *
INREC OVERLAY=(6:SEQNUM,8,BI)
/* |
|
|
