27-Dec-06 (Created: 27-Dec-06) | More in 'Howto'

How to use ConnectionPoolConnectionManager4

Why

This class implements a mechanism to preload a certain number of connections for each data source. The facility also allows to keep those many minimum connections open during the clean up process.

How to specify this class as the connection manager

#request.AppObjects.connectionManager.className=com.ai.db.ConnectionPoolConnectionManager1

request.AppObjects.connectionManager.className=com.ai.db.cp4.ConnectionPoolConnectionManager4
request.AppObjects.connectionManager.preload-datasources=reportsDB

request.AppObjects.scheduler.className=com.ai.scheduler.BasicScheduler
AppObjects.scheduler.timer_interval_in_milli_secs=20000

I have highlighted the difference from before. Notice a new parameter for the connection manager called preload-datasources. This field is a comma separated names of the data sources for which you would like to have the connections pre loaded.

Extended properties for the database definition

Database.akcReportsDB.jdbc_driver=sun.jdbc.odbc.JdbcOdbcDriver
Database.akcReportsDB.connection_string = jdbc:odbc:akc-reportsDB
Database.akcReportsDB.userid =
Database.akcReportsDB.password =

Database.akcReportsDB.expirationTimeInMin=2
Database.akcReportsDB.minimumNumberOfConnections=10

Notice the new properties that are high lighted for you. Nevertheless please notice that the database alias is important as below 

Database.alias.reportsDB=akcReportsDB

Notice that connection manager takes the alias name and not the real definition in its comma separated list.

You have to load the manager at initialization

Aspire.startup.initializers=SystemPropertiesInitializer,AppObjects.connectionManager

Notice how the connection manager request name (the left hand side where you have defined the connection manager object) is added to the list of system initializers.

Verifying that you have specified correctly

You will see entries like the following in the log file right after startup. As long as you are using connections with in this limit you also should not see any more of "Creating a new connection..." messages as the system runs. 

Creating the creator :com.ai.db.cp4.ConnectionPoolConnectionManager4
Trace:com.ai.db.cp4.ConnectionPoolConnectionManager4:the preload data source string is:reportsDB
Trace:com.ai.db.cp4.ConnectionPoolConnectionManager4:Going to preload for :[reportsDB]
Trace:com.ai.db.cp4.ConnectionPoolConnectionManager4:cp: Connection pool not found for data source :reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:expire after 120000 milliseconds
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:minimum number of connections:10
Creating the creator :com.ai.scheduler.BasicScheduler
sc: Scheduler started at :12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:Preloading for datasource:reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:Current pool size:0
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:Needed pool size:10
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creating a new connection to reportsDB
Trace:com.ai.db.CJDBCConnectionCreator:Registering jdbc driver: sun.jdbc.odbc.JdbcOdbcDriver
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creating a new connection to reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creating a new connection to reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creating a new connection to reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creating a new connection to reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creating a new connection to reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creating a new connection to reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creating a new connection to reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creating a new connection to reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creating a new connection to reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:The new pool size is:10
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:Completed Preloading for datasource:reportsDB

Isolating messages from this package "cp4"

You can use the following log pattern 

Logging.selectiveFilters=Error:,Trace:com.ai.db.cp4
Logging.excludeFilters=vectorMetadata:,dbrs:,

Notice how you can use the java package name to pick your messages to the granularity you want.

Tracking messages from The cleanup task

sc: timer tick detected : 12/22/06 5:32 PM
sc: timer tick detected : 12/22/06 5:33 PM
sc: timer tick detected : 12/22/06 5:34 PM
sc: timer tick detected : 12/22/06 5:35 PM
sc: timer tick detected : 12/22/06 5:36 PM
cp: Connection view before cleanup at: 12/22/06 5:36 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Name of the data source : 	reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Total number of connections: 	10
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Connections that are currently out : 	0
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Connections that are free :	10
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Individual connection details 
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:There are currently:10 connections
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:The minimum number of connections to be kept are:10 connections
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:The number of connections to be closed are:0 connections
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:There are no connections to cleanup.
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:There are connections to be cleaned up. 
walking through the collection to identify candidate connections to close.
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:No need to release any more as cursize is less than or equal to min size
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:Latest size of the pool is:10
cp: Connection view after cleanup: 12/22/06 5:36 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Name of the data source : 	reportsDB
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Total number of connections: 	10
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Connections that are currently out : 	0
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Connections that are free :	10
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Individual connection details 
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Creation time : /t12/22/06 5:31 PM
Trace:com.ai.db.cp4.SingleDataSourceConnectionPool:cp: Last checkout time : /t12/31/69 7:00 PM
sc: timer tick detected : 12/22/06 5:37 PM

when does a connection gets released

if the last used time exceeds the allowed amount then that connection is closed provided it is outside the range of minimum connections

Build Information

This facility is available starting at build 23.2