Parallel DataStage Jobs Hanging Intermittently - Oracle?

d-woo · Posted: Fri Jan 25, 2019 7:36 am

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 · Posted: Fri Jan 25, 2019 7:51 am

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

d-woo · Posted: Fri Jan 25, 2019 8:13 am

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 · Posted: Sat Jan 26, 2019 8:40 pm

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.

chulett · Posted: Sun Jan 27, 2019 4:00 am

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.

PaulVL · Posted: Mon Jan 28, 2019 5:10 am

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 · Posted: Mon Jan 28, 2019 6:40 am

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.

chulett · Posted: Mon Jan 28, 2019 10:55 am

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.

rkashyap · Posted: Tue Jan 29, 2019 4:22 am

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 · Posted: Fri Feb 01, 2019 2:35 am

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 · Posted: Fri Feb 01, 2019 6:30 am

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.

chulett · Posted: Sat Feb 02, 2019 3:42 am

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.