Maintain Connection Pools
Connection pooling is a framework to cache database connection information that is used by the Data Integration Service. Connection pools increase performance through the reuse of cached connection information.
A connection pool is a group of connection instances for one connection object. A connection instance is a representation of a physical connection to a data source. A connection pool library can contain multiple connection pools. The number of connection pools depends on the number of unique connections that the DTM instances use while running jobs.
You configure the Data Integration Service to run DTM instances in the Data Integration Service process or in separate DTM processes that run on local or remote nodes. Each Data Integration Service process or DTM process maintains its own connection pool library that all DTM instances running in the process can use. The number of connection pool libraries depends on the number of running Data Integration Service processes or DTM processes.
A connection instance can be active or idle. An active connection instance is a connection instance that a DTM instance is using to connect to a database. A DTM process or the Data Integration Service process can create an unlimited number of active connection instances.
An idle connection instance is a connection instance in a connection pool that is not in use. A connection pool retains idle connection instances based on the pooling properties that you configure for a database connection. You configure the minimum connections, the maximum connections, and the maximum idle connection time.
Connection Pool Management
When a DTM process or the Data Integration Service process runs a job, it requests a connection instance from the pool. If an idle connection instance exists, the connection pool releases it to the DTM process or the Data Integration Service process. If the connection pool does not have an idle connection instance, the DTM process or the Data Integration Service process creates an active connection instance.
When the DTM process or the Data Integration Service process completes the job, it releases the active connection instance to the pool as an idle connection instance. If the connection pool contains the maximum number of idle connection instances, the process drops the active connection instance instead of releasing it to the pool.
The DTM process or the Data Integration Service process drops an idle connection instance from the pool when the following conditions are true:
- •A connection instance reaches the maximum idle time.
- •The connection pool exceeds the minimum number of idle connections.
When you update the user name, password, or connection string for a database connection that has connection pooling enabled, the updates take effect immediately. Subsequent connection requests use the updated information. Also, the connection pool library drops all idle connections and restarts the connection pool. It does not return any connection instances that are active at the time of the restart to the connection pool when complete.
If you update any other database connection property, you must restart the Data Integration Service to apply the updates.
Pooling Properties in Connection Objects
You can edit connection pooling properties in the Pooling view for a database connection.
The number of connection pool libraries depends on the number of running Data Integration Service processes or DTM processes. Each Data Integration Service process or DTM process maintains its own connection pool library. The values of the pooling properties are for each connection pool library.
For example, if you set maximum connections to 15, then each connection pool library can have a maximum of 15 idle connections in the pool. If the Data Integration Service runs jobs in separate local processes and three DTM processes are running, then you can have a maximum of 45 idle connection instances.
To decrease the total number of idle connection instances, set the minimum number of connections to 0 and decrease the maximum idle time for each database connection.
The following list describes database connection pooling properties that you can edit in the Pooling view for a database connection:
- Enable Connection Pooling
Enables connection pooling. When you enable connection pooling, each connection pool retains idle connection instances in memory. To delete the pools of idle connections, you must restart the Data Integration Service.
If connection pooling is disabled, the DTM process or the Data Integration Service process stops all pooling activity. The DTM process or the Data Integration Service process creates a connection instance each time it processes a job. It drops the instance when it finishes processing the job.
Default is enabled for DB2 for i5/OS, DB2 for z/OS, IBM DB2, Microsoft SQL Server, Oracle, and ODBC connections. Default is disabled for Adabas, IMS, Sequential, and VSAM connections.
- Minimum # of Connections
The minimum number of idle connection instances that a pool maintains for a database connection after the maximum idle time is met. Set this value to be equal to or less than the maximum number of idle connection instances. Default is 0.
- Maximum # of Connections
The maximum number of idle connection instances that a pool maintains for a database connection before the maximum idle time is met. Set this value to be more than the minimum number of idle connection instances. Default is 15.
- Maximum Idle Time
The number of seconds that a connection instance that exceeds the minimum number of connection instances can remain idle before the connection pool drops it. The connection pool ignores the idle time when the connection instance does not exceed the minimum number of idle connection instances. Default is 120.
Example of a Connection Pool
You want to use connection pools to optimize connection performance. You have configured the Data Integration Service to run jobs in separate local processes.
You configure the following pooling properties for a connection:
- •Connection Pooling: Enabled
- •Minimum Connections: 2
- •Maximum Connections: 4
- •Maximum Idle Time: 120 seconds
When a DTM process runs five jobs, it uses the following process to maintain the connection pool:
- 1. The DTM process receives a request to process five jobs at 11:00 a.m., and it creates five connection instances.
- 2. The DTM process completes processing at 11:30 a.m., and it releases four connections to the connection pool as idle connections.
- 3. It drops one connection because it exceeds the connection pool size.
- 4. At 11:32 a.m., the maximum idle time is met for the idle connections, and the DTM process drops two idle connections.
- 5. The DTM process maintains two idle connections because the minimum connection pool size is two.
Optimize Connection Performance
To optimize connection performance, configure connection pooling for the database connections. Each DTM process or the Data Integration Service process caches database connections for jobs and maintains a pool of connections that it can reuse.
The DTM process or the Data Integration Service process caches and releases the connections based on how you configure connection pooling properties for the connection. Reusing connections optimizes performance. It minimizes the amount of time and resources that the DTM process or the Data Integration Service process uses when it opens and closes multiple database connections.
To optimize connection performance, enable the Connection Pooling property in the database connection properties. Optionally, configure additional connection pooling properties.