Enterprise Manager Java App: Connection Pools

In BBj 13.0 and higher, Enterprise Manager app is superceded by a new browser Enterprise Manager and Eclipse plug-in. See Enterprise Manager: Databases > Connection Pools.

Some applications require the use of connection pooling when connecting to third party JDBC/ODBC databases. Some use cases are:

There are a limited number of licenses available on the third party database but you have a larger number of users that need to use the system.
The application opens and closes a large number of connections and the third party database has problems cleaning up the closed connections fast enough.
Opening a new connection to the third party database takes too much time for a particular application.

A connection pool is an interface that sits between the application and the JDBC or ODBC Driver that actually communicates with the database. This allows an application to use a connection pool without making any changes to the application or the user experience.

What Is A Connection Pool?

Connection pools consist of zero or more open connections. When an application asks the pool for a connection, the pool may give the application a brand new connection (such as when the pool is empty or does not have any free connections available), or it may return an existing connection. The application does not care what kind of connection it receives; the pool manages the creation and distribution of connections.

Creating and Configuring a Connection Pool

Use The Enterprise Manager to configure connection pools. The following steps explain the process for adding a new pool or editing an existing pool:

Click on the "Connection Pools (3rd party)" node in the Enterprise Manager navigator.

The list of available connection pools displays in the right side pane on the Connection Pools panel.

Click [Add Pool] to add a new pool, or select an existing pool from the list and click [Edit Pool].

The Connection Pool dialog shows all of the settings for the connection pool:

Name	The name is a case-sensitive identifier for the connection pool. This is the name that the application uses in a call to SQLOPEN to acquire a connection from the pool.
JDBC Connect URL	This is the JDBC connection string that the pool should use when opening new connections to the database. The URL format is JDBC driver specific, so please consult the third party JDBC driver documentation for details.
JDBC Properties	Specify any properties that the JDBC driver should use when establishing a new connection. Each property value consists of a name and value in the format, NAME=VALUE. Separate each property with a comma. NOTE: This is how to specify a user name and password on global pools. For example: user=myself, password=mypass
Scope	There are two types of pools - global and user. The scope of a Global pool is the entire JVM, while the scope of a User pool is a particular user and password. For example, any application running within the BBjServices installation can access any Global connection pool with a simple SQLOPEN call taking no extra parameters. On the other hand, a User pool requires a user name and password that must be validated before the pool returns a connection.
When Exhausted	Specifies the behavior when the pool is exhausted or full: Fail: If the number of active connections in the pool is equal to the value in "Max. Active" and the application requests a connection from the pool, an error will occur. Grow: If the number of active connections in the pool reaches value the value in "Max. Active" and the application requests a connection from the pool, a new connection will be returned and the pool will grow in size. Block: If the number of active connections in the pool reaches value the value in "Max. Active" and the application requests a connection from the pool, the application will wait until a new or idle connection is available. If a positive "Max. Wait" value is supplied, the application will block for at most that many milliseconds, after which an error will occur. If "Max. Wait" is negative, the application will wait indefinitely.
Max. Active	Controls the maximum number of objects that can be borrowed from the pool at one time. When negative, there is no limit to the number of objects that may be active at one time. An active connection is one that is currently being used by the application. "Max. Active" is exceeded, the pool is said to be exhausted. See the description of the "When Exhausted" option for specific details on application behavior when the maximum number of active connections is exceeded.
Max. Idle	Controls the maximum number of connections that can sit idle in the pool at any time. An idle connection is one that is not currently being used by the application. When negative, there is no limit to the number of objects that may be idle at one time.
Max. Wait	The maximum amount of time (in milliseconds) that the application should wait for a connection to be made available from the pool. See the description of the "When Exhausted" option for specific details on application behavior when the maximum number of active connections is exceeded.
Time Between Eviction	Indicates how long (in milliseconds) the eviction thread should sleep before "runs" of examining idle objects. When non-positive, no eviction thread will be launched.
Min. Evictable	Specifies the minimum amount of time (in milliseconds) that a connection may sit idle in the pool before it is eligible for eviction due to idle time. When negative, no connections will be dropped from the pool due to idle time alone.
Test While Idle	If true, the pool runs the "Validation Query" to test the idle connections to see if they are still valid. If any of the connections are no longer valid, the pool evicts the stale connection.
Test On Borrow	If true, the pool runs the "Validation Query" to test the idle connection that the pool is about to make active. If the connection is not valid, the pool evicts that connection and tries another connection from the pool.
Test On Return	If true, the pool runs the "Validation Query" to test a connection when the application returns it to the pool (by calling SQLCLOSE). If the connection is not valid, the pool evicts that connection from the pool.
Validation Query	The SQL query that the pool should run when it needs to check the validity of a connection in the pool.
Read Only	If true, the pool opens new connections in read only mode.
Auto Commit	If true, the pool opens new connections in auto commit mode.
Transaction Isolation	The JDBC transaction isolation level that the pool should use when opening new connections.

Opening a Connection in a Global Pool

To open a connection from a Global connection pool, use code similar to the following:

SQLOPEN(chan)"MyGlobalPool"

All connections will have the same connection properties and user credentials when using Global pools. If this is a security issue for the application, use User pools.

Opening a Connection in a User Pool

To open a connection from a User connection pool, use code similar to the following:

SQLOPEN(chan, MODE="user=admin, password=admin123")"MyUserPool"

Notice that the User pool requires a user name and password if the third party database requires them. Each connection will be validated using the user and password so that the application can control access to the various connections in the pool.