Job with Procedure Hanging

jerome_rajan · Post by **jerome_rajan** » Wed Dec 07, 2016 3:14 am

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

atul9806 · Post by **atul9806** » Fri Dec 09, 2016 6:48 am

Can you try to use ODBC connector and check whether it is also stuck ?

jerome_rajan · Post by **jerome_rajan** » Wed Dec 14, 2016 12:23 pm

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

qt_ky · Post by **qt_ky** » Wed Dec 14, 2016 12:44 pm

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.

chulett · Post by **chulett** » Wed Dec 14, 2016 12:53 pm

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.

jerome_rajan · Post by **jerome_rajan** » Wed Dec 14, 2016 11:38 pm

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"

jerome_rajan · Post by **jerome_rajan** » Wed Dec 14, 2016 11:39 pm

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

chulett · Post by **chulett** » Thu Dec 15, 2016 7:53 am

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.

jerome_rajan · Post by **jerome_rajan** » Thu Dec 15, 2016 10:45 am

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

chulett · Post by **chulett** » Thu Dec 15, 2016 10:52 am

Do you have the Stored Procedure stage set to "Target"?

jerome_rajan · Post by **jerome_rajan** » Fri Dec 16, 2016 9:56 am

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

Code: Select all

SELECT :Dummy FROM DUAL

at the end of the procedure?
I'll test it out but would like to hear your thoughts

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Fri Dec 16, 2016 4:14 pm

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.

jerome_rajan · Post by **jerome_rajan** » Sun Dec 18, 2016 2:08 am

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.