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



Group memberships:
Premium Members

Joined: 12 Feb 2004
Posts: 364

Points: 2614

Post Posted: Mon Aug 30, 2004 3:19 am Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Server
OS: Unix
Additional info: 7.1 on AIX
Hi,

I have a scenario where the source is oracle and the target is a fixed width flat file. The fixed width flat file should have header, detail, trailer and last trailer records.

Each file can have multiple header, detail and trailer records based on the number of suppliers. The size of the records for header, detail, trailer and last trailer is the same and accounts to 251 characters but the number of columns across the header, detail, trailer and last trailer are different.

If it is for a single supplier then I can have 4 jobs for header, detail, trailer and last trailer in a sequence and use the cat command to combine the data but number of suppliers varies at runtime.

Is this feasible in DS? If so how can I achieve this. Your inputs would be valuable.

Thanks in advance
--Rich
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42621
Location: Denver, CO
Points: 219439

Post Posted: Mon Aug 30, 2004 7:40 am Reply with quote    Back to top    

Sure, it's just more fun. FWIW, I usually do your "Single Supplier" scenario in one job, making use of multiple streams and aggregators to produce the individual files and then cat them together aft ...

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
vmcburney

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 23 Jan 2003
Posts: 3582
Location: Australia, Melbourne
Points: 27998

Post Posted: Mon Aug 30, 2004 10:17 pm Reply with quote    Back to top    

I just answered this on devnet! Didn't catch up with all the weekend posts so I missed Craig's answer. The devnet answer is almost the same except it replaces the hash file with a link collector and adds a MergeRow stage to concatenate multiple columns into a single text string.

Both designs do the same thing, they split the data up into multiple streams, add a key field to define the correct order and then merge the streams back together again.

_________________
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn: Vincent McBurney LinkedIn
Rate this response:  
Not yet rated
richdhan



Group memberships:
Premium Members

Joined: 12 Feb 2004
Posts: 364

Points: 2614

Post Posted: Tue Aug 31, 2004 8:06 am Reply with quote    Back to top    

Craig & Vincent,

Thanks for your responses. I have been working on both of your inputs.

To make things easier I'm using a sequential file stage as source which has 2 columns empno and empname. From the sequential file stage I have 2 output streams feeding 2 transformers. The output of the first transformer is Gkey(A1) and empno. The output of the second transformer is Gkey(B1), empno and empname.

Using Craig's method I write the output of the transformers to the same hash file. I used the hashed file as source and loaded a flat file with fixed width columns and it worked fine. But in real time the data I would get is very complex. The number of columns and column lengths would not match against header and detail. It would be something like this

Header
--------
Record Type 1 X(2)
Data Source 3 X(10)
Report Type 13 X(10)
Supplier 23 X(5)

Detail
-------
Record Type 1 X(2)
Supplier 3 X(5)
Group 8 X(2)
Prefix 10 X(7)

In which case how would I define the metadata of the target hash file.

Using Vincent's method

Vincent-ADN wrote:
In this case RowMerge turns each of the four streams, with different metadata definitions, into one long text string so they can be combined by a link collector.


Row Merger accepts only one input link. So how can it combine four input streams into one long text string.

I used 2 Row mergers for outputs from 2 transformers and used the Link collector stage. But during compilation Iam getting the following error.

Code:
Link Collector stage does not support in-process active-to-active inputs or outputs


So Iam held up at this point. Looking forward for your feedback.

Thanks and Regards
--Rich
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42621
Location: Denver, CO
Points: 219439

Post Posted: Tue Aug 31, 2004 8:39 am Reply with quote    Back to top    

richdhan wrote: In which case how would I define the metadata of the target hash file. I'd suggest building your complete formatted output record and then putting it in the hash as a single ...

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
richdhan



Group memberships:
Premium Members

Joined: 12 Feb 2004
Posts: 364

Points: 2614

Post Posted: Tue Aug 31, 2004 9:10 am Reply with quote    Back to top    

Craig,

Thanks for your feedback. So the outputs from the header and detail transformer is to be formatted(header and detail columns need to be concatenated) so that there will be 2 output columns one being the key and the other being the data field of 251 characters. Is my understanding right?

Best Regards
--Rich

Pride comes before a fall
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42621
Location: Denver, CO
Points: 219439

Post Posted: Tue Aug 31, 2004 9:17 am Reply with quote    Back to top    

Exactly. Very Happy

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
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