CONNECT_TIMEOUT session environment option

Use the CONNECT_TIMEOUT session environment option of the SET ENVIRONMENT statement to set an upper limit on the number of seconds while the current session attempts to establish a connection to another database server, before an error is returned.

The CONNECT_TIMEOUT session environment option has this syntax:

CONNECT_TIMEOUT environment option

1  SET ENVIRONMENT CONNECT_TIMEOUT
2.1! '1'
2.1? 'integer'
Element Description Restrictions Syntax
integer An unsigned integer value > 0 sets the maximum number of seconds to attempts to establish a connection to a database server Must be delimited between single (') or double (") quotation marks. A setting of '0' defaults to the setting of the CONNECT_TIMEOUT configuration parameter. Literal Number as Quoted String

Usage

Use the CONNECT_TIMEOUT session environment option of the SET ENVIRONMENT statement to limit the number of seconds to spend attempting to establish a connection to a database server in the current session, before the connection effort times out with an error.
'integer' > 0
This value sets an upper limit on the time spent attempting to establish a connection, including (if the initial attempt fails) at least one additional attempt.

After the time limit that CONNECT_TIMEOUT specifies has been exceeded, or if no connection with another database server has been established after (integer_R + 1) attempts, for integer_R the CONNECT_RETRIES setting, the CONNECT statement or the implicit connection fails, and the database server returns an error. By setting the CONNECT_TIMEOUT and CONNECT_RETRIES session environment variables to configure your sever-to-server connection capability in the current session, you can minimize connection errors. To estimate the optimal value for CONNECT_TIMEOUT, take into account the total distance between nodes, the hardware speed, the volume of traffic, and the concurrency level of the network.

Order of precedence for time limits on connections

This is the ascending order of precedence (lowest to highest) among the methods for setting an upper limit on the amount of time that a CONNECT statement can spend attempting to connect to a database server instance:

  • System default value of 60 seconds, if none of the methods below are set.
  • CONNECT_TIMEOUT configuration parameter
  • CONNECT_TIMEOUT client environment variable
  • SET ENVIRONMENT CONNECT_TIMEOUT statement of SQL.

The CONNECT_TIMEOUT session environment option can override the setting of the client CONNECT_TIMEOUT environment variable, or of the CONNECT_TIMEOUT configuration parameter, or of the system default value, if any of these has established a time limit different from the SET ENVIRONMENT CONNECT_TIMEOUT value.

Setting session environment options for connections

The value of the CONNECT_TIMEOUT session environment option is divided by the value of the CONNECT_RETRIES session environment option to determine the maximum number of seconds between successive connection attempts, if the previous attempt of the same CONNECT statement failed to establish a connection.

For example, the following statements set CONNECT_TIMEOUT to 60 seconds and CONNECT_RETRIES to one retry:
SET ENVIRONMENT CONNECT_TIMEOUT '60';
SET ENVIRONMENT CONNECT_RETRIES '1';
In this example, the CONNECT statement attempts to establish a connection for 60 seconds. An initial attempt is made to connect to the database server at 0 seconds. If the CONNECT_TIMEOUT session environment option is set to the default value of '0', an additional attempt to connect is made within 60 seconds, if necessary, before connection failure error -908 is returned.
Similarly, you can configure these session environment options for multiple retries. With the same CONNECT_TIMEOUT setting, the following statement specifies three additional retries:
SET ENVIRONMENT CONNECT_RETRIES '3';

If CONNECT_RETRIES is set to '3', up to three additional attempts to connect to the database server are made (at 20, 40, and 60 seconds, if necessary), before an error is returned.

This 20-second interval is the result of dividing the CONNECT_TIMEOUT value by the CONNECT_RETRIES value.

If you set the CONNECT_TIMEOUT session environment option to '0', as in this example,
SET ENVIRONMENT CONNECT_TIMEOUT '0';
the database server automatically uses the setting of the CONNECT_TIMEOUT configuration parameter. If the CONNECT_TIMEOUT parameter is not set, its default value of 60 seconds is used during subsequent CONNECT statements in the session.

Implicit connections with database statements

If the CONNECT statement does not begin your application, its first SQL statement must either be one of the following database statements, or else be a single-statement prepared object for one of the same statements:
  • DATABASE
  • CREATE DATABASE
  • DROP DATABASE
If one of these statements, rather than the CONNECT statement, is the first SQL statement in an application, that database statement can establishes a connection to a database server that is known as an implicit connection, because no CONNECT statement in the session has established an explicit connection. To establishes the implicit connection, a database statement must specify one of the following, either as an SQL identifier or as the content of a variable:
  • a database server and a database,
  • or a database server only,
  • or a database only.
If the database statement specifies only a database, the database server obtains a database server name from the DBPATH environment variable setting. For more information on establishing implicit connections in UNIX or Windows environments, see the topic Specifying the Database Environment.
If you issue the CONNECT statement after the application establishes an implicit connection with only a database name, the CONNECT statement must search DBPATH to identify the database server for that database. For this search, the CONNECT_RETRIES session environment option specifies the number of additional connection attempts that can be made for each database server entry in DBPATH.
  • All appropriate servers in the DBPATH setting are accessed at least once, even if the CONNECT_TIMEOUT value is exceeded. In this context, the CONNECT statement might take longer than the CONNECT_TIMEOUT time limit to return an error that indicates a connection failure, or indicating that the database was not found.
  • The CONNECT_TIMEOUT value is divided among the number of database server entries that are specified in DBPATH. Thus, if DBPATH contains numerous servers, you can use the SET ENVIRONMENT CONNECT_TIMEOUT statement to increase the CONNECT_TIMEOUT value accordingly. For example, if DBPATH contains three entries, in order to spend at least 30 seconds attempting each connection, set CONNECT_TIMEOUT to '90'.
    SET ENVIRONMENT CONNECT_TIMEOUT '90';