Writing multiple record types flat file

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
dscatetl
Premium Member
Premium Member
Posts: 2
Joined: Fri Dec 19, 2014 10:07 am

Writing multiple record types flat file

Post by dscatetl »

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!
dscatetl
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
rschirm
Premium Member
Premium Member
Posts: 27
Joined: Fri Dec 13, 2002 2:53 pm

Post by rschirm »

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
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Tricky but very very do-able.

Post by rameshrr3 »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

rschirm wrote:If you need anymore detail on this email me
Or... just ask here. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply