Oracle Deadlocks

[prasannak]

Issue:-

Oracle deadlock issue problem while inserting data into ORACLE DB.

sqlcode is: -60
esql complaint: ORA-00060: deadlock detected while waiting for resource

Issue in detail:-

I am using the parallel ORACLE enterprise stage in my load job to load the transformed data.

During load I am facing deadlock held on the table and the job is getting aborted.

I am using user defined Insert(only Insert) statement on ORACLE stage to load the data and the job is running on 2 nodes.
A strange issue is that, I am facing this above deadlock during my 1st run of job, however if I run the job 2nd time not facing this problem.

Let me know if you require any more information.

Any thoughts on this would be highly appreicated.

Name & Version of Software: DataStage Enterprise Edition, 7.5.1.35
OS & Version: Generic_117350-08, SunOS 5.8
Oracle DB: Oracle 10G

[gabrielac]

A couple of thoughts that might help:

- Have you tried using the Load option instead of insert?
- Are there other Oracle stages reading from the same table in the same job, such as a sparse lookup?
- Have you changed the upsert time for commit / rows for commit environment variables?

Gaby

[prasannak]

Hi Gaby,

Apparently, load option using parallel stage has some bugs in datastage that has prevented us from using it...
We have given this information to IBM and it is being ackonwledged by them it is indeed a bug and last heard from them was that they are working on it...

No other oracle stage is reading from this table...This table is only being referenced at the end during insert operation...

I think we have not changed the upsert time for commit/rows for commit en variables...It is just default...
Can you please elaborate on this point?

[gabrielac]

Add the following environment variables as job parameters:

APT_ORAUPSERT_COMMIT_TIME_INTERVAL
APT_ORAUPSERT_COMMIT_ROW_INTERVAL

time = 60
and rows = 2000

worked for me

HTH, Gaby