Need to Optimize data insert mechanism in data stage

[jra2002]

It is taking a long time to insert data via datastage in a target database. The target database is Oracle 11g.

I would like to attached the existing properties of Oracle Connector. I am not sure how to upload attachments to a forum.

[chulett]

You can't upload anything here. You can, however, have your image(s) hosted at any of the free image hosting websites out there and then link to them here using the "Img" tag.

Please confirm for us you are indeed talking about a Server job and let us know your exact 8.x version.

[jra2002]

Version Number 8.5

I have never use free image hosting websites. Would you please let me know the details which on shall I use?

[bfennell]

You may find this thread interesting:

viewtopic.php?p=147391

[jra2002]

I am able to upload images

[chulett]

Still would like to know if we're talking about a Server or a Parallel job.

[jra2002]

This is on a server.

The basic problem is when major insert is happening on a table at the same time select statement is executed on the same table. We endup in loosing index on table (index become unused).

That's why we need to understand is there any way we can boostup a performance of insert

[chulett]

Are you certain as to the cause and did you mean "index becomes UNUSABLE"? More typical during a bulk load would be for a unique index to become unusable due to loading duplicates to it.

[jra2002]

Yes index becomes UNUSABLE. Any help is highly appreciated!!!

[jra2002]

This issue is still open. Any timely help is highly appreciated.
Please correct information in Oracle connector properties. Rebuild index is set to NO.

Thanks

[chulett]

You need to have a chat with your DBA about Oracle, bulk loads and indexes. That should help you understand what the various options mean and which you should use. If you don't rebuild the indexes, they can be left in an UNUSABLE state. And as I've noted, even if you do attempt a rebuild, a unique index that you've loaded duplicates to cannot be rebuilt and thus stays UNUSABLE as well.

I just want to make sure you understand that this isn't a "DataStage issue", it's an Oracle sqlldr issue and the best resource in your company if you are unfamiliar with using sqlldr should be your DBA. Hopefully.