Page 1 of 1
Need to Optimize data insert mechanism in data stage
Posted: Fri Nov 09, 2012 9:40 am
by 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.
Posted: Fri Nov 09, 2012 10:06 am
by 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.
Posted: Fri Nov 09, 2012 12:44 pm
by 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?
Re: Need to Optimize data insert mechanism in data stage
Posted: Fri Nov 09, 2012 2:01 pm
by bfennell
You may find this thread interesting:
viewtopic.php?p=147391
Posted: Fri Nov 09, 2012 2:04 pm
by jra2002
Posted: Fri Nov 09, 2012 2:23 pm
by chulett
Still would like to know if we're talking about a Server or a Parallel job.
Posted: Fri Nov 09, 2012 2:28 pm
by 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
Posted: Fri Nov 09, 2012 3:21 pm
by 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.
Posted: Fri Nov 09, 2012 3:49 pm
by jra2002
Yes index becomes UNUSABLE. Any help is highly appreciated!!!
Posted: Mon Nov 12, 2012 3:11 pm
by 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
Posted: Mon Nov 12, 2012 3:42 pm
by 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.
