Database connection pooling

A database connection pool is a collection of unused database connections that are already open. The pool allows queries to be quickly associated with an available connection.

References

For more information, see the following topics:

When a query is executed, Dynamic Query searches a connection pool to locate an idle connection that can be borrowed. If a connection is found, it is borrowed and subsequently returned to the pool after the request has completed. If a connection cannot be found, a new connection is created and is added to the pool for future use. Entries in the connection pool are automatically closed if they have remained idle for a period of 300 seconds (five minutes).

You can change the default timeout for the connection pool by adjusting the Advanced Server properties for the Query Service. You can set the parameter qs.queryExecution.defaultIdleConnectionTimeout to an integer value that is greater than or equal to 15 seconds and less than or equal to . New connections that are added to the pool will time out after the specified number of seconds.

If required, you can specify timeouts for specific connections using the ibmcognos.connectionTimeout name-value pair. You can set the value to an integer value that is greater than or equal to 15 seconds and less than or equal to <.

The default timeout values work well in most environments. However, you may need to lower them in the following cases:

  • when the database server settings close idle connections in the database server sooner than the default timeout value
  • when the database system is configured with a low maximum number of connections, either globally or per user

In more extreme cases, you can disable pooling for connections for which the ibmcognos.isConnectionReusable parameter is set to false. By default, this value is set to true, allowing Dynamic Query to pool connections.

Features that influence connection pool sizes

When Dynamic Query scans the pool for a re-usable connection, it takes into account these criteria:

  • the connection name
  • the database credentials used to create the connection and to process the request
  • the connection command block definition

If several Cognos Analytics users share the same database signon associated with a connection, there is a greater opportunity to re-use pooled connections. However, if most users authenticate to the data source using distinct credentials (for example, a user name/password, a Kerberos ticket, or an access token), the opportunity to re-use connections is decreased.

You can use connection command blocks to alter the state of a database connection, which can influence how a database computes result sets. Command blocks can include static text and values that are calculated (resolved) using macros. Dynamic Query compares the fully resolved command block text of a pooled connection to the resolved text for the new query. If they are not the same, the pooled connection cannot be used.

Tip: Avoid creating command blocks that include macros that change frequently. Doing so can force more connections to be created than is preferable.

Changing the default settings

While the default values work well in most environments, you may want to change them in any of these cases:

  • when the database administrator configured database settings to close idle connections sooner
  • when the database administrator set very low limits on how many connections a user can have
  • when the database environment has a low limit on concurrently opened database connections

Setting the connection timeout to be lower than the database enforced timeouts allows Dynamic Query to gracefully close the connections.

If the database server connection limits are small, then using a low timeout of no pooling of connections may be required.

Tip: Avoid using small connection timeout values or disabling connection pooling unless there are reasons to do so. Small timeout values can impact performance by increasing the number of connections that Dynamic Query must create prior to running queries.