Job with Procedure Hanging
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
Job with Procedure Hanging
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
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
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Can you try to use ODBC connector and check whether it is also stuck ?
~Atul Singh
<a href=http://www.datagenx.net>DataGenX</a> | <a href=https://www.linkedin.com/in/atulsinghds>LinkedIn</a>
<a href=http://www.datagenx.net>DataGenX</a> | <a href=https://www.linkedin.com/in/atulsinghds>LinkedIn</a>
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
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
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
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.
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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
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"qt_ky wrote:...Seems like in either case though, you should get a timeout error that the job would detect and abort on.
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
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.1chulett 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 ...
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
Thanks for opening up the post. My job design is very simplechulett 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.
Code: Select all
RowGen --> StoredProc (where I call the procedure)
Code: Select all
RowGen --> Oracle Connector (Using write mode as PL/SQL) (Also tried the PL/SQL in the AfterSQL)
All the other settings were the default ones. If there's any setting whose value you are looking for, I'll dig that up
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
Hi Craig,
We have it set to "Target".
Meanwhile, I contacted the DBA and this was his response
at the end of the procedure?
I'll test it out but would like to hear your thoughts
We have it set to "Target".
Meanwhile, I contacted the DBA and this was his response
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 aSessions 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.
Code: Select all
SELECT :Dummy FROM DUAL
I'll test it out but would like to hear your thoughts
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
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.
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.
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
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.
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.