Parallel DataStage Jobs Hanging Intermittently - Oracle?

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
d-woo
Participant
Posts: 5
Joined: Wed Feb 18, 2015 9:46 am
Location: United States

Parallel DataStage Jobs Hanging Intermittently - Oracle?

Post by d-woo »

We're having intermittent job hanging running 11.5.0.2, updating tables against Oracle 11g targets on a client database. We run hundreds of jobs per day that perform various table updates. Each day, we can have between zero and 4 jobs hang at various times. The only common theme appears to be that we are attempting to connect in parallel to the target table, and in the log in Director, we only see one connection success. Then the job hangs until we kill it.

It is rarely the same job and is not specific to a time or node we're on. In every case, we're able to kill and then successfully rerun the job. We have attempted multiple straces and suggestions from Fix Central, but haven't really made any progress. The latest evidence notes that this may be an issue when attempting the parallel connect as we do not see anything further than the process executing OCIServerAttach.

We continue to debug, just hoping someone has seen this behavior before. We have other jobs running in the same environment that do not establish an Oracle connection to a target, all of those jobs run without issue. Thanks.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Can you confirm for us what target stage you are using, please?
-craig

"You can never have too many knives" -- Logan Nine Fingers
d-woo
Participant
Posts: 5
Joined: Wed Feb 18, 2015 9:46 am
Location: United States

Post by d-woo »

My fault. We are using an Oracle connector stage.

Here's an example of what we see in the logs:

Orc_insert_update_table: The connector connected to Oracle server TARGET1A.
Orc_insert_update_table: Connector code revision: 263892_0223017
Orc_insert_update_table: The connector will run in parallel on 2 processing nodes.

But then later, we only see this one entry, whereas for the other tables there are both a 0 and 1 instance:

Orc_insert_update_table,0: The connector connected to Oracle server TARGET1A.

When we rerun, we'll see both entries and the job is successful:

Orc_insert_update_table,0: The connector connected to Oracle server TARGET1A.
Orc_insert_update_table,1: The connector connected to Oracle server TARGET1A.


This is not specific to an insert/update, we see this with deletes as well. It appears we are unable to establish a connection although the original "non-parallel" log states we have connected, but the second node does not appear to connect and just hangs forever.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Check with your Oracle DBA how many listeners are available. Work out how many the totality of DataStage jobs are trying to connect to Oracle at the same time.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Yeah, wondering as well if your DBA has been involved yet. Several limits could in play per user, per session, etc... which I imagine would show up in the alert log. Or could be monitored.
-craig

"You can never have too many knives" -- Logan Nine Fingers
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

Is your environment set in a grid where the nodes get distributed to different hosts? Or do you have a consistent hard coded APT file?
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

I'll describe a similar issue we had recently with the same symptoms. The DB is MSSQL Server, and I don't know if Oracle should handle this differently.

We transmit up to 1,000 rows per day for an "upsert", except that the command line has separate queries for update and insert. Running on two nodes meant that an unintended pair of key column duplicates were on separate nodes, and when the rows arrived they created a contention that caused the DB session to hang. We resolved it by reducing to one node in default.apt, with the rows coming in sequentially and being processed separately.

Our key column duplication was caused by truncation. The lesson for us was to check for duplication in the data, and bring out the nerf bats to use on the project team that failed to fix the code to avoid the truncation.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson

Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

From what I've seen, Oracle would handle it much the same way. Should show up as a "deadlock" in the alert logs, I believe.
-craig

"You can never have too many knives" -- Logan Nine Fingers
rkashyap
Premium Member
Premium Member
Posts: 532
Joined: Fri Dec 02, 2011 12:02 pm
Location: Richmond VA

Post by rkashyap »

We had faced similar situation and our support provider suspected it to be a symptom of oracle bug 10178982, which was related to partition maintenance.
Similar to Franklin's suggestion, workaround was to run Oracle connector in sequential mode.
d-woo
Participant
Posts: 5
Joined: Wed Feb 18, 2015 9:46 am
Location: United States

Post by d-woo »

Thank you all. We're looking in to the user/sessions/connections limitations with our Oracle connectivity now. One would think we'd have a job failure, rather than a hang though. We're also looking into the deadlock possibility, but again I'd think that we'd receive an error there and a failure. None of these jobs fail, despite leaving them running for hours.

We have turned on debug logging for several jobs on the Oracle connectors in the hopes we'll see a hang on one of them. We've had two clean days now, without making any changes.

Does anyone know if there's a way to set the Oracle connectors default to run sequentially at a project level? We have hundreds of jobs set up and do not want to modify each if we can avoid it.

Also - we are running grid with work being distributed to different hosts, and the issue isn't specific to one node. It's completely random - different times, different jobs, different days. There's no consistency in the hangs. Extremely frustrating at this point.

Thank you all for your suggestions thus far!
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

d-woo,

I understand your frustration very well. We were lucky in that the app is very small and has a maximum data volume of about 5,000 records. We don't need more than one node.

There should be an environment variable which you can use in each job that needs to run on only one node. I don't know how to find it, hopefully someone else reading your thread will know.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson

Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

You are right in that Oracle deadlocks will generate specific errors / failures when detected. However, since Oracle has been known to have the occasional bug or forty at any given time for any given version, there could be issues with communicating that back to the connector via the client or even perhaps with the connector detecting that. But it would definitely be in the alert logs which hopefully your DBA is monitoring for you when this happens.

Involve support if you haven't already.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply