Getting Error when loading data in Oracle Database in Upsert

[nirdesh2]

Hi,

We have Load Ready Dataset that is output of SCD type2 stage and we are laoding that setset into Databse table in another job using Oracle enterprise stage in upsert mode. Sometime data is loaded sucessfully and sometime loading gets failed by giving error "Unable to insert record into the table" and when we try again it is loaded sucessfully. It gives the random behaviour. Sometime failed and sometime successfull.
Please help..

[Pagadrai]

Hi,
Please give more details.
Can you try using a reject link to the Oracle stage to obtain the sqlerror. This will provide exact reason why the record is not interted.

also mention the exact warning message that you are seeing in the log.

[nirdesh2]

I am using Reject Link to capture any reject data that could not be laoded into the database.
I am getting the error message "Oracle_Enterprse : Unable to insert a record into the table." "Orcle_Enterprise : The runLocally() of the operator Failed."

We are running this job at 2 node and row commit frequency is 5000.

[chulett]

And if you run it on one node, does it work consistently?

[nirdesh2]

Today I run the loading job in Sequential mode in stead of parellel mode and data was loaded sucessfully without any error. Is there any problem with parellel mode when using upsert mode?

[mahadev.v]

Could it be a dead lock on the record in parallel mode? Try hash partitioning the data on the keys and run it in parallel.