I have 2 different files with different length, how can I combine the contents of the 2 files based on one key into one output file.
For example: File 1 and File2 are going to combine on column key1. If record from File1 is not found in File2 on key1 then it will be assigned blanks for other columns.
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
It shouldn't matter, if you have DFSort, you have ICETOOL.
And DFSORT is called to sort the input data sets so the order shouldn't matter either.
Check out the two "Join fields from two files" in Smart DFSORT Tricks
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
skhorizon,
Here's a DFSORT/ICETOOL job that will do what you asked for. If you have DFSORT, then you have DFSORT's ICETOOL (it has been available with DFSORT since 1991).
And the size of fileds is :
key1 (3 chars) , col1,col2 col3 (4 chars )
key2(3 chars) , col4,col5 (4 chars)
File 1 can have duplicate key values , but File2 will have one key values .So takin key value from File1 corrsponding to that value from File2 we have to find out value of " col4 " and if not found in File2 then assign blank in output value.
In output file it will have all records as file1 with new column " col4 " derived on basis key
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Quote:
But the result is not as expected .
Garbage in - garbage out.
I suppose you expected me to read your mind to figure out that even though you showed blanks between the fields they weren't there, and that even though you didn't show duplicates they were there.
Do both files have RECFM=FB and LRECL=80?
Can input file2 have a key that isn't in input file1?
Can input file1 have a key that isn't in input file2?
Show me a good example of the input records in each file and what you expect for output with all of the possible variations. Since you say input file1 can have duplicate key values, show that in your example.
Sorry that i have not shown in my previous post about duplicate rows .
Yes ,both files have RECFM=FB and LRECL=80 .
Yes ,input file2 can have a key that isn't in input file1 .
Yes ,input file1 can have a key that isn't in input file2.
Basically the requirement is that file1 have 1 key field and 3 other column . file1 can have duplicate key values .
file2 have 1 key field and 2 other columns. All key values in file2 are unique.
Then ,output file will have all records from file1 with one more column from file2 .
we have to match the key from file1 to file2 , and get the column value from file2 and append to output file .if the key is not found in file2 then assign blank value in the output file for that new column .
I have not much idea about DFSORT/ICETOOL ,so can u please explain what does the following line means .i tried to get answer from DFSORT programming guide but was not able to understand clearly :
1 .SPLICE FROM(T1) TO(OUT) ON(5,3,CH) KEEPNODUPS KEEPBASE -
WITHALL WITH(5,15) WITH(81,1) USING(CTL3) , what does WITHALL WITH(5,15) WITH(81,1) means ?
2. INREC BUILD=(1:8,4,5:5,3,81:C'BB') what does 'BB' means here ?
3. INREC BUILD=(5:1,15,81:C'VV') what does 'VV' means here ?
4. OUTFIL FNAMES=OUT,OMIT=(81,2,CH,EQ,C'BB'),
BUILD=(1,80) , what does OMIT=(81,2,CH,EQ,C'BB') means ?
5. why we are using 81 here , whats its signinficance ?
It might also help if you display the records in T1 so you can see what they look like before they are spliced, and if you comment out the control statements in CTL3CNTL so you can see what the spliced records look like before OUTFIL removes the BB records and the ids.
I can give you some brief answers to your questions, but it may not help if you haven't spent the time to understand SPLICE.
Quote:
1 .SPLICE FROM(T1) TO(OUT) ON(5,3,CH) KEEPNODUPS KEEPBASE -
WITHALL WITH(5,15) WITH(81,1) USING(CTL3) , what does WITHALL WITH(5,15) WITH(81,1) means ?
WITHALL splices the base record (from file2) with each matching overlay record (from file1). WITH(5,15) and WITH(81,1) gives the fields from the overlay record to be spliced into the base record.
Quote:
2. INREC BUILD=(1:8,4,5:5,3,81:C'BB') what does 'BB' means here ?
BB is an id code we're using to identify the records from file2. The file2 records are the base records.
Quote:
3. INREC BUILD=(5:1,15,81:C'VV') what does 'VV' means here ?
VV is an id code we're using to identify the records from file1. The file1 records are the overlay records.
Quote:
4. OUTFIL FNAMES=OUT,OMIT=(81,2,CH,EQ,C'BB'),
BUILD=(1,80) , what does OMIT=(81,2,CH,EQ,C'BB') means ?
We're removing the records with an id of BB (records with keys that only appear in file2). We're keeping the records with an id of VV (records with keys that only appear in file1), and with an id of VB (records that appear in file2 and file1).
Quote:
5. why we are using 81 here , whats its signinficance ?
We're adding the ids after the end of the records. The LRECL is 80, so the position after the end of the records is 81.
Quote:
6. how it is working : step by step concept ?
See the explanations above and the referenced Smart DFSORT Trick.
I wanted to know what will be the change in the code if the file length will be equal to record length .Then both input file will be of different record length and output will be different lrecl .
The code is working in run member .
now the problem is that we have to use same code in many jobs. So i tried to write in PROC .calling the same proc in all jobs.
If run through PROC , then it gives JCL error as RC for the job . Its not recognizing from the statement
//TOOLIN DD *
.
.
/* (till last )
And same thing if written in job its giving RC=0 and running successfully.
Please help in finding the solution for this problem .
Do i need to add any system libary for running ICETOOL pgm . ?
Actually i wanted to run the whole STEP from a proc . But when i am running through proc it is giving JCL error saying INVALID JCL STATEMENT for all below mentiioned lines .
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
skhorizon wrote:
Actually i wanted to run the whole STEP from a proc . But when i am running through proc it is giving JCL error saying INVALID JCL STATEMENT for all below mentiioned lines .
Quote:
And same thing running through job , is running successfully .
So i wanted in whole step running through proc .
