DSXchange

Hi,
I am using bulk load option in oracle connector on target side and enable partition on source side. I am getting around 30 million records on source side. I just curious to know is there any better way to do transfer in efficient way.

Thank you,

If this is Oracle to Oracle you may find that doing the whole thing within Oracle using Oracle utilities is faster.

You may still choose to initiate/control that from DataStage.

If you prefer to use DataStage to design the approach, and you have Balanced Optimizer licensed, try using Balanced Optimizer to (design new jobs that) push some or all of the logic into Oracle.

If you are doing any transformation, then write the output in to a text file and find any way to load that file using bulk load command.

I did the same approach in SQL Server.

ray.wurlod wrote:If this is Oracle to Oracle you may find that doing the whole thing within Oracle using Oracle utilities is faster.

You may still choose to initiate/control that from DataStage.

If you prefer to ...

Hi Ray,
Could you please guide me in that direction, how can I initiate/control from DataStage?

Thank you,

SURA wrote:If you are doing any transformation, then write the output in to a text file and find any way to load that file using bulk load command.

I did the same approach in SQL Server.

Do you mean first write the entire data to a text file and then load into the oracle table ?

Thank you,

If your source and target is Oracle, then you can create a job that reads a dummy row from a Row Generator Stage and feeds it to an Oracle Connector Stage. In the Oracle Connector Stage, you can write a INSERT INTO...SELECT ...FROM query.

bond88 wrote: how can I initiate/control from DataStage?,

Easiest is an Execute Command activity in a sequence to initiate a sqlplus command (script).

bond88 wrote:Do you mean first write the entire data to a text file and then load into the oracle table ?

Yes mine will be a good only if you forced to do some transformation in the Datastage. Otherwise What Ray / others mentioned (to use oralce alone) would be good.

crystal_pup wrote:If your source and target is Oracle, then you can create a job that reads a dummy row from a Row Generator Stage and feeds it to an Oracle Connector Stage. In the Oracle Connector Stage, you can write a INSERT INTO...SELECT ...FROM query.

Thanks Sura.

ray.wurlod wrote:Easiest is an Execute Command activity in a sequence to initiate a sqlplus command (script). ...

Thanks Ray.