DSXchange

Hello, I've been tasked with extracting data and sending it via a flat file to a vendor in a particular format. The challenge for me is that there are multiple record types in the file. I've only ever worked with one set of column definitions. This file consists of several different record types. One for the header and footer and three for detail data. The first two characters of each record comprise the first column which is the record type. The second column is an ID field or column. And the rest vary in number and type.

The data in the output file would look something like this:

FH LOAD 20170410 2.2 3365 1
CD 3365 1 23172 Doe, John
LD 3365 1 0000001 35784629
HL 3365 1 0000001 14723 N N
FF LOAD

How can I transform my input data, which is one record type, into these different record types (the CD,LD,HL)? The header and footer will be hardcoded. I read some posts regarding the complex flat file stage and I played around with it a little. Correct me if I am wrong, but that stage only allows multiple records types when reading (source) and NOT writing. See this quote from the IBM website "You cannot write to MVS data sets or to files that contain multiple record types."

Would any of the stages under "Restructure" work?

Thanks!

Construct each line as a single string (e.g. in a Transformer stage) and write into a single VarChar column. Add a field that allows you to use a sort merge collection algorithm to preserve the correct order, or run the Transformer stage in sequential mode.

Yup, have done exactly that in the past, custom sort rules and the whole nine yards. Or add yet another field that will allow your record types to sort 'naturally' and then drop the field at the end.

As the Ray and Craig have added I will add one more suggestion. Since you have one row coming in and you need basically 5 going out in the transformer take advantage of the Loop capability. This way you can produce the multiple rows at a time for each input record. If you need anymore detail on this email me at rick@dsxchange.net

Yeah - custom sorts and subsorts are what i could think of , give we implemented the very complex PVF Files for DTCC which are Hierarchical Flat files better suited for cobol based programming or Websphere TX or even Java ( which had predefined IPS messaging containers for such industry standard formats)
In my requirement i had to build all the individual record types separately as the logic to create each was different , concatenate them , and sort them internally for repeating groups, build global sort keys by trickery , sort them with Stable sort option = YES - so that i do not disturb locally sorted records, and then use cat command to add standardized headers and trailers with a execsh
I think an alternative algorithm would use a bunch of funnel stages in ordered mode , lookups ( with multi reslt enabled) and wave generators to insert wavebreaks if it works with Non realtime data sources - so that individual record groups could be used to create a wave - I have never got time to test it out.
Another option would be hierarchical data stage using XML , and then removing the XML with some chicanery - but the OP's requirement seems far more simpler that the DTCC formats.
IBM only supports read operations with PX CFF stages for multi format records , the write doesn't support as yet creating multi format records.
Even Mainframe edition now discontinued by IBM had no write for the MFF stage ( The Multi format flat file stage) or the IMS stages

rschirm wrote:If you need anymore detail on this email me

Or... just ask here.