Improving Database Connection Pools

Although this isn't a Server Manager specific post the SM tool may provide insight into the database connection pooling behavior. The EnterpriseOne web based servers maintain a pool of connections to a database. When a database connection is required it will be obtained from the connection pool. When no longer used it will be returned to the pool.

The maximum size, initial size, growth increment, and other parameters are configured in the JDBJ Database Configuration -> JDBj Connection Pools configuration parameters. Refer to the Server Manager Guide for information about the particular settings.

For each effective database connection, or connection URL, a pool of connections is created upon first request. What is a connection URL? The actual logic varies based on database type but can be summarized as a combination of the connection information (server, port, SID, etc) appended by the name of the proxy database user. A good way to think of it is evaluating the actual connection details defined by an EnterpriseOne datasource. For example a typical Oracle database based installation will use the same SID for each datasource. Thus regardless of whether the datasource is 'System - 812' or 'Central Objects - PD812' it all boils down to the same database -- the SID. In this case the connection URL will be the concatenation of the SID with the name of the proxy user.

Other databases may use more complex naming conventions for the connection URL. For example SQL Server based datasources will look something like 'jdbc:sqlserver://DENDSQWN01:1433_JDE_DEVELOPMENT_true_JDE'. This URL includes the server name, listening port, physical database name, unicode enabled, and proxy user.

The active database connections may be viewed using Server Manager. Navigate to the HTML server of intesrest and select 'JDBj Connection Caches' from the runtime metrics. You will see all the connection pools that have been established.


In the screen shot above you can see I actually have two sets of 10 database connections to two different connection URLs. The thing is these are the same database differing only by case. This didn't seem like a good thing so I went through my database datasources (F98611) and found I used all upper case there. I then went through the configuration metrics in server manager for my JAS server and found both upper and lower case uses of the Oracle SID. Changing those all to uppercase and restarting my server resulted in a single pool to the database.