Databases can be configured to terminate sessions that have been idle for some time, and networks often does the same with TCP/IP connection. The Connection Keep-Alive feature helps preventing connections to be closed due to time-outs of this kind by periodically executing a simple SELECT statement.
Network connections may be terminated for other reasons than a time-out in the database or at the network layer, e.g due to a restart of the database or a network element. The Connection Keep-Alive feature does not help in those cases. Also note that connections that are busy, e.g. actively used to run a script, are not "pinged". If a SELECT statement or stored procedure takes a very long time to complete, it is therefore possible that a time-out happens at the network level. In this case, the network configuration must be tuned to handle long running statements without timing out.
To enable Keep-Alive for a connection:
- Open the Object View tab for the connection,
- Open its Properties tab,
- Select the Physical Connection category,
- Modify or enter a simple SQL statement in the Validation and Keep-Alive SQL field, if needed (see note below),
- Enable Connection Keep-Alive and optionally change the Connection Idle Time,
- Click the Apply button.
The SELECT statement used for Connection Keep-Alive can also be specified in the properties pane. For supported databases, it is set to a SELECT statement that has been verified to work for the database type but for connections that use the Generic profile, you must specify a valid SELECT statement in order for this feature to work. For many databases,
SELECT 1 or
SELECT 1 FROM aSmallTable WHERE 1 = 0 should work.