Six Stars

Oracle - Bulk loading?

I am getting a little over 3K rows per second using tFileInputDelimited > tOracleOutput.

 

I have been looking at tOracleOutputBulk and tOracleOutputBulkExec, is there a way to improve the speed without having to install TOS on the Oracle database server?

Tags (3)
7 REPLIES
Six Stars

Re: Oracle - Bulk loading?

Not sure about the bulk components, but in the tOracleOutput component, try to play around with the following settings in 'Advanced Settings':

 

  • Commit every x records
  • Use batch size

 

Setting the 'Use batch size' setting has proven to help us in the past.

Six Stars

Re: Oracle - Bulk loading?

I did see an improvement already when I did "Use Batch Size".

Right now I am committing every 150K and a batch size of 75K.
Eleven Stars TRF
Eleven Stars

Re: Oracle - Bulk loading?

If you want to try the bulk, I think you just need to install sqlldr on the same machine where the job runs (or the Studio when running from there).

TRF
Six Stars

Re: Oracle - Bulk loading?

I do have sqlldr installed on the same server the TOS job is located.
The Oracle database is on a completely different server.

When I make the job:
tFileInputDelimited > tMap > tOracleOutputBulkExec

The rows go across and look like they processed, but then the job dies with error "java.lang.NullPointerException".
Eleven Stars TRF
Eleven Stars

Re: Oracle - Bulk loading?

There is a lot of reason for this kind of error.

I understand the job doesn't crash when not using bulk operations, but it is not easy to tell the reason of the error with idea of what the job looks like.

Can you share the design?

Also, tell us when the error occurs, immediatly when coming into the bulk component or after a delay?

Share also the complete error message.


TRF
Six Stars

Re: Oracle - Bulk loading?

The error is happening on the tOracleOutputBulkExec component:

 

image.png

image.png

 

image.png

Moderator

Re: Oracle - Bulk loading?

Hello,

The tOracleOutputBulk and tOracleBulkExec components are used together in a two step process. In the first step, an output file is generated. In the second step, this file is used in the INSERT operation used to feed a database. These two steps are fused together in the tOracleOutputBulkExec component, detailed in a separate section. The advantage of using two separate steps is that the data can be transformed before it is loaded in the database.

Please have a look at this scenario:TalendHelpCenterSmiley Frustratedcenario: Inserting transformed data in MySQL database

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.