Using connection pools helps to both alleviate excessive connection management 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. Tuning connection pools can mitigate the strain on overall application resources.
Administrators can establish a pool of backend connections that applications can share on an
application server. Connection pooling spreads the connection processing across several user
requests and conserves 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 you make a request over the web to a resource,
the resource accesses a data source. Because you connect and disconnect frequently with applications
on the Internet, the application requests for data access can surge to considerable volume.
Therefore, the total processing activity for a data store can quickly become high for web-based
applications, causing performance to deteriorate. When connection pooling capabilities are used, web
applications can realize performance improvements of up to 20 times the normal results.Note: 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.
Procedure
- Prevent a connection deadlock.
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 in use.
- 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 ensures that at least one of the waiting threads obtains a
second database connection and avoids 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 are displayed in the following locations:
- the stderr.log file
A common cause of connection deadlock occurs when the same connection pool is used
by both servlets and by Enterprise JavaBeans (EJBs). The deadlock occurs when the servlet directly
or indirectly invokes the bean. For example, when a servlet obtains a JMS connection from the
connection pool and 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 needed for the MDB to send a reply to the servlet. Servlets and enterprise beans do not use
the same connection pool. This illustration 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 on 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 leverages 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's 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
need to know if the resource adapter provides this functionality.
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 and later specification calls
the deferred enlistment technique lazy transaction enlistment optimization.
This support comes through a marker interface (LazyEnlistableManagedConnection)
and a 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
globaldefaultConnectionTypeOverride connection pool custom property for a
particular connection factory or data source to control connection sharing:
- 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 or connection factory the
resource reference's configurations take precedence over the
defaultConnectionTypeOverride property settings. For example, if an application
is doing direct queries and unshared connections are needed, set
thedefaultConnectionTypeOverride property to
unshared
.
- The value specified for the globaldefaultConnectionTypeOverride 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 any resource reference setting.
Important: If you specify values for both the
defaultConnectionTypeOverride and the
globaldefaultConnectionTypeOverride properties, only the values specified for
the globaldefaultConnectionTypeOverride property are 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. Then under Additional Properties, click . Now specify defaultConnectionTypeOverride or
globalConnectionTypeOverride in the Name field and
shared or unshared in the Value
field.
- For other J2C or JMS connection factories, navigate to the connection factory definition in the
administrative console. Then under Additional Properties, click . 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 properties 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.
You can add the nondeferredreaper custom property to your JDBC driver
provider data source settings. This addition has the connections discarded at the time that is
specified by a combination of reaper time and unused timeout settings. Adding the
nondeferredreaper custom property can cause an idle servant region to become
active again.
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.
- 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, a connection error event is fired when a stale connection exception
(StaleConnectionException) results from the exception-mapping process. Thus, the connection pool
gets purged. However, in rare cases during exception mapping, the stale connection exception is
mapped and created, but the connection error event is not fired. Thus, the connection pool is not
purged. If you encounter this rare situation, resolve the problem by setting the
fireCEEventOnSCE data source custom property. This custom property can help
fire the connection error event and purge the connection pool.
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.