Open Command Causing Job to Get Hung - Oracle Enterprise

[tyler.mehr_PHX]

Hi all, I'm trying to execute a command to refresh a materialized view from an Oracle Enterprise stage using the Open Command. It seems to be that if the command takes over an hour to complete, the DataStage job will hang indefinitely even though we have had our DBA team confirm that the process has finished on the database. If the command takes under an hour (it varies), the DataStage job will complete without issue.

We have the server level Inactivity Timeout set to 43,200 seconds so that does not seem to be the issue. Why would the connection between DataStage and the Oracle database be timing out after an hour? Is this a setting somewhere in DataStage?

Thanks.

[chulett]

Firewall timeout? Oracle idle timeout?

[tyler.mehr_PHX]

I think the Oracle idle timeout is a good starting point for me to check with the DBA's. I'll do that. I was under the impression that a timeout would cause the DataStage job to abort. Is this not always the case? Is DataStage known for leaving jobs running or does that usually not indicate a timeout?

[chulett]

Not always the case, no... seen circumstances where the ETL tool is waiting for a reply that never comes. Seemed like a good place to start at least.

[tyler.mehr_PHX]

Thanks chulett. The DBA team has confirmed there is no timeout. We opened a SQL* Plus session to run the materialized view refresh command from the DataStage server to try to narrow down the root cause of DataStage or the Oracle database. It hung up as well so the DBA is looking back into other settings on the database.

[tyler.mehr_PHX]

The issue has been resolved. The sqlnet.expire_time on the Oracle database was set to 30. The DBA team changed to 10. This was causing issues since the firewall timeout was at a lower interval (I think..).

Anyways, thanks chulett, you were right on both suggestions!

[chulett]

Been there, done that, got the t-shirt. Both t-shirts, actually.