Using connection pools helps to both alleviate connection
management overhead and decrease development tasks for data access.
Each time an application attempts to access a backend store, such
as a database, it requires resources to create, maintain, and release
a connection to that data store. To mitigate the strain this process
can place on overall application resources, the application server
enables administrators to establish a pool of backend connections
that applications can share on an application server. Connection pooling
spreads the connection overhead across several user requests, conserving
application resources for future requests.
About this task
Connection pooling can improve the response time of any application
that requires connections, especially Web-based applications. When
a user makes a request over the Web to a resource, the resource accesses
a data source. Because users connect and disconnect frequently with
applications on the Internet, the application requests for data access
can surge to considerable volume. Consequently, the total overhead
for a data store can become high for Web-based applications quickly,
and performance deteriorates. When connection pooling capabilities
are used, however, Web applications can realize performance improvements
of up to 20 times the normal results.
Avoid trouble: Connection pooling
is not supported in an application client. The application client
calls the database directly and does not go through a data source.
If you want to use the getConnection() request from the application
client, configure the JDBC provider in the application client deployment
descriptors, using Rational
® Application Developer
or an assembly tool. The connection is established between application
client and the database. Application clients do not have a connection
pool, but you can configure JDBC provider settings in the client deployment
descriptors.
gotcha
- Prevent a connection deadlock.
A deadlock
can occur if the application requires more than one concurrent connection
per thread, and the database connection pool is not large enough for
the number of threads. Suppose each of the application threads requires
two concurrent database connections and the number of threads is equal
to the maximum connection pool size. Deadlock can occur when both
of the following conditions are true:
- Each thread has its first database connection, and all are used.
- Each thread is waiting for a second database connection, and none
would become available since all threads are blocked.
To prevent the deadlock in this case, increase the maximum
connections value for the database connection pool by at least one.
This supports at least one of the waiting threads to obtain a second
database connection and avoid a deadlock scenario.
For general
prevention of connection deadlock, code your applications to use only
one connection per thread. If you code the application to require
C concurrent
database connections per thread, the connection pool must support
at least the following number of connections, where
T is
the maximum number of threads:
T * (C - 1) + 1
The
connection pool settings are directly related to the number of connections
that the database server is configured to support. If you increase
the maximum number of connections in the pool and the corresponding
settings in the database are not increased accordingly, the application
might fail. The resulting SQL exception errors would be displayed
in the following locations:
- the stderr.log file
- the SYSOUT of the servant
A common cause of connection deadlock is
the use of the same connection pool by both servlets and by Enterprise JavaBeans (EJBs), and where
the servlet directly or indirectly invokes the bean. For example,
a servlet that obtains a JMS connection from the connection pool,
sends a message to a Message Driven Bean (MDB) and waits for a reply.
The MDB is configured to use the same connection pool as the servlet,
therefore, another connection from the pool is required for the MDB
to send a reply to the servlet. Servlets and enterprise beans do not
share the same connection pool. This is a classic case of concurrent
(C) threads, where C=2 and T is the maximum size of the servlet
and EJB thread pools.
- Disable connection pooling.
- For relational resource adapters (RRAs), add the disableWASConnectionPooling custom
property for your data sources.
- Click JDBC > Data sources.
- Click the name of the data source that you want to configure.
- Click Custom properties under the Additional
Properties heading.
- Click New.
- Complete the required fields with the following information:
- Name: disableWASConnectionPooling
- Value: true
- For other resource adapters, consult with the binding specifications
for that resource adapter to configure your applications to disable
connection pooling.
- Programmatically disable connection pooling through the resource
adapter.
- The application server uses the following code to detect the javax.resource.NotSupportedException
exception and disable connection pooling:
_managedFactory.matchManagedConnections(s,subject,cri); // 169059 174269 }
catch(javax.resource.NotSupportedException e){
- Enable deferred enlistment.
In the application
server environment, deferred enlistment refers to the technique in
which the application server waits until the connection is used before
the connection is enlisted in the application unit of work (UOW) scope.
Consider
the following illustration of deferred enlistment:
- An application component that uses deferred enlistment calls the getConnection method
from within a global transaction.
- The application component does not immediately use the connection.
- When the application issues the call for initial use of the connection,
the transaction manager intercepts the call.
- The transaction manager enlists the XA resource for the connection
and calls the XAResource.start method.
- The connection manager associated with the XA resource sends the
call to the database.
Given the same scenario, but the application component does not
use deferred enlistment, the component container immediately enlists
the connection in the transaction. Thus the application server incurs,
for no purpose, an additional load of all of the overhead associated
with that transaction. For XA connections, this overhead includes
the two-phase commit (2PC) protocol to the resource manager.
Deferred
enlistment offers better performance in the case where a connection
is obtained, but not used, within the UOW scope. The technique saves
the cost of transaction participation until the UOW in which participation
must occur.
Check with your resource adapter provider if you
must know if the resource adapter provides this function. The application
server relational resource adapter automatically supports deferred
enlistment.
Incorporating deferred enlistment in your code:
The Java Platform, Enterprise Edition
(Java EE) Connector Architecture
(JCA) Version 1.5 specification calls the deferred enlistment technique
lazy transaction enlistment optimization. This support comes through
a marker interface (LazyEnlistableManagedConnection) and a new method
on the connection manager (LazyEnlistableConnectionManager()):
package javax.resource.spi; import javax.resource.ResourceException; import
javax.transaction.xa.Xid; interface LazyEnlistableConnectionManager { // application server void
lazyEnlist(ManagedConnection) throws ResourceException; } interface LazyEnlistableManagedConnection { // resource adapter }
- Control connection pool sharing.
You can use the defaultConnectionTypeOverride or globalConnectionTypeOverride
connection pool custom property for a particular connection factory
or data source to control connection sharing or to globally set a
data source connection type:
- The defaultConnectionTypeOverride property changes the default
sharing value for a connection pool. This property enables you to
control connection sharing for direct queries. If resource references
are configured for this data source and connection factory, they take
precedence over this property and the resource reference settings
are used. For example, if an application is doing direct queries,
and you do not want the connections pool shared. set this property
to unshared.
- The value specified for the globalConnectionTypeOverride custom
property takes precedence over all of the other connection sharing
settings. For example, if you set this property to unshared, all connection
requests are unshared for both direct queries and resource reference
lookups. This property provides you with a quick way to test the consequences
of moving all connections for a particular data source or connection
factory to unshared or shared without changing the resource reference
setting in all the deployment descriptors for an application. The
globalConnectionTypeOverride property also enables you to move between
shared, and unshared connections for a particular data source or
connection factory without changing any resource references.
If you specify values for both the defaultConnectionTypeOverride
and the globalConnectionTypeOverride properties, only the values specified
for the globalConnectionTypeOverride property is used to determine
connection sharing type.
To add these new custom
properties to the settings for a data source or connection factory
connection pool, a new connection pool custom property must be created.
To add one of these properties to a data source, use the administrative
console. Click select your data source from the list . For other J2C or JMS connection
factories, navigate to the connection factory definition in the administrative
console. Then select . Now
specify
defaultConnectionTypeOverride or
globalConnectionTypeOverride in
the
Name field and
shared or
unshared in
the
Value field.
Important: The
properties must be set in the Connection pool custom properties and
not the general Custom propeties on the data
source or connection factory.
- Discard connections.
Reap time
and unused timeout settings do not cause the idle or unused connections
to be discarded if the servant region is idle. This situation might
cause some DB2 connections to be held longer than is necessary.
If
you prefer to have the connections discarded at the time specified
by a combination of reaper time and unused timeout settings, even
if this preference might cause an idle servant region to become active
again, you can add the nondeferredreaper custom property to your JDBC
driver provider data source settings. When you add this custom property,
connections are discarded at the time specified by a combination of
reaper time and unused timeout settings.
To add this custom
property to your JDBC driver provider data source settings, in the
administrative console, click . Then
specify nondeferredreaper in the Name field, true in
the Value field, and java.lang.Boolean in
the Type field. This new setting does not go
into effect until you restart the server that is using this data source.
Avoid trouble: Activating an idle servant region for the sole
purpose of discarding unused connection, might cause additional and
sometimes undesirable CPU usage. Also, the following warning message
might be logged and should be ignored:
DSRA8200W: DataSource Configuration:
DSRA8020E: Warning: The property 'nondeferredreaper' does not exist on the DataSource
classcom.ibm.db2.jcc.DB2ConnectionPoolDataSource.
gotcha
- Purge connection pools based on
the purge policy.
When the connection pool error detection
model is configured to exception mapping, the stale connection exception
indicates that the connection is no longer valid.
Typically,
when a StaleConnectionException results from the exception mapping
process a connection error event is fired and subsequently the connection
pool gets purged. However, in this case, the SCE is instantiated,
the existing code does not have the functionality to fire the connection
error event and the pool does not get purged When connections are
terminated on the database side when requesting a new connection,
the driver throws a XAException. If the errorDetectionModel=ExceptionMapping
results, a ConnectionErrorEvent will be fired so that the connection
pool gets purged based on the purge policy.
To add this custom
property to your JDBC driver provider data source settings, in the
administrative console, click . Then
specify fireCEEventOnSCE in the Name field, true in
the Value field, and java.lang.Boolean in
the Type field. This new setting does not go
into effect until you restart the server that is using this data source.
The
WebSphere RRA code has been changed so that the pool is purged properly
upon a StaleConnectionException when using ExceptionMapping as the
errorDetection model.