Page 1 of 1
Job with Procedure Hanging
Posted: Wed Dec 07, 2016 3:14 am
by jerome_rajan
Hi,
I have a job that simply invokes an Oracle Procedure. The procedure takes about 1.5 hours to complete for approx 50 million records. The issue is that the job goes into an indefinite "Running" state. We waited for more than 7 hours only to later realize that the procedure had actually completed execution in the Database. But for some reason, DataStage thought that the procedure was still running.
The procedure also completed successfully in 1.5 hours when executed via Toad.
Has anyone experienced this issue? What's the solution?
Thank you
Posted: Fri Dec 09, 2016 6:48 am
by atul9806
Can you try to use ODBC connector and check whether it is also stuck ?
Posted: Wed Dec 14, 2016 12:23 pm
by jerome_rajan
How do you think will that help? I tried to wrap it in a UNIX script and call the UNIX script from DS but still faced the same issue
Posted: Wed Dec 14, 2016 12:44 pm
by qt_ky
I wonder if it could be related to an Oracle client session timeout setting or default value that may be less than 1.5 hours. Might be worth looking into with your DBA.
On a related note, I have seen situations where the in-between firewall timeout settings need to be increased. Seems like in either case though, you should get a timeout error that the job would detect and abort on.
Posted: Wed Dec 14, 2016 12:53 pm
by chulett
Help us out here, I'm curious about a couple of things. One is when you wrapped it in a script to call from DS, if you run the script from outside of DataStage does it work? Hopefully the script makes no assumptions about the environment or the CWD or any such thing.
Secondly, could you elaborate a bit on your job design? I'm assuming you are using the Stored Procedure stage for this, if so could you detail the design and the settings you are using in the stage? Hoping it might help.
Posted: Wed Dec 14, 2016 11:38 pm
by jerome_rajan
qt_ky wrote:...Seems like in either case though, you should get a timeout error that the job would detect and abort on.
Exactly my thought. I've been in situations where the session would timeout and it would get logged. Here, it just hangs. The DBA checked and told us that inactive sessions longer than 40 mins would get dropped. But I'm not sure why this session would be "inactive"
Posted: Wed Dec 14, 2016 11:39 pm
by jerome_rajan
chulett wrote:Help us out here, I'm curious about a couple of things. One is when you wrapped it in a script to call from DS, if you run the script from outside of DataStage does it work? Hopefully the scrip ...
Let me try running the script from outside of DataStage. One more thing that I should probably add is that this issue has started showing up after upgrading to a super cluster database and DS 9.1
Posted: Thu Dec 15, 2016 7:53 am
by chulett
Since it would seem you are no longer as Premium as you used to be, I've opened up my previous message so you can see the second question I asked.
Posted: Thu Dec 15, 2016 10:45 am
by jerome_rajan
chulett wrote:....
Secondly, could you elaborate a bit on your job design? I'm assuming you are using the Stored Procedure stage for this, if so could you detail the design and the settings you are using in the stage? Hoping it might help.
Thanks for opening up the post. My job design is very simple
Code: Select all
RowGen --> StoredProc (where I call the procedure)
I even tried
Code: Select all
RowGen --> Oracle Connector (Using write mode as PL/SQL) (Also tried the PL/SQL in the AfterSQL)
Also tried wrapping the procedure in a UNIX script and calling the UNIX scipt as part of my master sequencer using ExecuteCommand.
All the other settings were the default ones. If there's any setting whose value you are looking for, I'll dig that up
Posted: Thu Dec 15, 2016 10:52 am
by chulett
Do you have the Stored Procedure stage set to "Target"?
Posted: Fri Dec 16, 2016 9:56 am
by jerome_rajan
Hi Craig,
We have it set to "Target".
Meanwhile, I contacted the DBA and this was his response
Sessions becomes INACTIVE when it does not make a SQL call to database; meaning it becomes INACTIVE only if the session is not doing anything in the database. In case if the session stays INACTIVE for more than 45 mins, server process kills the INACTIVE sessions to release the resources held by the process as it is NOT doing anything in the database. This is a standard across ASC3 and ASC4 super clusters.
I'm assuming DataStage issues the procedure call and simply waits doing nothing "Active" and that's what's causing these drops. Is there a way I can pass a value from the RowGen to the procedure and make DataStage do something active till the core function of the procedure is complete? Perhaps, like a
at the end of the procedure?
I'll test it out but would like to hear your thoughts
Posted: Fri Dec 16, 2016 4:14 pm
by asorrell
So I assume there is a very long pause before any data is sent back to DataStage? There's not a direct way to have "traffic" sent. You might be able to write a "wrapper" procedure that kicks off the big procedure and then keeps sending "Not ready - keep waiting" kind of messages back to DataStage, but I've never done that.
Short answer - your DBA needs to increase the inactivity timer to cover the long inactivity time while DataStage waits for the database to respond. I'd also say that you need to have your DBA take a look at the SQL to see if the performance can be improved. Also make sure your DataStage inactivity timer is set high enough as well.
Posted: Sun Dec 18, 2016 2:08 am
by jerome_rajan
Thank you, Andy. Will have to think about how to implement that wrapper procedure considering it'll involve intermittently spawning a process while the actual code is still running. Thanks for your suggestions.