Joined: 05 Jun 2009 Posts: 185 Location: Planet Earth
Hello all,
I have two input files (say file1 and file2). Both the input files have same record layout. Let us assume a record length of 10 bytes each. But the input files are in sorted order. I want to copy the records from file1 into a output file (say file3) based on the following criteria,
1) If col1 of file1 matches with col1 of file2(any record, not just the same record number), then copy only if col2 not equal to zeroes. [assuming col1 occupies 3-5 pos and col2 occupies 7,8 pos]
2) If col1 of file1 doesn't match with col1 of file2, then copy the record.
I was reading through ICETOOL options to find out ways to do this. But could not find any clue as in how to start with control card to process records by comparing records in one file with records in another file. That too not the same record number necessarily.
Can anyone pls advice me if this is feasible using DFSORT /ICETOOL ???
This SORT card works fine as per my requirement when the splicing field (pos 1 - 25) has unique values..
But when the first 25 bytes has duplicate records in any case then the duplicate record is not getting filtered in the output. Can anybody advice how this can be achieved for duplicate records as well ???
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
rockish,
Does both files have duplicates? or is it just 1 file which has duplicates? Splice can handle "one to one" "one to many" match but has a limitation with "many to many" match.
Please provide the following details
1. What is the LRECL and RECFM of File1
2. What is the Key position and format in file1
3. What is the LRECL and RECFM of File2
4. What is the Key position and format in file2
5. Which file has duplicates?
6. What is lrecl and recfm of the output file?
7. What are the fields that needs to picked from file1 and file2
Joined: 05 Jun 2009 Posts: 185 Location: Planet Earth
Hi Kolusu,
I will first answer your 7 questions before explaining for the first question on duplicates.
1. What is the LRECL and RECFM of File1
LRECL=150,RECFM=FB
2. What is the Key position and format in file1
Key Position=1:25, Format=9(25)
3. What is the LRECL and RECFM of File2
LRECL=150,RECFM=FB
4. What is the Key position and format in file2
Key Position=1:25, Format=9(25)
5. Which file has duplicates?
Both the file can have duplicates. More explanation below.
6. What is lrecl and recfm of the output file?
LRECL=150,RECFM=FB
7. What are the fields that needs to picked from file1 and file2
All the 150 bytes from file1 is picked for output. File2 content not required in the output.
Now, to explain in detail about the duplicate records. Ideally both the files can have duplicates. As already mentioned, it is only the records from file 1 that would be getting picked into the output file. The various cases possible are,
Case 1 : 1 Instance in File 1, 1 instance in File 2
Desired Output : Record from file 1 getting picked for output
Case 2 : 1 instance in File 1, more than one in File 2
Desired Output : The only record from file 1 getting picked for output
Case 3 : More than one instance in File 1, only one instance in file 2
Desired Output : All the instances in file 1 getting picked for output
Case 4 : More than one instance in both file1 and file2
Desired Output : All the instances from file 1 getting picked for output irrespective of the exact number of occurrences.
I hope that gives you some understanding of what I want to achieve !!
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
rockish,
Based on your requirements, the following DFSORT/ICETOOL JCl will give you the desired results. The trick here is to eliminate the duplicates in file 2 as we concerned only on picking the records from file1.
1. First sort operation eliminates duplicates from file2 and creates the file with unique keys and an indicator '22' in pos 151
2. Second copy operation just copies the file 1 with an indicator at pos 151
Once spliced the matching records all will have '12' as indicator and we pick them using an include cond and get rid of the indicator
Joined: 05 Jun 2009 Posts: 185 Location: Planet Earth
Thanks for your reply,
My output was not what I expected :-(
I think here the problem will come for the following case,
File 1 - Multiple instance of the value.
File 2 - No instance of the value.
In such a situation I expect the record from File 1 to be written. But here the splicing will happen among the records in file 1 and ending in losing a record..
Joined: 05 Jun 2009 Posts: 185 Location: Planet Earth
As i have missed few of the points that I mentioned in the first post, when I answered your questions, there has been some confusion.
I would rather summarize my requirement one more time.. I actually have a cobol program. Which processes two files to generate a output file.
Both the input files are RECFM-FB and LRECL-150
Desired output file also RECFM-FB and LRECL-150
Sort key of both the input files: 1-25 (record type 9(25))
The program does the following process,
1) Read the first record from file1 and file2.
2) Compare the records based on the key (first 25 bytes, let us call it as field 1)
3) If field1 of file 1 < field1 of file 2, read the next record from file 1.
4) If the field1 of file 1 > field1 of file 2, check if the field3 (pos 17,6) from file 2 is greater than a 00 or not. If greater, write the record from file2 in to the output file. If not skip the record. Read the next record from file 2.
5) If field 1 of file1 = field 1 of file2, check for two conditions,
(i) is field 3 (17,6) from file 2 is greater than 0 ?
(ii) is filed 4 (88,1) from file 1 is not equal to zero ?
If both the conditions are satisfied, write the record from file2. If not skip and read the next record from file 1 and file2.
6) Continue this until EOF is reached.
7) If file1 EOF is reached before EOF of file2, write all the records there after from file2, if field 3 is greater than 0.
I felt this can be replaced using ICETOOL. The JCL I used is available below,
This code works fine, if there are no duplicate records in the first 25 bytes in both the files. But now that my input file (both file1 and file2) can hold duplicates, I wish to enhance this JCL to support that as well.
Could you pls suggest if that is feasible ??
Please revert if you have any doubts over my requirement.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
rockish,
wow , I don't see a point in providing you with an answer when you code your own logic to get the desired results and point out that my solution does NOT work.
Did you even come close to copying my code as is? The job I have provided earlier completely satisfies all the conditions you mentioned in your last post. Now you change the requirement completely.
You don't even pay attention to how I used DISP=MOD instead of concatenating the two temp files. If you are seeking help from us, try to use the solution provided instead of coming up with your own logic and asking us to correct it.
Isn't your key(1,25) overlapping the field 3 (17,6) to check?
If you need help , you need to show me relevant fields and format of the fileds with examples for both inputs and outputs. cover all requirements in one single post, don't keep on changing the requirements as I have my regular job to attend.
Joined: 05 Jun 2009 Posts: 185 Location: Planet Earth
Kolusu,
First of all, I am terribly sorry for troubling you.
Actually I was trying to clarify the concept from experts and code a little on my own. That is why I was quoting simple examples which is similar to my actual problem statement. And based on your advice, I was coding for the actual files.
Regarding the usage of MOD and avoiding the concatenation, I swear I replaced the concatenation with a single DD usage.. But the only mistake I made was I dint note the (MOD,PASS) and used (,PASS) and that is why I got undesired results.. Now after I made that change also to the code I had, it works :-)
The JCL that I pasted you in the most recent post was a old one that I had working for unique records before I got first comments from you.. Though I posted that, I was sincerely trying based on your suggestions.
Anyways, after testing your temper, I have somehow managed to achieve what I wanted to. My requirement is now working, thanks to your suggestion. All the troubles were only because of that MOD which I failed to note and you correctly pointed me out..
Finally, thanks once again for helping me out. And a BIG SORRY one more time for troubling you by not being clear on my requirements.
I assure you, I would be 200% clear in my future post.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
rockish,
I don't mind answering questions as I long as I get the requirement clearly. Frank and my self answer questions related to DFSORT across 5 or 6 helpboards and via email apart from our regular work. It gets tiring when we have to go back and forth on the same topic but with a different requirement each time. Of course frank has lot of patience but I just happen to snap once in a while. sorry about that
