Sybase connection timeout settings

Date:Archived
Product/Release:Visual LANSA
Abstract:Applications may get database errors after being idle for a long time due to Sybase connection timeout settings in Client/Server environment
Submitted By:LANSA Technical Support

Applications get database errors after being idle for a long time.

Cause (Summary):

Adaptive Server Anywhere has a default timeout of 4 hours when using connection types other than Shared Memory. LANSA only configures an ASA connection like this when connecting to Server databases. Standalone databases just use Shared Memory and so they do not exhibit this behaviour.

Examples:

  1. Running Host Monitor on a Server in order to receive Repository Propagations.
  2. LANSA for Web returns an error the first time it is accessed in the morning.
  3. The LANSA Development Environment is left running overnight and the first time it is used in the morning a database error occurs.

Note: LANSA intends implementing a workaround to periodically tell the server that the connection is still alive so that Host Monitor and LANSA for Web do not do this.

Fix:

The best rule is that any jobs that you want to continually run should be run on the same machine as the database engine with only Shared Memory communications selected in the ODBC DSN. Leave the Client Idle Time at its default. 

Cause (Detailed):

This is caused by the "Client Idle Time". The default is that after 240 minutes of no activity from the Client, it is disconnected. The parameter for this is -ti minutes. This is IGNORED if the connection from the client to the database engine is ONLY Shared Memory. That is, deselect all other network protocols. Typically, a standalone database ODBC DSN is configured to use Shared Memory and a client database ODBC DSN is configured to use TCP/IP. Thus the overnight disconnection would only occur with the client database ODBC DSN. Shared Memory can be used by a client if the database server is on the same machine. Thus, if your application is running on the server machine, check the Shared Memory option and deselect all the others and your client will never be disconnected.

If your client is remote, you can adjust the Client Idle Time. This can only be set on the database engine command line so it relates to ALL connected clients. Its purpose is to be able to automatically remove dead connections and free outstanding locks. Setting the value to zero disables checking of inactive connections, so that no connections are disconnected. But, it is inadvisable to set it to zero as dead connections with active locks will not be disconnected automatically. They will have to be removed manually.

Essentially, the best rule is that any jobs that you want to continually run should be run on the same machine as the database engine with Shared Memory communications. Leave the Client Idle Time at its default, or maybe even reduce it, but never to 0. An example might be running Host Monitor on the Server machine that has been configured as the Repository Gateway for Repository Propagations. Thus the Repository is kept up to date without a particular Client PC needing to be switched on in order for Propagations to be received.