Timeout parameter

VasanthRM · Post by **VasanthRM** » Tue Aug 01, 2006 7:20 am

We are facing an issue in production, where an oracle query runs for a long time (about 1.5 hrs).

Certain it happens where the status of the job is running, but the query would have run and got over. But still the job wouldn't have got the data. This seems as if the connection is lost after posting the query.

When we try to rerun this job the query fetches the data in the 2nd or 3rd run

Could anyone please let me know is there any timeout parameter that can be increased which will avoid these types of stalled connections?

Any clue in this will be of a great help

DSguru2B · Post by **DSguru2B** » Tue Aug 01, 2006 1:37 pm

What else is the job doing? Open the designer and try to view data , see how much time does it take to retrieve data. If its not taking that much time then the other transformations must be taking time.
Do you have any after job subroutines? If you do then once the job executes, the status still remains as 'running' as the after job subroutine is being executed.
If none of the above applies to you then clear the status file, the log files. Also check the size of &PH& folder. If its really huge try to clean files older than 7 days.

VasanthRM · Post by **VasanthRM** » Wed Aug 02, 2006 4:22 am

The transformations are not taking time. It is the query that runs against out database that takes a lot of time. The query is highly tuned, so nothing can be done to reduce the runtime for the query.

There are no subroutines. This is single straight job fetching data from our Oracle DB and moves them into a hash file.

The &PH& folder is not huge and we clear the log file every week

The problem is that the connection expires/timeouts by the time the query returns the data. Is there any way to increase the timeout, so that the connection doesn't expire that fast.

ArndW · Post by **ArndW** » Wed Aug 02, 2006 4:43 am

What Oracle connection method are you using? Is it ODBC or an Oracle stage? I think the timeout might occur when going through ODBC and not when accessing using the builtin stages. I know I've had extremely long query times before without any timeouts, so this isn't an insurmountable problem. Perhaps you could ask your DBA if he/she has set a limiting value in Oracle as well.

VasanthRM · Post by **VasanthRM** » Wed Aug 02, 2006 11:09 pm

We are using Oracle 8i stage. We are also looking for all other possibilities. All that I want to know is there any parameter available in Datastage which timeouts the session opened with datastage. If so what is the parameter. Will I be able to tune it based on requirements?

What is the purpose of inactivity timeout parameter that is available in datastage administrator? Does it enforce only on the timeouts only between datastage client and datastage server. Does this parameter olds good for any session opened with datastage

kumar_s · Post by **kumar_s** » Thu Aug 03, 2006 1:54 am

What is the purpose of inactivity timeout parameter that is available in datastage administrator? Does it enforce only on the timeouts only between datastage client and datastage server.

Yes.

chulett · Post by **chulett** » Thu Aug 03, 2006 6:49 am

Unfortunately incorrect. Actually it does. From the docs:

Administrator Guide wrote:The timeout also affects the dsjob -wait command issued from the command line and the job control api function DSWaitForJob. If you are using these features you are advised to set a long timeout, or disable the timeout altogether. It also affects Shared Containers on the Parallel canvas (which use DSWaitForJob).