Understanding the different implementations of database Connection Pooling.

Content

DB2 Information Integrator for Content users often want to understand the difference in implementation of database connection pooling by DB2 Connect servers, Websphere Application Servers and DB2 Information Integrator for Content JavaBeans. This article will discuss the basic concepts including the common and different aspects of database connection pooling implementations by DB2 Connect Servers, Websphere Application Servers and DB2 Information Integrator for Content JavaBeans.

All three database connection pooling implementations are designed to save the cost of establishing a connection to the host when one is no longer needed by a terminating application. That is one application can reuse a pooled connection agent released when another application gets terminated. Therefore in a database connection pooling setup environment, applications need to close connections in a normal fashion in order to make connection agents available for reuse.

DB2 Connection Pooling:

The connection pooling, provided by DB2 Connect servers (gateways) is a mechanism where the applications from multiple clients and application servers, all with different user IDs, can reuse each other’s outbound connections to the host, resulting in a much better utilization of pooled resources.

The connection pooling is on when the database manager configuration parameter NUM_POOLAGENTS is greater than zero, in which case the gateway will keep up to that number of agents in a pool. The default value for NUM_POOLAGENTS is 100, i.e. DB2 Connection Pooling is ON by default. Once an application disconnects from the server, the agent that served that application will go to the pool and its connection with the server is kept alive. Such an agent is now ready to serve another application that may need the connection to the same database.

Database Connection Pooling mechanisms implemented by Websphere Application servers differ significantly from what is provided by the DB2 Connect servers. Since Websphere application servers pool connections only for their own use they typically presume that user id, password, isolation levels and so on will be exactly the same for all connections. Even more important, application servers only pool connections initiated by the same process. This means that connections from other machines, users or processes are not pooled. While these application server pooling techniques are effective for reusing connections established by the same instance of an application they are absolutely ineffective for pooling connections from multiple users, servers etc.

Connection pooling, provided by the DB2 Connect servers, is completely application, machine and user independent. Connections from multiple clients, application servers all with different user ids can all reuse each other's connections resulting in a much better utilization of the pooled resources.

Websphere Connection Pooling:

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 datastore. To mitigate the strain this process can place on overall application resources, WebSphere 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, thereby conserving application resources for future requests. The connection pool is used to direct JDBC calls within the application, as well as for enterprise beans using a database.

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 datastore overhead quickly becomes high for Web-based applications, and performance deteriorates. When connection pooling capabilities are used, however, Web applications can realize significant performance improvements when compared to the normal results.

With connection pooling, most user requests do not incur the overhead of creating a new connection because the data source can locate and use an existing connection from the pool of connections. When the request is satisfied and the response is returned to the user, the resource returns the connection to the connection pool for reuse. The overhead of a disconnection is avoided. Each user request incurs a fraction of the cost for connecting or disconnecting. After the initial resources are used to produce the connections in the pool, additional overhead is insignificant because the existing connections are reused.

Use WebSphere connection pooling in an application that meets any of the following criteria:

• It cannot tolerate the overhead of obtaining and releasing connections whenever a connection is used.
• It needs to share connections among multiple users.
• It needs to take advantage of product features for managing local transactions within the application server.
• It does not manage the pooling of its own connections.
• It does not manage the specifics of creating a connection, such as the database name, user name, or password.

Furthermore, Application Server creates a separate instance of the connection pool in each application server that uses the data source. For example, if you run a three server cluster in which all of the servers use myDataSource, and myDataSource has a Maximum Connections setting of 10, then you can generate up to 30 connections (three servers times 10 connections). Consider how this behavior potentially impacts the number of connections that your backend resource can support.

DB2 Information Integrator for Content JavaBeans Connection Pooling:

The CMBConnectionPool bean maintains a pool of live and connected CMBConnection instances. CMBConnection beans wrap DKDatastore instances. Therefore, the pool is in effect a pool of DKDatastore objects. Because the datastore instances held by the pool are connected and remain connected for as long as they are managed by the pool, each instance is tied to the specific user ID and password that were used to create the datastore instance.

The connection pool will return only a pre-existing connection object from the pool if the user ID and password in the new connection request exactly matches the user ID and password data. If no existing connection is available, then the pool creates a new connection for that user ID. It does not attempt to disconnect or reuse one of the idle connections in the pool that are connected with another user’s credentials.

You can use the CMBConnectionPool when your application is designed such that in the course of using the application, the same user ID makes multiple requests for a connection object, and returns the connection to the pool after performing a few tasks with the connection.

If your application is designed such that a single user ID never requests a connection more than once per session, there is no benefit to using a CMBConnectionPool.

The CMBConnectionPool bean has two options when no matching connection is found in the pool for a connection request and there is no more room in the pool to create a new connection object. This behavior is controlled by the maxConnectionsBehavior property. The default value for this property is CMBConnectionPool.CMB_MAX_CONNECTIONS_ERROR. When no connection is found in the pool that matches the credential information supplied in a getConnection request, and if there is not enough room in the pool to create a new connection for the user, the CMBConnectionPool returns an error. The other possible setting for this property is CMBConnectionPool.CMB_MAX_CONNECTIONS_QUEUE, which enables thread queuing. When no matching connection is found and there is not enough room in the pool to create a connection, the thread making the connection request is put into a wait state. The thread waits until one of following situations occur:

• A connection with matching credentials is freed back to the pool by another thread. In this case, the first waiting thread with credentials that match those of the connection being freed is activated, and the connection that was just freed will be returned to that thread.
• The thread wait time is exceeded. You can specify the maximum amount of time you want a thread to wait for an available connection by setting the maxThreadWaitTime property on the CMBConnectionPool. If your thread waits for longer than the wait time and there is still no connection available to fulfill the getConnection request, the request will return with an exception.
• A connection that is being freed to the pool must be destroyed. When putting a connection back into the free pool violates one of the free connection tuning parameters, that connection is destroyed instead of being returned to the pool. If any threads are waiting, the first waiting thread is activated and a new connection is created for that thread.
• One of the connections in the free pool times out and must be destroyed. You can specify the maximum amount of time that a connection can remain idle in the free pool by setting the maxWaitTime property on the CMBConnectionPool. Any connection that remains idle in the free pool for longer than the maxWaitTime is destroyed. At the time the expired connection is destroyed, if there are any threads waiting, a new connection is created for the first waiting thread.

Which type of connection pooling is the right one to use?

All. Generally, using both DB2 Connect connection pooling and Websphere Application Server connection pooling is a good strategy since they don't interfere with each other. Even when application server connection pooling is enabled, DB2 Connect connection pooling can provide connection reuse for multiple application servers as well as other clients using the DB2 Connect server. Generally Websphere Application Server Connection Pooling is recommended for DB2 Information Integrator for Content JavaBeans API applications to eliminate the underlying database connection overhead.