Routine that appends to SEQ File

[kovirago]

Hallo,

In a datastage routine, how can one append a record to the end of SEQ File?

This is what we would want the routine to do:
1. OPENSEQ
2. WRITESEQ (at end of the file)
3. CLOSESEQ

OPENSEQ, though, opens the file and moves the pointer to the beginning of the file? How can we move it to the end, or is there a better way to write the record?

 

[johnherman]

In the Inputs tab of the Sequential Stage, there is a checkbox to Append rather than Overwrite. I am using Data Stage 7 so not sure if earler versions have this option.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[kovirago]

Thanks for the reply. We would use the SEQ stage if we could, but the routine peforms a complex transformation so we must somehow write to the file within the routine.

The routine takes a record from a database table, performs a transformation that yields multiple new records. It is these new records that would be appended to the SEQ file.

So, for one record from the database table, the routine:
Opens SEQ
Transforms
Writes new records
Close SEQ

It will open and close the SEQ file for each record from the table. This is where the issue lies.. As I said before, the OPENSEQ opens the file and places the pointer at the beginning of the file. How can we move the pointer to the EOF easily, without reading the file each time?

One possibility was to open the SEQ file first in a different routine, call the transform routine, and then close the SEQ file in another. But I am not sure how to pass the FILE.Variable between routines..

 

[johnherman]

Here's an idea. Why not write to a new (separate) file, a sequential file. Then, cat (unix) or copy (win/DOS) the new file to the end of the file you want to append to.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[vmcburney]

A transformer can turn a single input row into multiple output rows by putting end of line characters into a field, eg. char(10). That way the routine can return back to the transformer a text that contains an array of values with char(10) between the records. The transformer would output it as a single large varchar field but when it gets written out by the sequential file stage it becomes many rows of data.

This is better in the row-by-row design of a DataStage job rather then trying to constantly open and close files from within a routine.

regards
Vincent
Blog: DataStage certification coming soon