Connection pooling
This problem is especially evident in web environments where each visit to a web page can require building a new connection to the database server, performing a query and terminating a connection. Most applications based on web technologies execute large volume of short transactions. A typical web transaction is executed as part of its own connection. In other words, executing a transaction means establishing a database connection and then terminating this connection only after a few SQL statements. This process of establishing and tearing down a connection is very costly. It involves creation of a Db2 Connect agent, establishing a network connection between this agent and the Db2® server, and creation of a Db2 thread on the server. For longer running connections these costs are amortized over all of the transactions executed on this connection but for a typical web transaction these costs will typically exceed the cost of executing the transaction itself.
Connection pooling is a technique that allows reuse of an established connection infrastructure for subsequent connections. When a Db2 Connect instance is started a pool of coordinating agents is created. When a connection request comes in an agent is assigned to this request. The agent will connect to the Db2 server and a thread will be created. When the application issues a disconnect request, the agent will not pass this request along to the Db2 server. Instead, the agent is put back in to the pool. The agent in the pool still owns its connection to the Db2 server and the corresponding Db2 thread. When another application issues a connect request, this agent is assigned to this new application. To insure secure operation, user identity information is passed along to the thread which, in turn, performs user authentication.
Db2 Connect's connection pooling provides a significant performance improvement in such environments. Db2 Connect maintains open connections to the database in an available pool. When a client requests a connection, it can be provided from this pool of ready connections. Connection pooling significantly reduces the processing usage typically spent on opening and closing these connections.
Connection pooling is transparent to applications connecting to the host through Db2 Connect. When an application requests disconnection from the host, Db2 Connect drops the inbound connection with the application, but keeps the outbound connection to the host in a pool. When a new application requests a connection, the Db2 Connect uses one from the existing pool. Using the already-present connection reduces the overall connection time, as well as the high CPU connect cost on the host.
Db2 Connect agents can be in one two states: idle or active. An agent is active when it is executing work for an application. Once this work is completed the agent goes into an idle state awaiting further work from the same or a different application. All idle agents are kept together in what is known as the idle agent pool. You can configure the size of this pool using the num_poolagents configuration parameter. This parameter equals the maximum number of idle agents you want the system to maintain. Setting this parameter to zero is equivalent to turning off the connection pooling feature. The default for this configuration parameter is set to AUTOMATIC with a value of 100. By being set to AUTOMATIC, Db2 Connect automatically manages the number of idle agents in the idle agent pool.
Db2 Connect does not establish connections to the database before receiving its first client request. Alternatively, you can fill the pool of idle agents before any clients make a request. The pool can be filled on startup using the num_initagents configuration parameter. This parameter determines how many idle agents should be created at start up time. These idle agents initially will not have connections to the host database server.
When a client requests a connection to the host, Db2 Connect will attempt to get an agent from among those in the pool that have a connection to the host database server. If that fails, it will try to find an available agent in the idle pool. If the pool is empty, Db2 Connect will create a new agent.
You can control the maximum number of agents that can be concurrently active using the max_coordagents configuration parameter. Once this number is exceeded, new connections will fail with error sqlcode SQL1226. (This code means that the maximum number of concurrent outbound connections has been exceeded.) The default for this configuration parameter is set to AUTOMATIC with a value of 200. By being set to AUTOMATIC, Db2 Connect automatically manages the number of coordinator agents.
The Db2 registry variable DB2CONNECT_IN_APP_PROCESS allows applications running on the same machine as a Db2 Connect Server product to either have Db2 Connect run within the applications process, default behavior, or to have the application connect to the Db2 Connect Server product and then have the host connection run within an agent. For an application to use connection pooling the connections to the host must be made from within the Db2 Connect Server product agents and thus DB2CONNECT_IN_APP_PROCESS must be set to NO.
Db2 Connect Connection Pooling versus Application Server Connection Pooling
Connection pooling is a must for any web technologies based application that is to support large volumes of transactions. Most web application servers now provide their own way of pooling database connections. For example, both Microsoft MTS (COM+) and IBM® WebSphere® provide connection pooling.
Application pooling mechanisms implemented by these servers differ significantly from what is provided by the Db2 Connect servers. Since 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, and so on.
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.
Which type of connection pooling is the right one to use? Both. Generally, using both Db2 Connect connection pooling and 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.