Hi!
All the input files to the ICETOOL process below are VB.
The step executes and gives me the expected records in each of the four output files except the ouput files were created as FB.
I would like to keep the format as VB.
Anyone know what I'm missing or what I need to do differently?
Thanks
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
John W Perry,
You need to have a tail to retain the VB format ie. If the REFORMAT statement defines a position without a length (p without m) field, each joined record will be variable-length (TYPE=V).
That takes care of your original question, but your JCL is a mess and can be optimized. Please answer these questions
1. What is the LRECL of LOANWRK? In the output file you just need to move the value at position 15 next to RDW?
2. What is the LRECL and RECFM of FOREA, FORER24,FORES05 and FORES10?
3. Except for FOREA file all the other files have the key at position 15 which can be used to concatenate all of them into a single file (assuming they are VB files)
Updated JCL below.
All output files are now OK with VB 4011.
Answer to your questions:
1. LOANWRK is VB LRECL 4004
2. FOREA, FORER24,FORES05 and FORES10 are also VB 4004
3. I concatenated FORS05 & FORS10 under the same DD. FORER24 has the same key (15,7) but has some selection criteria so I kept this in a separate copy.
Question:
LOANWRK could contain 3 million records, will LOANWRK be processed three times in the step - once for each copy?
My goal is to end up with one file containing LOANWRK, OUTA, OUTR24 & OUTSXX merged/sorted by 5,7. I intended to add another SORT step to do this and expect to have 7 million records in the final file.
If you see any oportunity to optimize the process further please let me know.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
You are sorting LOANWRK three times. If not originally in order, you have data in order after the first sort.
If you're not using all the LOANWRK records, just the key, you could extract the keys from LOANWRK to an FB. Even if only using LOANWRK once, you can cut down the records to just the key, even if not sorting.
If nothing to identify source file when concatenating, it is possible to make something, a dummy "header" for each different file.
Are you other input files in key order, or do they require sorting for the JOINKEYS?
You wouldn't need to SORT your outputs, a simple MERGE would do, as they will certainly by that time be in key order.
Your INCLUDE as it stands is wasteful, because you are doing it on OUTFIL, rather than on input.
So, as a starting point, are the other files in key order already?
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
If all the files are in order for the match already, specify SORTED,NOSEQCK on the JOINKEYS statements.
Step 1
Use JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,4,?,F1:5,7,F2,5)
One OUTFIL takes all the Bs, a second OUTFIL takes everything, just BUILD the key and use VTOF. On your first OUTFIL, BUILD=(1,4,6) should get the key pre-pending your reocrds.
Use the second OUTFIL as input to the following steps.
Use INCLUDE on JNF2CNTL where you need it.
Seperate step for each process.
Final step to MERGE your files.
Unless there is a particular need to use ICETOOL (for functionality it provides) there is not much point in jamming everything together. If your third COPY bombs, you need to change the control cards before re-submitting, or waste all the time by doing it again.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
John w perry,
It is quite unnecessary to read the Loanwrk file to get the matched records. Here is a JCL which will give you the desired results reading the loanwrk file just once and the output is also sorted on your key, so you don't need another step.
I assumed that you don't have keys with spaces in loanwrk file. But if you do have spaces as keys in the loanwrk and want to retain them then it is an easy fix.
I made one change - 37,2,BI,EQ to 30,2,BI,EQ - and got the same results as my process which used multiple COPY steps. Your solution reducued CPU time & other resources by 50%.
In reality, I will be selecting records from 13 different files with a total of six different key positions and not just the four files I am currently testing with, so your solution will result in even greater resource savings.
FYI:
The seven byte key is PIC 9(13) comp-3 - so it will not contain spaces.
I will also have to write all the records in loanwrk to the output file.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Did you remember SORTED,NOSEQCK for LOANWRK?
I've also shown you how to include the LOANWRK in the output, same can be applied here.
It is alway a good idea to inform us of the full requirement. Solutons can change.
I'd suggest a slight change since you have 13 files. In the step to generate the dummy headers, I'd sequence then 01-13. This can be done with REPEAT on OUTFIL to get multiple records, then BUILD with the sequence number in the final two of the seven.
Then instead of using the ID, use the value on the record in the PUSH. It will make the code more stable if files are removed later, you won't have to "resequence" the code instead.
Saves a byte in the records as well :-) Two bytes, as you'd need a two-digit ID.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
John W Perry wrote:
Awesome,
I made one change - 37,2,BI,EQ to 30,2,BI,EQ - and got the same results as my process which used multiple COPY steps.
I have to ask as to why you changed the position. My solution is shifting the data contents by 8 bytes and that is the reason as to why I changed the check from position 29 to position 37. But now if you changed it back to 30 then, it would mean that you are now Validating data at position 22 instead of 29. Is that true?
John W Perry wrote:
In reality, I will be selecting records from 13 different files with a total of six different key positions and not just the four files I am currently testing with, so your solution will result in even greater resource savings.
I am guessing that you are fully aware of the changes that needs to be done. The file identifier is now increased 2 bytes and everything gets shifted by another byte. Make sure you change all the places where file identifier is being used.
John W Perry wrote:
The seven byte key is PIC 9(13) comp-3 - so it will not contain spaces.
I will also have to write all the records in loanwrk to the output file.
Remember that a PD field can have C or F as positive sign and D or B as negative sign. So if you have a mixed bag of keys with C and F then you need to normalize the keys before you match as Joinkeys does the matching in binary format
Bill,
I added SORTED,NOSEQCK which shaved another 10% of the CPU time.
JOINKEYS F2=LOANWRK,FIELDS=(5,7,A),SORTED,NOSEQCK
Kolusu,
Position 22 is correct. My JCL referenced the field in outfile processing after I had already added seven bytes.
I've not used DFSORT Group operations before, but I'm familiar with the other operations in the job. After a little trial & error, I should be able to make the updates.
