Using a Connection Pool

Home | Previous Page | Next Page Tuning and Troubleshooting > Managing Performance >

Using a Connection Pool

To improve the performance and scalability of your application, you can obtain your connection to the database server through a DataSource object that references a ConnectionPoolDataSource object. IBM Informix JDBC Driver provides a Connection Pool Manager as a transparent component of the ConnectionPoolDataSource object. The Connection Pool Manager keeps a closed connection in a pool instead of returning the connection to the database server as closed. Whenever a user requests a new connection, the Connection Pool Manager gets the connection from the pool, avoiding the overhead of having the server close and re-open the connection.

Using the ConnectionPoolDataSource object can significantly improve performance in cases where your application receives frequent, periodic connection requests.

For complete information about how and why to use a DataSource or ConnectionPoolDataSource object, see the JDBC 3.0 API provided by Sun Microsystems, available from the following Web site: http://java.sun.com.

Important:

This feature does not affect IfxXAConnectionPoolDataSource, which operates under the assumption that connection pooling is handled by the transaction manager.

The following sections discuss how to use connection pooling with IBM Informix JDBC Driver:

Deploying a ConnectionPoolDataSource Object

In the following steps:

The variable cpds refers to a ConnectionPoolDataSource object.
The JNDI logical name for the ConnectionPoolDataSource object is myCPDS.
The variable ds refers to a DataSource object.
The logical name for the DataSource object is DS_Pool.

To deploy a ConnectionPoolDataSource object

Instantiate an IfxConnectionPoolDataSource object.

Set any desired tuning properties for the object:

cpds.setIfxCPMInitPoolSize(15);
cpds.setIfxCPMMinPoolSize(2);
cpds.setIfxCPMMaxPoolSize(20);
cpds.setIfxCPMServiceInterval(30);

Register the ConnectionPoolDataSource object using JNDI to map a logical name to the object:
```
Context ctx = new InitialContext();
ctx.bind("myCPDS",cpds);
```
Instantiate an IfxDataSource object.
Associate the DataSource object with the logical name you registered for the ConnectionPoolDataSource object:
```
ds.setDataSourceName("myCPDS",ds);
```

Context ctx = new InitialContext();
ctx.bind("DS_Pool",ds);

Tuning the Connection Pool Manager

During the deployment phase, you or your database administrator can control how connection pooling works in your applications by setting values for any of these Connection Pool Manager properties:

IFMX_CPM_INIT_POOLSIZE lets you specify the initial number of connections to be allocated for the pool when the ConnectionPoolDataSource object is first instantiated and the pool is initialized. The default is 0.
Set this property if your application will need many connections when the ConnectionPoolDataSource object is first instantiated.

To obtain the value, call getIfxCPMInitPoolSize().

To set the value, call setIfxCPMInitPoolSize (int init).
IFMX_CPM_MAX_CONNECTIONS lets you specify the maximum number of simultaneous physical connections that the DataSource object can have with the server.
The value -1 specifies an unlimited number. The default is -1.

To obtain the value, call getIfxCPMMaxConnections().

To set the value, call setIfxCPMMaxConnections(int limit).
IFMX_CPM_MIN_POOLSIZE lets you specify the minimum number of connections to maintain in the pool. See the IFMX_CPM_MIN_AGELIMIT parameter for what to do when this minimum number of connections kept in the pool exceeds the age limit. The default is 0.
To obtain the value, call getIfxCPMMinPoolSize().

To set the value, call setIfxCPMMinPoolSize(int min).
IFMX_CPM_MAX_POOLSIZE lets you specify the maximum number of connections to maintain in the pool. When the pool reaches this size, all connections return to the server. The default is 50.
To obtain the value, call getIfxCPMMaxPoolSize().

To set the value, call setIfxCPMMaxPoolSize(int max).
IFMX_CPM_AGELIMIT lets you specify the time, in seconds, that a free connection is kept in the free connection pool.
The default is -1, which means that the free connections are retained until the client terminates.

To obtain the value, call getIfxCPMAgeLimit().

To set the value, call setIfxCPMAgeLimit(long limit).
IFMX_CPM_MIN_AGELIMIT lets you specify the additional time, in seconds, that a connection in the free connection pool is retained when no connection requests have been received.
Use this setting to reduce resources held in the pool when there are expected periods in which no connection requests will be made. A value of 0 indicates that no additional time is given to a connection in the minimum pool: the connection is released to the server whenever it exceeds IFMX_CPM_AGELIMIT.

The default is -1, which means that a minimum number of free connections is retained until the client terminates.

To obtain the value, call getIfxCPMMinAgeLimit().

To set the value, call setIfxCPMAgeMinLimit(long limit).
IFMX_CPM_SERVICE_INTERVAL lets you specify the pool service frequency, in milliseconds.
Pool service activity includes adding free connections (if the number of free connections falls below the minimum value) and removing free connections. The default is 50.

To obtain the value, call getIfxCPMServiceInterval().

