Bulk load

bond88 · Post by **bond88** » Wed Jun 26, 2013 11:18 am

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,

ray.wurlod · Post by **ray.wurlod** » Wed Jun 26, 2013 4:16 pm

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.

SURA · Post by **SURA** » Wed Jun 26, 2013 5:34 pm

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.

bond88 · Post by **bond88** » Thu Jun 27, 2013 7:17 am

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,

bond88 · Post by **bond88** » Thu Jun 27, 2013 7:19 am

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,

crystal_pup · Post by **crystal_pup** » Thu Jun 27, 2013 8:41 am

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.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 27, 2013 3:57 pm

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

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

SURA · Post by **SURA** » Thu Jun 27, 2013 5:51 pm

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.

bond88 · Post by **bond88** » Fri Jun 28, 2013 8:58 am

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.

bond88 · Post by **bond88** » Fri Jun 28, 2013 8:59 am

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

Thanks Ray.

DSXchange

Bulk load

Bulk load

Re: Bulk load

Re: Bulk load

Re: Bulk load