Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
JCL to merge two files without duplicates

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
MChandrasekar

New User


Joined: 21 May 2007
Posts: 7
Location: Chennai

PostPosted: Tue Jun 19, 2007 9:20 am    Post subject: JCL to merge two files without duplicates
Reply with quote

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.
Back to top
View user's profile Send private message

Devzee

Active Member


Joined: 20 Jan 2007
Posts: 684
Location: Hollywood

PostPosted: Tue Jun 19, 2007 9:24 am    Post subject:
Reply with quote

Use ICETOOL SPLICE 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: Tue Jun 19, 2007 8:49 pm    Post subject:
Reply with quote

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)
/*
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts Comparing 2 Files using Current time arunsoods SYNCSORT 9 Fri Sep 22, 2017 6:00 pm
No new posts Split 1 file into 10 output Files - S... Prasanth Kumar SYNCSORT 5 Sat Sep 16, 2017 12:02 am
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm
No new posts Match two files using multiple keys a... santoshks1987 SYNCSORT 11 Fri Aug 18, 2017 10:50 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us