|
View previous topic :: View next topic
|
| Author |
Message |
santoshks0611
New User
Joined: 18 Apr 2013
Posts: 10
Location: India
|
|
|
|
Hi,
I have two files. I have to merge two files into one file. But I have to select only few but different columns from both the files. For eg:
File1: 111AAA222BBB333CCC
444DDD555EEE666FFF
File2: ZZZ999YYY888XXX777
WWW666VVV555UUU444
I need output file containing
111BBB
444EEE
999XXX
666UUU
Can anybody tell me if this is possible using ICETOOL or DFSORT utilities. If yes, which command can be used. Thanks! |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Is there a method or value could be used to identify if the data is from File1 or File 2? |
|
| Back to top |
|
|
santoshks0611
New User
Joined: 18 Apr 2013
Posts: 10
Location: India
|
|
|
|
Hi, Thanks for the reply.
There is no specific method actually. But if you see the above example. I have to copy column 1 to 3 and 10 to 12 from file1 and column 4 to 6 and 13 to 15 from file2. I dont have to check for any keys. Just I have to select these columns and write in a new file. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Yes this can be achieved through ICETOOL
you could achieve the solution through two passes of of COPY with CNTL
Search forum for aamples |
|
| Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
Is it possible that your two input files may have same values in those specific columns.
For example:
| Code: |
File1:
111AAA222BBB333CCC
333AAA222DDD333CCC
444DDD555EEE666FFF
File2:
ZZZ999YYY888XXX777
ZZZ333YYY888DDD777
WWW666VVV555UUU444 |
What should be the output in the above case? |
|
| Back to top |
|
|
santoshks0611
New User
Joined: 18 Apr 2013
Posts: 10
Location: India
|
|
|
|
Hi mistah kurtz, Thanks for the reply.
Yes it is possible to have same data. the output should be,
| Code: |
Output File:
111BBB
333DDD
444EEE
999XXX
333DDD
666UUU
|
First 3 rows from file1 and next 3 rows from file2. |
|
| Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
ok..In that case you cna try below SORT job(Assuming that all files are FB and LRECL=80):
| Code: |
//SRT01 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD *
111AAA222BBB333CCC
333AAA222DDD333CCC
444DDD555EEE666FFF
//SORTJNF2 DD *
ZZZ999YYY888XXX777
ZZZ333YYY888DDD777
WWW666VVV555UUU444
//*
//SORTOUT DD SYSOUT=*
//*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,3,A,10,3,A),SORTED,NOSEQCK
JOINKEYS FILE=F2,FIELDS=(4,3,A,13,3,A),SORTED,NOSEQCK
JOIN UNPAIRED,F1,F2
OPTION COPY
REFORMAT FIELDS=(?,F1:1,3,10,3,F2:4,3,13,3)
OUTFIL IFTHEN=(WHEN=(1,1,CH,EQ,C'1'),BUILD=(2,6)),
IFTHEN=(WHEN=(1,1,CH,EQ,C'2'),BUILD=(8,6))
/* |
Output will be SORTOUT:
| Code: |
111BBB
333DDD
444EEE
999XXX
333DDD
666UUU |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
mistah kurtz,
Having established that there may be equal values on the "keys" in the files, you will lose data if the last record of the first file has the same value as the first record of the second file.
You are only getting the correct output because the 999 value in the first record of the second file is "protecting" the other records on the second file from being selected "early". If the 999 appear last, the 333 record will appear in an inconvenient order.
santoshks0611,
What are the RECM and LRECL of your two input files? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Bill,
Why not use ICETOOL for this requirement? |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Yes, two copies to same output DD with DISP=MOD, USING(xxxx) and USING(yyyy) to do the data selection for the separate input DDs. |
|
| Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
Thanks Bill for pointing out the error..I was getting the feeling that something is wrong..my apologies for posting it as solution..
Hi Santosh,
Please try similar to this job:
| Code: |
//STEP01 EXEC PGM=ICETOOL
//DFSMSG DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//IN1 DD *
111AAA222BBB333CCC
333AAA222DDD333CCC
444DDD555EEE666FFF
//IN2 DD *
ZZZ999YYY888XXX777
ZZZ333YYY888DDD777
WWW666VVV555UUU444
//*
//OUT DD DSN=XXXXX.OUTPUT,DISP=(MOD,CATLG,DELETE),
// SPACE=(TRK,(1,1),RLSE),UNIT=DASD,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=0,DSORG=PS)
//*
//TOOLIN DD *
COPY FROM(IN1) TO(OUT) USING(CTL1)
COPY FROM(IN2) TO(OUT) USING(CTL2)
/*
//CTL1CNTL DD *
OUTFIL FNAMES=OUT,BUILD=(1,3,4:10,3,80:X)
/*
//CTL2CNTL DD *
OUTFIL FNAMES=OUT,BUILD=(4,3,4:13,3,80:X)
/* |
Hope this time it's fine :-) |
|
| Back to top |
|
|
|
|
