Connection Pooling

Overview

Connection pooling is a reserve of database connections that are maintained in SAS Federation Server so that the connections can be reused as future requests to the database are required. Opening and maintaining a database connection for each user, especially requests made dynamically, is costly and resource intensive. With connection pooling, connections are created and placed into the pool to be used over again so that a new connection to a back-end data source does not have to be reestablished. This practice reduces the amount of time it takes to establish a connection to a database. If all the pooled connections are in use, and the pool is large enough to hold a new connection, then a new connection is made and added to the pool.

If connection pooling is enabled, the client connects to a data source as usual. If there is an existing database connection in the connection pool that meets the client’s requirements (for example, a connection to the desired database using the applicable credentials), then that connection will be used by the client. Otherwise, a new connection is created.

When the client disconnects, the server evaluates whether to keep the underlying connection in the connection pool, or whether to free it. If the connection will not be pooled, then the connection is freed when the client frees its connection handle.

Options

The following options control connection pooling on the server. The options are controlled by the ALTER SERVER DDL statement.

Enable Connection Pooling

CONNECTION_POOLING
[N[O]|F[ALSE]|OFF|0|Y[ES]|T[RUE]|ON|1]

This option controls whether connection pooling is enabled or disabled for the server. If connection pooling is switched on, connections to databases are not disconnected immediately when the client requests to disconnect from the database. The connections are put into a pool of connections that can be reused by subsequent requests to connect to the same database with the same attributes and credentials. Connections used for Memory Data Store cannot be pooled.

To disable connection pooling, use DROP_CONNECTION_POOLING. Configuring ENABLE_CONNECTION_POOLING using a value of 0 (zero) is invalid and does not disable connection pooling.

Connection Pool Timeout

CONNECTION_POOL_TIMEOUT
seconds

This option identifies the time in seconds an unused connection stays in the connection pool. The default is 60 seconds. If the time is exceeded, the connection is removed from the pool and the connection is closed. A value of -1 indicates that the connection never times out and can stay in the pool indefinitely. These connections are freed when the server is stopped. To disable the timeout, use DROP_CONNECTION_POOL_TIMEOUT. Configuring CONNECTION_POOL_TIMEOUT using a value of 0 (zero) does not disable the timeout.

Maximum Unused Connections

CONNECTION_POOL_MAXSIZE maxsize

This option identifies the maximum number of unused connections in the connection pool. The default is 50. If the maximum number of connections is reached and a new connection is added to the connection pool, the oldest connection is removed from the pool and that connection is closed. If this option is set at 0, the default of 50 is used. Connections used for Memory Data Store cannot be pooled.

Drop Connection Pooling

DROP CONNECTION_POOLING

This option drops and also disables the connection pooling option.

Drop Connection Pool Timeout Option

DROP CONNECTION_POOL_TIMEOUT

This option removes the connection pool timeout value. If connection pooling is enabled and connection pool timeout option has been dropped, it will use the default timeout value, 60.

Drop Connection Pool Maxsize Option

DROP CONNECTION_POOL_MAXSZIZE

This option removes connection pool maxsize value. If connection pooling is enabled and connection pool maxsize option has been dropped, it will use the default maxsize value, 50.