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 | |
| $SQLUseConnectionPool | whether 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 |