I have a requirement to merge 2 files (or more) sent via FTP from another system. These are to be merged into 1 file so that it can be passed on for processing in another environment.
I have tried using ICETOOL and advanced functions of DFSORT (well, advanced for me!), but failed to create the complete job. Sections have worked, others failed and other parts just did not work!
Previously I have been able to use Easytrieve and Selcopy for such jobs but they are now both barred by Company policies, so ICETOOl & DFSORT are the alternatives.
I have a requirement to merge 2 files (or more) sent via FTP from another system. These are to be merged into 1 file so that it can be passed on for processing in another environment.
I have tried using ICETOOL and advanced functions of DFSORT (well, advanced for me!), but failed to create the complete job. Sections have worked, others failed and other parts just didn’t work!
This came through as a ‘new’ request. Previously I have been able to use Easytrieve and Selcopy for such jobs but they are now both barred by Company policies, so ICETOOl & DFSORT are the alternatives.
The files are arranged in a 'sort-of' grouped manner:-
FIG being the first header;
2 more additional header records HDR1 & HDR2;
Data Header record UTL1 holds Date sent, format yyddd;
Data records:
All with same 14 byte key
Sorted within on Account then Date
Data record Value
2 trailer records EOF1 & EOF2;
Data Trailer record UTL1:
With accumulated Data-record Value’s (5-17),
With total number of Data-records (31-37).
For instance, arrives as:-
Code:
FIG Main header, just FIG
HDR1 file header, just HDR1
HDR2 file header, just HDR2
UHL1 Data header which includes date YYDDD.
11112222333344 …. Data record
11112222333344 …. Data record
11112222333344 etc multiple Data records
EOF1 file trailer, just EOF1
EOF2 file trailer, just EOF2
UTL1 Data trailer, includes totals of Data record
A strange sequence or data records, but that’s what I will be given.
I need to output just one set of records encompassing all data records. These data records can be in reverse ‘file’ order – that is can be grouped from File 2 first then File 1, etc - but must be sorted in Account/Date sequence within the File.
The Data header record UHL1 needs to be the last one sent (i.e. Most recent YYDDD); and the Data trailer record UTL1 needs to have the total number of Data records from all files, and the total Value of all these records updated.
The files will be stored in a GDG which I have input into a sort, but noticed they processed in reverse (last to first).
I then struggled to reprocess in first to last order.
Can you help me with this request, I am new to the side of DFSORT and keep reviewing examples & manuals but not been able to come up with the correct answer yet!
Note:
I had to blank or alter some sensitive Demographic details below for Data Security reasons.
Sample files and expected results are:-
Files are 100 character records, only detailed first 72 characters though
Important details below are (in order):-
UHL1 cols 06-10 Data header date YYDDD
Data record key cols 01-14 same for all data records
Data record Date cols 18-23 Date of Data record, within Account
Data record Value cols 36-46 Data record Value
Data record Account cols 65-72 Data record Account (can be multiple, sorted by Date)
UTL1 cols 05-43 Data trailer record ‘Total’ value of all data record Values
UTL1 cols 31-37 Data trailer record ‘Total’ number of Data records
Example File 1
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
FIG
HDR1
HDR2
UHL1 10165
11112222333344099130610 DIS B 00000015646F.I.G. 11111800
11112222333344099250210 DIS B 00000009772F.I.G. 11112800
11112222333344099270310 DIS B 00000009772F.I.G. 11112800
11112222333344099260410 DIS B 00000009772F.I.G. 11112800
11112222333344099260510 DIS B 00000009772F.I.G. 11112800
11112222333344099030410 DIS F 00000024816F.I.G. 11113200
EOF1
EOF2
UTL10000000079550 0000000000000000006 000000
Example File 2
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
FIG
HDR1
HDR2
UHL1 10166
11112222333344099130610 DIS B 00000015646F.I.G. 11116874
11112222333344099250210 DIS B 00000009772F.I.G. 11117882
11112222333344099270310 DIS B 00000009772F.I.G. 11117882
EOF1
EOF2
UTL10000000035190 0000000000000000003 000000
Expected Output file
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
FIG
HDR1
HDR2
UHL1 10166
11112222333344099130610 DIS B 00000015646F.I.G. 11111800
11112222333344099250210 DIS B 00000009772F.I.G. 11112800
11112222333344099270310 DIS B 00000009772F.I.G. 11112800
11112222333344099260410 DIS B 00000009772F.I.G. 11112800
11112222333344099260510 DIS B 00000009772F.I.G. 11112800
11112222333344099030410 DIS F 00000024816F.I.G. 11113200
11112222333344099130610 DIS B 00000015646F.I.G. 11116874
11112222333344099250210 DIS B 00000009772F.I.G. 11117882
11112222333344099270310 DIS B 00000009772F.I.G. 11117882
EOF1
EOF2
UTL10000000114740 0000000000000000009 000000
Many thanks for the solution, I coded it and the resulting file was fine except for one small area which I am quite happy to investigate further.
The solution has pointed me towards loads of functionality I want to read up on and understand. I realise you guys are experts on these queries but I need to learn, and this will certainly make me do that
As for the problem, I noticed that the UHL1 record was not the most recent record. The job ended up with 'UHL1 10165' instead of 'UHL1 10166'. It may have been how I explained the requirements, not sure.
The Input data will come from a GDG, I ended up with GDG's in reverse generation sequnce as input to the job, so that may have been the cause.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Graham Montague,
When you just give the GDG base the system concatenates the generations in the LIFO (last in first out ) order. So your latest generation will be concatenated first. Use the following DFSORT control cards which would give you the desired results which will retain your latest UHL info.
Sorry to be a nuisance yet again, but I have been trying my hardest to work out why the job now fails if I increase the number of GDG files to process to 3, or more?
All attempts I have tried give strange results?
Third GDG file added:-
********************************* Top of Data *********************************
FIG FILE3
HDR1 FILE3
HDR2 FILE3
UHL1 10167 FILE3
11112222333344099140610 DIS B 00000013000F.I.G. FILE3 11111300
EOF1 FILE3
EOF2 FILE3
UTL10000000013000 0000000000000000001 000000 FILE3
******************************** Bottom of Data *******************************
File output by ICETOOL after processing all 3 GDG's which is Input to sort job:-
********************************* Top of Data **********************************
FIG FILE3
HDR1 FILE3
HDR2 FILE3
UHL1 10167 FILE3
11112222333344099140610 DIS B 00000013000F.I.G. FILE3 11111300
EOF1 FILE3
EOF2 FILE3
UTL10000000013000 0000000000000000001 000000 FILE3
FIG FILE2
HDR1 FILE2
HDR2 FILE2
UHL1 10166 FILE2
11112222333344099130610 DIS B 00000015646F.I.G. FILE2 11116874
11112222333344099250210 DIS B 00000009772F.I.G. FILE2 11115882
11112222333344099270310 DIS B 00000009772F.I.G. FILE2 11115882
EOF1 FILE2
EOF2 FILE2
UTL10000000035190 0000000000000000003 000000 FILE2
FIG FILE1
HDR1 FILE1
HDR2 FILE1
UHL1 10165 FILE1
11112222333344099130610 DIS B 00000015646F.I.G. FILE1 11111800
11112222333344099250210 DIS B 00000009772F.I.G. FILE1 11112800
11112222333344099270310 DIS B 00000009772F.I.G. FILE1 11112800
11112222333344099260410 DIS B 00000009772F.I.G. FILE1 11112800
11112222333344099260510 DIS B 00000009772F.I.G. FILE1 11112800
11112222333344099030410 DIS F 00000024816F.I.G. FILE1 11113200
EOF1 FILE1
EOF2 FILE1
UTL10000000079550 0000000000000000006 000000 FILE1
******************************** Bottom of Data ********************************
********************************* Top of Data **********************************
FIG FILE2
HDR1 FILE2
HDR2 FILE2
UHL1 10166 FILE2
11112222333344099140610 DIS B 00000013000F.I.G. FILE3 11111300
11112222333344099130610 DIS B 00000015646F.I.G. FILE2 11116874
11112222333344099250210 DIS B 00000009772F.I.G. FILE2 11115882
11112222333344099270310 DIS B 00000009772F.I.G. FILE2 11115882
11112222333344099130610 DIS B 00000015646F.I.G. FILE1 11111800
11112222333344099250210 DIS B 00000009772F.I.G. FILE1 11112800
11112222333344099270310 DIS B 00000009772F.I.G. FILE1 11112800
11112222333344099260410 DIS B 00000009772F.I.G. FILE1 11112800
11112222333344099260510 DIS B 00000009772F.I.G. FILE1 11112800
11112222333344099030410 DIS F 00000024816F.I.G. FILE1 11113200
EOF1 FILE2
EOF2 FILE2
UTL10000000048190 0000000000000000004 000000 FILE2
FIG FILE1
HDR1 FILE1
HDR2 FILE1
UHL1 10165 FILE1
EOF1 FILE1
EOF2 FILE1
UTL10000000092550 0000000000000000007 000000 FILE1
******************************** Bottom of Data *******************************
Analysis of the sorted details is:-
1) It has retained the header UHL1 from the 2nd file, not 3rd?
2 The DATA record 11112222333344 for FILE 3 has been processed correctly though.
3) The trailer details are totally incorrect?:-
UTL10000000048190 0000000000000000004 000000 FILE2
FIG FILE1
HDR1 FILE1
HDR2 FILE1
UHL1 10165 FILE1
EOF1 FILE1
EOF2 FILE1
UTL10000000092550 0000000000000000007 000000 FILE1
******************************** Bottom of Data *************
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Graham Montague,
You would have a saved a lot of time of your time as well as my time if you had mentioned you are dealing with GDG Generations in the very first post itself. I can't keep on working 'n' different solutions as I have my regular work to finish.
Now Once again start over and answer the following questions
1. From which generation do you need the FIG, HDR1, HDR2 ,UHL, EOF & EOF2 Details?
2. Do you need to sum all the UTL records and write it out to 1 UTL record? If so does it matter from which generation did the UTL record come from?
Sorry, I had no intention of wasting your time. I take away each solution you have supplied and try my best to understand what you have developed. I didn't mention gdg on first posting, I did later but must have got lost within the long postings!!
To recap, I will be sent 2 or more files, max = 5, a day.
As I am uncertain how many will arrive I will save them to a GDG.
I couldn't get anything working to process the whole GDG in one go, so wrote ICETOOL job to read the gdg base and output a SINGLE file with all GDG's appended after each other. Ok, the details were in last-to-first gdg sequence but that didn't bother me as got most recent details First now
To answer your questions:-
1) FIG, HDR1, HDR2, EOF1 & EOF2 can come from ANY group of records as they don't contain any data.
UHL1 records hold the Date-sent, so I want the most recent Date-sent.
As for which generation, there are 2 answers!!
(i) if you process the file I created from ICETOOL of all gdg's appended after each other in last-to-first sequence, then you need any FIG, HDR1, HDR2, EOF1, EOF2 occurrence but the FIRST UHL1 occurrence as it will be the most recent.
or,
(ii) if you decide you want to process the whole gdg base in DFSORT then the details for FIG, HDR1, HDR2, EOF1, EOF2 can again come from any gdg but this time the UHL1 needs to be the LAST gdg - or just overwrite each read UHL1 and retain last details.
2) 'yes', I only need one UTL1 record with the accumulated totals of ALL UTL1 records.
'no', all UTL1 records are same format with their relevant totals.
Thank you for your time.
If you are still uncertain what my request is then please ask more questions as required. I don't want to waste any more of your valuable time Developing me yet another solution.
Unfortunately I don't know if you want to Develop a solution reading gdg's or not, so both options are still open. I have no preference, just what's easier
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Graham Montague,
use the following DFSORT JCL which will give you the desired results. It doesn't matter how many gdg generations are present.
The job is quite simple. We pad a 1 byte character followed by a seqnum of 9 bytes and 20 zoned zeroes at the end of every record ie pos 101.
Now if the first 3 bytes is FIG we modify the contents at pos 101 to 0 and 9 one's so that the record stays at top
Similarly we use different values for other header and trailer details so as to keep them in their place. All the detail records will have a 1 and a 9 byte seqnum at pos 101 which makes them unique.
Since you wanted the sum of all UTL values we overlay the contents of 5 and 31 on to pos 111 and 124
Now sorting on the 10 byte key at pos 101 , we sum the contents at pos 111 and 124. All the detail records will stay as is , but others are all summed into a single record.
While writing out we remove the extra bytes we added and update the UTL records.
Yes, that did the trick. All works 100% now. Thank you 
