DSXchange

Hi All,

Did any one faced this issue when updating data in oracle 9 table using oracle enterprise stage?

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

Sure, plenty. Have you tried searching the forums for that error?

Do you have any bitmap indexes on this table.If you have any please disable them before updating and also do proper partitining make sure all records with same key will be in the same partition.

Please check if you are using same table for fetching the records and same table to update or insert the records in same job.This situation also can create deadlock.

I finally have to choice server job to update a table.

- checked with increasing INITRANS on table
- And not doing any other tasks other the updating.

Satish,

You can use even parallel job but you have to verify two things

1. Check whether you rae using proper key columns in job based on the table definition

2. Put the execution mode of the job in sequential.

Regards,
Sateesh.M

Is this sateeshbabu mandepudi?

Well i changed to sequential mode. just to sack for work and it works.

But the real question remains is why parallel updatation doesn't work?.
is this something underlying database doesn't support it?

I have four nods and everything DBA comes and says datastage tried to update the table four times and got locked itself!

Possibly the same key arriving to Oracle from more than one partition?

I've had the same issue lately and DBA's are stumped. INITRANS are increased on the table and the index. With commit set at 5000 and 4 nodes, always have the issue. I've set commits to 4, then to 10 and it keeps running ok. It seems to be a matter of keeping sessions on the nodes out of each other's way by making them commit more often. I can't say this will work every time in every environment (dev, qa, prod). But, at least I'm running more nodes than 1 and performance is better than on 1 or server, albeit not great performance.

Hi,

Iam getting a warning in Lookup stage like,

"Lookup: Ignoring duplicate entry at table record 9; no further warnings will be issued for this table"


Iam trying to get n links from same copy stage and hence ends up with this warning.
Any solution for removing this warnings.


Thanks.

Come on, what the heck does this have to do with the current topic? Hint: nothing. Please start your own post on this subject.

If running in parallel, at the Oracle stage, click the INPUT tab, then choose partition. Select HASH and select the same columns that comprise the keys for the table you are loading. This will ensure that all the duplicate keys go down the same "path".