Connection Pools

Database connection pools are a common way to improve the performance of database operations. They can be useful because creating a new connection can easily take several seconds to establish; this is a problem when the database operation is one that only needs a few milliseconds. DatabaseLink provides a connection pool mechanism built on top of the Apache Commons DBCP, http://jakarta.apache.org/commons/dbcp/index.html.

Working with Connection Pools

If you find that the examples in this tutorial do not work as shown, you may need to install or restore the example database with the "DatabaseLink`DatabaseExamples`" package, as described in "Using the Example Databases".

To create a connection from a pool, you can set the UseConnectionPool option of OpenSQLConnection. Here is an example.

First, the DatabaseLink package is loaded. Then a connection using a pool is made to the publisher example database.

Instead of using the UseConnectionPool option, you could set the default value $SQLUseConnectionPool to True. When DatabaseLink loads it is False.

OpenSQLConnection[src,UseConnectionPool->True]
connect to a data source using a connection pool
$SQLUseConnectionPoolwhether to always use connection pools
SQLConnectionPools[]information on all active connection pools
SQLConnectionPools[conn]information on pool for connection conn
SetOptions[pool,opts]set options opts for connection pool pool
SQLConnectionPoolClose[pool]close the connection pool pool

Commands for working with connection pools.

This shows all the connection pools that have been created; there is only one.

This shows the connection pool used to connect to the publisher database. You can see some of the options that the connection pool is using.

This closes the connection pool, and it also closes any connections that the pool is using.

Connection Pool Options

There are a number of options that control how the connection pool operates. This example shows how to work with them.

First, the DatabaseLink package is loaded. Then a connection using a pool is made to the publisher example database.

This shows all the connection pools that have been created; there is only one.

This shows the connection pool used to connect to the publisher database. You can see some of the options that the connection pool is using.

This sets the MaximumActiveConnections option of this connection pool.

SQLConnectionPools[]information on all active connection pools
SQLConnectionPools[conn]information on pool for connection conn
SetOptions[pool,opts]set options opts for connection pool pool
CloseConnectionPool[pool]close the connection pool pool

Functions for working with connection pool options.

option name
"MaximumActiveConnections"maximum number of connections to keep in the pool
"MinimumIdleConnections"minimum number of idle connections to keep in the pool
"MaximumIdleConnections"maximum number of idle connections to keep in the pool
"Catalog"location of the database catalog
"ReadOnly"set the connection to be read only
"TransactionIsolationLevel"set transaction isolation for the connection

Connection pool options.