Connection Pooling

Connection Pooling Overview

The LSX provides a simple programmatic interface to connection pooling. The LCSession class exposes a property called "ConnectionPooling." Setting this property to "True" enables connection pooling.

A typical operation consists of three major parts (for details, see the subtopic, "Sequence of Events for Connection Pooling,").

1. Connecting to the data source
2. Identifying information of interest, retrieving and or updating the information
3. Disconnecting from the data source

The middle step is the processing step. When you work with large amounts of information or when you are performing complex operations, processing becomes the most significant time consideration. Many applications have a very short processing step. In these applications, a much greater percentage of total time is associated with connecting to and disconnecting from the external data source. The connect/disconnect time is amplified when it must occur for each processing request.

The LSX provides a connection pooling property that makes it possible to retain discarded connections for later use. The pooling functionality is controlled by the LCSession property, ConnectionPooling, which is a Boolean with a default value of FALSE. When this property is set to TRUE, subsequent requests for new connections are processed through the connection pool.

With connection pooling, creating a new connection is serviced first by checking the pool for an existing compatible connection and then if one is not available, creating a new connection. A compatible connection is determined by the external system and all of the system's required connectivity properties. This prevents a connection originally established for one user, later being used by another. As an example, a connection to DB2® which was originally created using John's user name and password, and then release to the connection pool would not be issued if a new connection was requested using Jane's user name and password.

A connection is removed from the pool when the "Connect" method occurs. A connection is returned to the pool when the "Disconnect" method occurs. If no explicit disconnect occurs, an automatic disconnect is performed when the object is deleted.

Keep in mind that a connection that is returned to the pool does not disconnect from the external system. Code that takes advantage of connection pooling must anticipate this behavior. The important issues to remember are related to what may happen automatically during a normal disconnect from an external system. For example, disconnecting from an RDBMS may trigger a commit of records inserted, updated, or deleted, since the connection was first established. Likewise, there may be rollback or other database operations that take place automatically as a result of disconnecting. When connection pooling is enabled, these events do not take place because the connection is not actually dropped. Therefore, if you expect and want these types of operations to take place, the processing portion of the script must explicitly perform them.

The life span of a pooled connection is dependent on the LSX. Within Notes® and Domino®, an LSX is loaded when the execution of the Uselsx statement occurs in a script. The LSX is not unloaded until the Notes® or Domino® process terminates. Once a connection is pooled, it remains available until the associated process terminates.

The connection pool defaults to a maximum of 20 pooled connections for a given external system. When the maximum number of connections have been created and are already in use, a request for an additional connection will be granted but the connection will not be pooled. The default may be overridden using settings in the notes.ini file.

The syntax for configuring the connection pools is a list of comma-delimited connector names, the pool size, and an optional data source maximum. The default pool size for all connectors is 20. The optional data source maximum value indicates the limit of allowed connections to a single database. This value cannot be greater than the total pool size for a given connector. For example, a DB2® pool size of 10 and a data source maximum of 5 indicates the pool will hold no more than 10 connections to DB2® and of the 10, no more than 5 will be to any one database. If you do not specify a data source maximum, it is the same as the pool size.

Here is an example of an INI entry:

EIConnectionPool=oracle,10,db2,20,5,psoft7,4

This example has pooling information for three connectors: Oracle, DB2®, and PeopleSoft 7.0. Respectively, they support connection pools of 10, 20, and 4 connections each. In addition, DB2® indicates that no more than 5 connections in the pool can be to a single database.

Sequence of Events in Connection Pooling

Programs which use the LC API follow a typical progression:

1. Allocate a connection
2. Set connectivity properties
3. Connect
4. Select data
5. Fetch data
6. Manipulate data
7. Store data
8. Disconnect
9. Free connection

Connection pooling primarily affects the first three steps during which the connection is established. While the presentation material contains material pertaining to each step, the diagram that follows details the connection pooling process.

One goal of connection pooling is to minimize any adverse impact on performance. For this reason, all code which utilizes a connection handle, must determine quickly if pooling is enabled and if a given connection handle refers to a traditional connection or a pooled connection.

The connection pool is subdivided based on connectors. There is a portion of the overall pool for each connector type used. The pool is configurable on a per connector basis. The default pool maintains 20 connections for any given connector. Connections are not established until requested by the users program. Additionally, new connections are not created unless no applicable connection is available in the pool. The pool may be configured to limit the total number of connections for an individual connector as well as limit the number of identical connections. A connection's individuality is based on its connectivity parameters or credentials. Connections are considered to be identical when they are for the same connector and all of the connectivity properties match. The matching criteria is also used to determine if a connection in the pool satisfies a user request for a connection.

When a connection is first allocated, the connection pool is checked to verify that pooling is enabled and the pool has as least one connection of the requested type: DB2®, Oracle, SAP, and so on.

• The pooling code creates a facsimile of a connection and returns its handle in place of a real connection handle. The purpose of this facsimile is to emulate a connection while the user assigns the connectivity properties. The properties must be kept separate from a real connection until the request to establish a connection to the external system.
• When connect request occurs, the properties held in the facsimile are used to locate a matching connection in the pool. Assuming one is found, it is returned. Otherwise, a new connection is created returned.
• If the pool is full, the newly created connection is not pooled. The LC API can quickly differentiate between pooled and non-pooled connection permitting them to be mixed freely. The user is unaware that a request for a pooled connection was actually served by an unpooled connection. The only evidence would be the absence of any performance gains associated with pooling.