Ora bulk loader Stage

yaminids · Post by **yaminids** » Thu Jun 16, 2005 4:35 am

Hello friends,

Can anyone help me with improving the performance of Ora bulk loader Stage (OCI 9i Load)? The only field, which I thought would improve the performance is the "Number of Records" field on the 'Properties' page.

Does increasing the Number of records filed would improve the performance?

Thanks a lot in advance
Yamini

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jun 16, 2005 4:38 am

SQL Loader works better and can be tuned efficiently using manual methods than the one given by DataStage.

You need to try different values for 'number of rows' to get the correct balance.

yaminids · Post by **yaminids** » Thu Jun 16, 2005 11:41 am

Hello Sai,

I am la ittle confused with your reply.

1) Do you mean to say that I can get better performance if I can use Ora BLK Stage to generate 'Control File' and 'Data File' and then load the data manually.

or

2) Try to use 'Sql Loader' without the intervention of DataStage

Can you elaborate your reply?

Thanks
Yamini

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jun 16, 2005 11:45 am

What I meant was to obtain the sql loader control and data created seperately (search forum for details) and then load them externel to your job.

You will also be able to tune and enhance performance by that way.

hkotze · Post by **hkotze** » Fri Jun 17, 2005 3:53 am

I second Sai,

We try to use Bulk loader and found that you can only insert into a blank table. Well we have tables that we want to append to and the control file only caters for Inserts and not all the other possibilities.

I wrote a after job routine to manipulate the generated control file and add Append in also to add the text to allow null columns to be loaded.

The Ora Bulk load stage need some enhancements.

Use the stage to create the control file and the data file or have the data file created in another job and then change the control file to read the correct data file. Your control file shouldn't change that often and if the recreate it and run sqlldr in an after job routine.

anupam · Post by **anupam** » Fri Jun 17, 2005 7:48 am

The fastest way of loading data is using sqlldr with direct hint. Now it all depends on the situation whether to use direct hint or not.

If you are creating data file and ctl file separetly then you need to trigger the sqlldr manually ( by wrinting code) so that point in time you can add different kind of hints like arraysize rowsize bindsize etc.

So you should explore all the options related with sqlldr.