Oracle Connector with Insert Else Update

anusha · Post by **anusha** » Mon May 09, 2011 6:15 am

Hi,
Am using Oracle connector stage with load method as 'Insert Else Update'.
but the amount of data for Update will be much bigger and higher than Inserts. Because of my 'Insert Else Update' load method, job takes atleast 3 hours to update 0.3 million records.

If I use 'update else Insert', job pops out 'Unique key violation error' and if I turn it ot 'Insert Else Update', it works fine but very degraded performance.

Please share your thoughts to overcome this performance issue or suggest me any alterantives to manage this.

Thanks in advance.

anusha · Post by **anusha** » Mon May 09, 2011 6:31 am

I can't use Oracle enterprise stage here because it's not supporting reject link from target database when job runs with RCP.

Aquilis · Post by **Aquilis** » Mon May 09, 2011 7:42 am

Hi,
Check your Array size and commit interval on oracle connector stage.

anusha · Post by **anusha** » Mon May 09, 2011 11:06 am

Default we kept it as 2000 and tried increasing upto 10,000. Nothing hasn't seem fruitful.

Does it seem fine, if we double Nodes in configuration file. Currently we have 4-Node configuration and am thinking of recreating with 8-Node configuration?

I know, it's more to do with Database transactions but a Thought went through mind.

Thanks in advance.

prakashdasika · Post by **prakashdasika** » Wed May 11, 2011 9:42 pm

Because the number of records written are high i would use two different stages to do insert and update. Instead of insert you can aslo do bulk load which will save some time. Also how are you dealing with indexes on the table?

greggknight · Post by **greggknight** » Wed May 11, 2011 10:16 pm

Currently we have 4-Node configuration and am thinking of recreating with 8-Node configuration?

So what gains do you think you will get by doing this.
do you have 8 controllers?
do you have at least 4 cores.

Increasing your config will increase the amount of sectionleaders (osh) processes to eight and the number of player (osh) processes will increase by 4x as well. so if your job under a single node has 1 section leader and 6 play
ers it will now have 8 section leaders and 48 players (osh) processes. Datastage is process intensive. As you add nodes your cpu consumption and memory usage will increase as well.
Make sure you got the resources.

Bear in mind any other jobs which will be running at the same time.

Happy Trails