CONNECT_RETRIES session environment option

Use the CONNECT_RETRIES session environment option of the SET ENVIRONMENT statement to specify the maximum number of additional connection attempts that can be made to each database server during a single CONNECT statement in the current session, after the initial connection attempt fails. All these attempts must be made within the time limit that the CONNECT_TIMEOUT setting specifies.

The CONNECT_RETRIES session environment option has this syntax:

CONNECT_RETRIES environment option


Element	Description	Restrictions	Syntax
integer	The maximum number of retry attempts during a CONNECT statement	Must be greater than zero. If you specify no value, the default behavior is a single retry.	Literal Number as Quoted String

Usage

Use the CONNECT_RETRIES session environment option to specify the maximum number of additional connection attempts that each CONNECT statement can make for a server-to-server connection during the session, if the first attempt of fails to establish a connection to the target database server instance. You can specify a nonzero integer value for this limit on the maximum number of retries by the CONNECT statement:

integer > 0: If the first attempt by the CONNECT statement to establish a connection to a database server fails, this value sets an upper limit on the number of additional attempts.

If no connection with another database has been established after (integer + 1) attempts, or after the time limit that CONNECT_TIMEOUT specifies has been exceeded, the CONNECT statement returns an error.

For the current session only, the setting of the CONNECT_RETRIES session environment option overrides any other value that is currently set for the CONNECT_RETRIES client environment variable or for the CONNECT_RETRIES configuration parameter.

Order of precedence for CONNECT_RETRIES settings

This is the descending order of precedence (highest to lowest) among methods for setting the maximum number of retry attempts for CONNECT statements:

The SET ENVIRONMENT CONNECT_RETRIES statement (for a session)
The CONNECT_RETRIES client environment variable setting.
If CONNECT_RETRIES configuration parameter in the onconfig file.
The system default value of 1 additional attempt to establish a new connection to a server.

Unless the setting of the CONNECT_RETRIES session environment option, or of the CONNECT_RETRIES configuration parameter, or of the CONNECT_RETRIES environment variable is an integer greater than zero, the default behavior of the CONNECT statement after a failed initial connection attempt is to attempt a single connection retry.

Examples of setting CONNECT_RETRIES

For example, the following statement resets the CONNECT_RETRIES session environment option to the default value of 1, restricting the database server to no more than a single additional connection attempt if the first attempt fails:

SET ENVIRONMENT CONNECT_RETRIES;

The next example specifies that, if the initial connection attempt fails, up to three additional connection attempts are made before the database server issues an error.

SET ENVIRONMENT CONNECT_RETRIES '3';

You can also use double ( " ) quotation marks to delimit the setting, which allows five retries (or up to a total of 6 connection attempts) in the following example:

SET ENVIRONMENT CONNECT_RETRIES "5";

Important:

If the CONNECT_TIMEOUT session environment option or the CONNECT_TIMEOUT configuration parameter is set to a time interval, that value takes precedence over the CONNECT_RETRIES setting. That is, the connection attempts can end after the limit in seconds from the CONNECT_TIMEOUT setting has elapsed, even if this limit is exceeded before the CONNECT_RETRIES limit on attempts is reached.