DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
dscatetl



Group memberships:
Premium Members

Joined: 19 Dec 2014
Posts: 2

Points: 55

Post Posted: Mon Apr 10, 2017 1:33 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Additional info: How to transform one record type into multiple record types?
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54539
Location: Sydney, Australia
Points: 295735

Post Posted: Tue Apr 11, 2017 5:07 am Reply with quote    Back to top    

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

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Rate this response:  
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43034
Location: Denver, CO
Points: 222128

Post Posted: Tue Apr 11, 2017 7:02 am Reply with quote    Back to top    

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

I've all my wisdom teeth, Two up top, two beneath
And yet I'll recognize my mouth says things that aren't so wise
Rate this response:  
rschirm

Premium Poster



Group memberships:
Premium Members, Inner Circle

Joined: 13 Dec 2002
Posts: 27

Points: 203

Post Posted: Tue Apr 11, 2017 10:52 am Reply with quote    Back to top    

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
Rate this response:  
rameshrr3



Group memberships:
Premium Members

Joined: 10 May 2004
Posts: 609
Location: BRENTWOOD, TN
Points: 6937

Post Posted: Tue Apr 11, 2017 11:17 am Reply with quote    Back to top    

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
Rate this response:  
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43034
Location: Denver, CO
Points: 222128

Post Posted: Tue Apr 11, 2017 1:03 pm Reply with quote    Back to top    

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

Or... just ask here. Wink

_________________
-craig

I've all my wisdom teeth, Two up top, two beneath
And yet I'll recognize my mouth says things that aren't so wise
Rate this response:  
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours