Page 1 of 1
bulk select/insert
Posted: Mon Jun 02, 2008 10:18 pm
by 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.
Posted: Mon Jun 02, 2008 10:23 pm
by 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.
Posted: Tue Jun 03, 2008 5:14 am
by chulett
So, what database are we talking about here? What stages are you currently using? Array Size? Transaction Size?
Re: bulk select/insert
Posted: Fri Jul 11, 2008 8:36 am
by yserrano
laiko wrote: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