Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - Database Administration » SQL Anywhere database connections » Database connections


Connection pooling

Connection pooling may improve the performance of applications that make multiple, brief connections to the database server. If connection pooling is enabled for a connection, when it is disconnected, the connection is automatically cached and may be reused when the application reconnects. You control connection pooling with the ConnectionPool (CPOOL) connection parameter. Once an application makes a specified number of connections with the same connection string, then the connection is pooled.

An application must make five connections with the same connection string before a connection is cached. The connection name can be different each time, but all other connection parameters must be identical for a cached connection to be reused.

If the application process connects again and there are cached connections available for the same connection string, the cached connection is reused. Connections remain in the cached state for the time specified by the ConnectionPool (CPOOL) connection parameter (60 seconds by default).

Cached connections are not reused if it would change the behavior of the application. For example, cached connections are not reused:

  • For databases that stop automatically when there are no connections to them.

  • If connections are disabled.

  • If the database server has reached its connection limit.

  • If a password has changed.

  • If the login_mode option is set.

To ensure that connection pooling is transparent to the application, a connection is disconnected if a failure occurs when caching a connection. If a failure occurs when attempting to reuse a cached connection, the database server attempts to connect normally.

A connection is cached if it is disconnected and the maximum number of connections specified by the CPOOL connection parameter has not been reached. The connection is reinitialized, and the cached connection remains connected to the database server even though the application has disconnected it. The cleanup and reinitialization of a connection includes the following activities:

  • Rolling back all outstanding transactions.

  • Dropping temporary tables, temporary functions, and variables.

  • Resetting connection options and connection counters.

  • Decrementing and incrementing the database server connection counts. You are not informed that there are active connections when a database server with only cached connections shuts down.

  • Executing all defined disconnect and connect events.

  • Executing the login_procedure database option and verifying the login policy.

  • Resetting the connection ID.

 Using SQL Anywhere connection pooling with other connection pooling products
 Connection pooling and read-only scale-out
 See also