Is a routine possible as an alternative to Lookup Stage?

[rajeshknl]

Hi

I have a situation where 1 sequential file populates 40 target tables. As i am using "External Filter stage" in my stream and other reasons i cant do a generic lookup before i split the data to 40 targets.

The temporary solution for this is i used 40 Lookup Stages one for each traget. The lookup is the last stage before the target.

But i realised i have to concatenate few columns(columns that i have retrived from Lookup) before i pass them to the target. I can do concatenate only in Trasnsformer stage so i need 40 Transformer stages now.

I am afraid the performance might be bad for large files. Each file size is approx 500MB.

My idea is to have a routine as first part of the sequence. This routine should pass the values looked up as parameters to the actual job.

My question now is " Is this feasible?" If so can anybody help me with writing the routine.

Any help is appreciated. Thanks in advance

[ray.wurlod]

But surely you'll end up with the same number of Transformer stages executing with this approach?

[ray.wurlod]

Moderator: please move to parallel forum

[rajeshknl]

I would have the same no of Transformer stages but atleast i can avoid Lookup stages. So how do i start to write the routine?

[ray.wurlod]

So how do i start to write the routine?

You would start by designing it. What do you want it to do? Create a precise specification, including connecting to and disconnecting from database, creating and dropping cursor, binding variables, and all the other things you need to do with your particular database API.