Connection Pooling

Introduction

Connection pooling and the use of a datasource is strongly recommended when moving an application into production. There are many performance benefits and it is a standard data access pattern. 

The key advantage is database connections are fairly expensive operations and as such, should be reduced to a minimum in every possible use case (in edge cases, just avoided).

There are many connection pool libraries available - for extremely high-performance requirements, we would recommend https://github.com/brettwooldridge/HikariCP but the standard connection pool builtin to Tomcat is typically good enough for most applications and is the one that we will configure here.

Tomcat configuration

1. Copy the JDBC jar file for the required database into <tomcat dir>/lib directory

2. Add the connection pool to the <tomcat dir>/conf/context.xml. The sample below is for MS SQL Server.

 <Resource
name="jdbc/TestDB"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
initialSize="50"
maxActive="100"
maxIdle="21"
minIdle="13"
timeBetweenEvictionRunsMillis="34000"
minEvictableIdleTimeMillis="55000"
validationQuery="SELECT 1"
validationInterval="34"
testOnBorrow="true"
removeAbandoned="true"
removeAbandonedTimeout="233"
username="username"
password="password"
defaultTransactionIsolation="committed"
defaultAutoCommit="false"
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://10.10.192.24;databaseName=vitec_test"
/>

NOTE: Please change the user/password and URL to match your configuration

maxActive, initialSize, minIdle, maxIdle, etc., are all tuneable parameters and depend on your specific application load. Full details can be found here https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html

We also need to add jdbcInterceptors, these are used to ensure the connections reinitialize to the defaults, especially with respect to the autocommit flag.

3. Add the following to the deploy.properties file of your COBOL or PL/1 application, removing any other sql.* statements.

sql.default.autocommit=false
sql.default.isolation=committed
sql.default.datasource=jdbc/TestDB
sql.default.readonly=false


#add the dcb table entries
dcb.db.jndi=jdbc/TestDB
dcb.db.table=_prod
dcb.mode.db=true
#use this when running from etp
dcb.db.etp=true

NOTE: Ensure that the datasource name matches the one defined in the context.xml

Hikari Connection Pool

We can also use the Hikari connection pool, this has been benchmarked by many projects to be the fastest pool around.

The entries for this in context.xml for Hikari are as follows.

   <Resource name="jdbc/testdb" auth="Container"
      factory="com.zaxxer.hikari.HikariJNDIFactory"
      type="javax.sql.DataSource"
      minimumIdle="10"
      maximumPoolSize="20"
      connectionTimeout="300000"
      driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDrive"
      jdbcUrl="jdbc:sqlserver://localhost;databaseName=test"
      dataSource.implicitCachingEnabled="true"
      dataSource.user="xxx"
      dataSource.password="xxx"
      dataSource.autoCommit="false"
    />

We also need to copy the HikariCP jar file to <tomcat home>/lib directory. Hikari also depends on SL4J logging libraries so the api and simple implementation jar need to be copied into the same directory. For JDK 8 i have attached the required files as a zip.