Implement Header/Detail/Footer loop in a Fixed Width File

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
richdhan
Premium Member
Premium Member
Posts: 364
Joined: Thu Feb 12, 2004 12:24 am

Implement Header/Detail/Footer loop in a Fixed Width File

Post by richdhan »

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

Post by chulett »

Sure, it's just more fun. :wink: 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 after job.

What I've done in the past for your other situation is to stage intermediate results into a Hash file. It's a little hard to explain, but I'd generate a 'sequencing key' for the hash that would allow me to pull the records back out (or sort them) into the proper sequence for delivery. Data would simply be your 251 character string. For example off the top of my head, the first header line might be keyed with an "A1", the detail lines with "B10001" thru "B10123" and then trailer "C1". Next set would be written with "A2", etc... anything that allowed you to get them out in the proper order.

They could be generated in one pass, if possible, or have multiple jobs working the problem - one to produce all of the headers, another all of the detail, etc. When you are done, access the hash with a UV stage or pass it through a Sort stage, remove the key and then write out your file. Since here each record as one big string, a single Sequential File stage can handle it.

Hope that makes sense,
-craig

"You can never have too many knives" -- Logan Nine Fingers
vmcburney
Participant
Posts: 3593
Joined: Thu Jan 23, 2003 5:25 pm
Location: Australia, Melbourne
Contact:

Post by vmcburney »

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.
richdhan
Premium Member
Premium Member
Posts: 364
Joined: Thu Feb 12, 2004 12:24 am

Post by richdhan »

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: Select all

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

Post by chulett »

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 "data" field of a fixed length.
-craig

"You can never have too many knives" -- Logan Nine Fingers
richdhan
Premium Member
Premium Member
Posts: 364
Joined: Thu Feb 12, 2004 12:24 am

Post by richdhan »

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

Post by chulett »

Exactly. :D
-craig

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