Update query hanging while running in parallel

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Dec 16, 2009 8:55 am

First you need to deduce the reason for this issue before knowing how to avoid it.

Your DBA will be able to trace the session with SQLs and values.

Try running the same in single node - with no change in data.

harshada · Post by **harshada** » Thu Dec 17, 2009 3:59 am

I am not a premier member

...So can't read all of your comment..But I tried running it with single node by changing the .apt file..and it worked...
I want to understand how only that job will be configured to run on single node..cause when I change the .apt file it is appliclable for the whole project..Also if the job needs to be run on multiple nodes, if there any ways to avoid deadlocks ...

priyadarshikunal · Post by **priyadarshikunal** » Thu Dec 17, 2009 4:56 am

before you turn towards using it with single node try:

1. Check if the input has duplicates on the keys defined in where clause. ( a remove duplicate stage should give you the stats to find if there are duplicates)
2. Check trace file to see if its row level lock or table level lock.
3. Hash partition before the oracle stage on the keys defined in where clause or just one column (most dominant).

If all above mentioned point fails the workaround will be:
1. call $APT_CONFIG_FILE variable to the job and change the file name to one node config file.
or
2. Go to oracle stage properties then Stage -> Advanced and click on the selection button of node map constraint then select a node to run that stage on one node.

But still I think you should try to fix the problem instead of workaround.

raj_prodigy · Post by **raj_prodigy** » Thu Dec 17, 2009 6:08 am

Hi Priyadarshi Kunal,

The work around is fine if the job runs in single node. What if the user needs to run the job in multiple nodes?

Please clarify my doubt.

Thanks,
Bhasan.

harshada · Post by **harshada** » Thu Dec 17, 2009 8:39 am

Hi Priyadarshi

Thanks for your suggestions.
In the advanced tab I can't see any option to define node. All I can see is buffering options. I am using DS 7.5.1 enterprise edition.

priyadarshikunal · Post by **priyadarshikunal** » Thu Dec 17, 2009 9:05 am

harshada wrote: In the advanced tab I can't see any option to define node. All I can see is buffering options.

select stage from the main tabs. not the input tab.

raj_prodigy wrote: The work around is fine if the job runs in single node. What if the user needs to run the job in multiple nodes?

The problem is "the user cannot run it on multiple node because its causing locks". so thats the workaround.

still I think the constraints in where clause are not enough to distinctly identify a records and lock that only. OR There are duplicates in data and hence both input records are locking the same record or set of records.

So my suggestion is to find the problem and remove it instead of taking the other route to escape.

workaround should always be the last escape.

harshada · Post by **harshada** » Fri Dec 18, 2009 5:00 am

I have checked the key value and it is unique.
So I tried this :-
Go to oracle stage properties then Stage -> Advanced and click on the selection button of node map constraint then select a node to run that stage on one node
as suggested by priyadarshi and it worked.Job completed successfully

.
Thanks guys for all your help and suggestions.