Job hungs up when trying to update table

sarathchandrakt · Post by **sarathchandrakt** » Fri May 01, 2015 9:01 am

Hi,

I am copying,
ID(Primary KEY),
EMP_ID,
NAME,
ADDRESS
from Table EMP to a DATASET.

Then I am doing a lookup on DATASET from a different file with columns EMP_ID,NAME,ADDRESS. When ever the EMP_ID matches, I am sending ID,NAME,ADDRESS to an Oracle Connector and trying to update NAME and ADDRESS based on ID in EMP_ID table. But the table hungs up when ever I do this.

I tried running it Sequential and by Partioning data. It doesn't work either.

I copied this data to a temp table and tried to update EMP_ID by using MERGE query. It worked. I am curious why the UPDATE query is not working.

(My DBA says, the table is going into locked stage when ever I run the Job. And many sessions are open on that table. We tried deleting all sessions and blocks. Still the same. None else is accessing the table except me)

PaulVL · Post by **PaulVL** » Fri May 01, 2015 9:30 am

I would look to your DBA for more assistance.

Is the other table actually a view and the underlying table currently has a lock on it?

I basically sounds like a table lock issue and nothing on the datastage side.

taylor.hermann · Post by **taylor.hermann** » Fri May 01, 2015 10:46 am

We had a similar locking problem, because of how the partitioning worked with a job we had.
But we ran out of processes on the Oracle server, and when our DBA increased that much higher, everything worked fine.

AshishDevassy · Post by **AshishDevassy** » Fri May 01, 2015 1:32 pm

few things i would check are .
1. Are there duplicates coming from the source links. If so remove / manage them.
2. Work with DBA to identify the active query that is in execution when the job hangs.
3. Check the partitions of the job. i.e. test by running on a single node to see if that works.
4. My guess is since the table locked up , there might be multiple DML operations on that table at the same time.

sarathchandrakt · Post by **sarathchandrakt** » Fri May 01, 2015 1:43 pm

This is what I did,

Created Index on Column which I was using to update the table in where clause.

It fixed the issue.

Thanks for the suggestions though.

chulett · Post by **chulett** » Fri May 01, 2015 2:47 pm

So not hung up or 'locked' but rather just... slow. From the full table scan each update had to perform.