To set the value, call setIfxCPMServiceInterval (long interval).
IFMX_CPM_ENABLE_SWITCH_HDRPOOL lets you specify whether to allow automatic switching between the primary and secondary connection pools of an HDR database server pair.
Set this property if your application relies on High-Availability Data Replication with connection pooling. The default is false.

To obtain the value, call getIfxCPMSwitchHDRPool().

To set the value, call setIfxCPMSwitchHDRPool(boolean flag).

A demonstration program is available in the connection-pool directory within the demo directory where your JDBC driver is installed. For connection pooling with HDR, a demonstration program is available in the hdr directory within the demo directory. For details about the files, see Appendix A. Sample Code Files.

Some of these properties overlap Sun JDBC 3.0 properties. The following table lists the Sun JDBC 3.0 properties and their Informix equivalents.

Sun JDBC Property Name	Informix Property Name	Notes
initialPoolSize	IFMX_CPM_INIT_POOLSIZE
maxPoolSize	IFMX_CPM_MAX_POOLSIZE	For maxPoolSize, 0 indicates no maximum size. For IFMX_CPM_MAX_ POOLSIZE, you must specify a value.
minPoolSize	IFMX_CPM_MIN_POOLSIZE
maxIdleTime	IFMX_CPM_AGELIMIT	For maxIdleTime, 0 indicates no time limit. For IFMX_CPM_ AGELIMIT, -1 indicates no time limit.

The following Sun JDBC 3.0 properties are not supported:

maxStatements
propertyCycle

Using High-Availability Data Replication with Connection Pooling

IBM Informix JDBC Driver implementation of connection pooling provides the ability to pool connections with database servers in an HDR pair:

The primary pool contains connections to the primary server in an HDR pair.
The secondary pool contains connections to the secondary server in an HDR pair.

You do not have to change application code to take advantage of connection pooling with HDR. Set the IFMX_CPM_ENABLE_SWITCH_HDRPOOL property to true to allow switching between the two pools. When switching is allowed, the Connection Pool Manager validates and activates the appropriate connection pool.

When the primary server fails, the Connection Pool Manager activates the secondary pool. When the secondary pool is active, the Connection Pool Manager validates the state of the pool to check if the primary server is running. If the primary server is running, the Connection Pool Manager switches new connections to the primary server and sets the active pool to the primary pool.

If IFMX_CPM_ENABLE_SWITCH_HDRPOOL is set to false, you can force switching to the other connection pool by calling the activateHDRPool_Primary() or activateHDRPool_Secondary() methods:

public void activateHDRPool_Primary(void) throws SQLException
public void activateHDRPool_Secondary(void) throws SQLException

The activateHDRPool_Primary() method switches the primary connection pool to be the active connection pool. The activateHDRPool_Secondary() method switches the secondary connection pool to be the active pool.

You can use the isReadOnly(), isHDREnabled(), and getHDRtype() methods with connection pooling (see Checking for Read-Only Status).

A demonstration program is available in the hdr directory within the demo directory where IBM Informix JDBC Driver is installed. For details about the files, see Appendix A. Sample Code Files.

Cleaning Pooled Connections

You can alter connections from their original, default properties by setting database properties, such as AUTOCOMMIT and TRANSACTION ISOLATION. When a connection is closed, these properties revert to their default values. However, a pooled connection does not automatically revert to default properties when it is returned to the pool.

In IBM Informix JDBC Driver, you can call the scrubConnection() method to:

Reset the database properties and connection level properties to the default values.
Close open cursors and transactions.
Retain all statements.

This now enables the application server to cache the statements, and it can be used across applications and sessions to provide better performance for end-user applications.

The signature of the scrubConnection() method is:

public void scrubConnection() throws SQLException

The following example demonstrates how to call scrubConnection():

try
  {
  IfmxConnection conn = (IfmxConnection)myConn;
  conn.scrubConnection();
  }
catch (SQLException e)
  {
  e.printStackTrace();
  }

The following method verifies whether a call to scrubConnection() has released all statements:

public boolean scrubConnectionReleasesAllStatements()

Managing Connections

The following table contrasts different implementations of the connection.close() and scrubConnection() methods when they are in connection pool setup or not.

Connection Pooling Status	Behavior with connection.close() Method	Behavior with scrubconnection() Method
Non-connection pool setup	Closes database connection, all associated statement objects, and their result sets Connection is no longer valid.	Returns connection to default state, keeps opened statements, but closes result sets Connection is still valid. Releases resources associated with result sets only.
Connection Pool with Informix Implementation	Closes connection to the database and reopens it to close any statements associated with the connection object and reset the connection to its original state Connection object is then returned to the connection pool and is available when requested by a new application connection.	Returns a connection to the default state and keeps all open statements, but closes all result sets. Calling this method is not recommended here.
Connection Pool with AppServer Implementation	Defined by user's connection pooling implementation	Returns connection to default state and retains opened statements, but closes result sets