bulk select/insert

[laiko]

Hi,

Is there such thing as bulk select and insert in DataStage? I have something like:

Code: Select all

 SELECT from remote DB  --> transformer  --> INSERT to DW

I noticed that the records are one by one going through the transformer, then to INSERT stage. I find this very slow. Is it possible to bulk colelct them before inserting? There's no stage variables also in my transformer, just TRUNC functions.

Thanks in advance.

[ray.wurlod]

Not as directly as you would like. Depending on the actual database some degree of prefetch buffering and in-process row buffering may be possible. Also depending on the actual database you may be able to invoke its export facility to bulk unload the data as the Filter command of a Sequential File stage, or in a pre-job script. These are just some ideas - there are doubtless other ways as well.

[chulett]

So, what database are we talking about here? What stages are you currently using? Array Size? Transaction Size?

[yserrano]

Hi,

Is there such thing as bulk select and insert in DataStage? I have something like:

Code: Select all

 SELECT from remote DB  --> transformer  --> INSERT to DW

I noticed that the records are one by one going through the transformer, then to INSERT stage. I find this very slow. Is it possible to bulk colelct them before inserting? There's no stage variables also in my transformer, just TRUNC functions.

Thanks in advance.

You could trick it like this:

Code: Select all

 SELECT from remote DB  --> Sequential stage -->transformer  --> INSERT to DW