Heirloom Framework Connection Pooling Configuration Guide – Heirloom Computing

Overview

This guide describes how to configure and use JDBC connection pooling for applications built on the Heirloom Computing framework running on Apache Tomcat. Connection pooling provides better performance, resource management, and scalability compared to direct JDBC connections.

Benefits of Connection Pooling

Performance: Reuses existing database connections instead of creating new ones
Resource Management: Limits the number of concurrent database connections
Scalability: Handles multiple concurrent users efficiently
Connection Validation: Automatically tests and removes stale connections
Transaction Management: Ensures proper transaction boundaries with autocommit control

Prerequisites

Apache Tomcat 8.5 or higher
JDBC driver for your database in <tomcat>/lib directory
Heirloom application deployed to <tomcat>/webapps

Configuration Steps

Step 1: Configure Connection Pools in Tomcat

Edit <tomcat>/conf/context.xml to define your connection pools:

<Context>
    <!-- Existing configuration... -->
    
    <!-- Main Application Database Connection Pool -->
    <Resource name="jdbc/VitecDB"
              auth="Container"
              type="javax.sql.DataSource"
              factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
              driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
              url="jdbc:sqlserver://server:port;databaseName=dbname;applicationName=HEIRLOOM-ONLINE;trustServerCertificate=true"
              username="db_username"
              password="db_password"
              initialSize="50"
              maxActive="100"
              maxIdle="21"
              minIdle="13"
              maxWait="10000"
              timeBetweenEvictionRunsMillis="34000"
              minEvictableIdleTimeMillis="55000"
              validationQuery="SELECT 1"
              validationInterval="34000"
              testOnBorrow="true"
              removeAbandoned="true"
              removeAbandonedTimeout="233"
              defaultAutoCommit="false"
              jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
                               org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"/>
    
    <!-- DCB Database Connection Pool (if using Table DCB) -->
    <Resource name="jdbc/DcbDB"
              auth="Container"
              type="javax.sql.DataSource"
              factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
              driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
              url="jdbc:sqlserver://server:port;databaseName=dbname;applicationName=HEIRLOOM-DCB;trustServerCertificate=true"
              username="db_username"
              password="db_password"
              initialSize="10"
              maxActive="50"
              maxIdle="15"
              minIdle="5"
              maxWait="10000"
              timeBetweenEvictionRunsMillis="34000"
              minEvictableIdleTimeMillis="55000"
              validationQuery="SELECT 1"
              validationInterval="34000"
              testOnBorrow="true"
              removeAbandoned="true"
              removeAbandonedTimeout="233"
              defaultAutoCommit="false"
              jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
                               org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"/>
</Context>

Important Connection Pool Parameters:

Parameter	Description	Recommended Value
`initialSize`	Initial number of connections	10-50 depending on load
`maxActive`	Maximum active connections	50-100 for production
`maxIdle`	Maximum idle connections	20-30% of maxActive
`minIdle`	Minimum idle connections	10-20% of maxActive
`maxWait`	Max wait time for connection (ms)	10000
`validationQuery`	Query to test connections	"SELECT 1" for SQL Server
`testOnBorrow`	Test connection before use	true
`defaultAutoCommit`	CRITICAL for Heirloom	false
`removeAbandoned`	Remove leaked connections	true
`removeAbandonedTimeout`	Timeout for abandoned connections (seconds)	233

Step 2: Configure deploy.properties

Edit <tomcat>/webapps/<app>/WEB-INF/classes/deploy.properties:

For Main Application Database:

Replace direct JDBC configuration:

# OLD - Direct JDBC Connection
sql.file.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
sql.file.url=jdbc:sqlserver://server:port;databaseName=dbname
sql.file.user=username
sql.file.password=password

With JNDI datasource configuration:

# NEW - Connection Pool via JNDI
sql.file.autocommit=false
sql.file.datasource=jdbc/VitecDB
sql.file.isolation=committed
sql.file.readonly=false

# Also configure for sql.sqldb if used
sql.sqldb.autocommit=false
sql.sqldb.datasource=jdbc/VitecDB
sql.sqldb.isolation=committed
sql.sqldb.readonly=false

# Default SQL configuration
sql.default.autocommit=false
sql.default.datasource=jdbc/VitecDB
sql.default.isolation=committed
sql.default.readonly=false

For DCB Database (if using Table DCB):

Replace direct JDBC configuration:

# OLD - Direct JDBC Connection
dcb.db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
dcb.db.url=jdbc:sqlserver://server:port;databaseName=dbname
dcb.db.user=username
dcb.db.pass=password
dcb.db.table=_prod
dcb.mode.db=true

With JNDI datasource configuration:

# NEW - Connection Pool via JNDI
dcb.db.jndi=jdbc/DcbDB
dcb.db.table=_prod
dcb.mode.db=true

Step 3: Verify JDBC Driver Installation

Ensure the JDBC driver is in Tomcat's lib directory:

ls -la <tomcat>/lib/ | grep jdbc

For SQL Server, you should see something like:

mssql-jdbc-8.4.1.jre8.jar

Step 4: Restart Tomcat

After making these changes, restart Tomcat:

<tomcat>/bin/shutdown.sh
<tomcat>/bin/startup.sh

Database-Specific Configurations

SQL Server

driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://server:port;databaseName=dbname;trustServerCertificate=true"
validationQuery="SELECT 1"

PostgreSQL

driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://server:port/dbname"
validationQuery="SELECT 1"

Oracle

driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@server:port:sid"
validationQuery="SELECT 1 FROM DUAL"

DB2

driverClassName="com.ibm.db2.jcc.DB2Driver"
url="jdbc:db2://server:port/dbname"
validationQuery="SELECT 1 FROM SYSIBM.SYSDUMMY1"

Critical Considerations for Heirloom Applications

1. AutoCommit Must Be Disabled

CRITICAL: Always set defaultAutoCommit="false" in the connection pool configuration. Heirloom applications manage their own transaction boundaries and expect autocommit to be disabled.

2. JDBC Interceptors

The ConnectionState interceptor is essential for ensuring connections are properly reset when returned to the pool, particularly for the autocommit flag.

3. Connection Pool Sizing

Start with conservative values (initialSize=10, maxActive=50)
Monitor actual usage and adjust based on load
For high-transaction environments, increase to initialSize=50, maxActive=100

4. Validation and Eviction

Always enable connection validation (testOnBorrow="true")
Configure eviction to remove stale connections
Use appropriate validation queries for your database

Troubleshooting

Common Issues and Solutions

Connection Pool Exhaustion
- Symptom: "Cannot get a connection, pool error timeout"
- Solution: Increase maxActive or check for connection leaks
Transaction Rollback Issues
- Symptom: Unexpected transaction behavior
- Solution: Verify defaultAutoCommit="false" is set
Stale Connections
- Symptom: "Connection closed" errors
- Solution: Enable testOnBorrow="true" and set appropriate validationQuery
JNDI Name Not Found
- Symptom: "Name jdbc/VitecDB is not bound in this Context"
- Solution: Verify Resource name in context.xml matches datasource in deploy.properties

Monitoring Connection Pool

Monitor pool statistics through Tomcat's JMX interface or logs:

Active connections
Idle connections
Wait count
Connection creation/destruction rate

Performance Tuning

For Standard Applications

initialSize="10"
maxActive="50"
maxIdle="15"
minIdle="5"

For High-Load Applications

initialSize="50"
maxActive="100"
maxIdle="30"
minIdle="15"

For Batch Processing

initialSize="20"
maxActive="75"
maxIdle="25"
minIdle="10"

Migration Checklist

When migrating from direct JDBC to connection pooling:

Install JDBC driver in <tomcat>/lib
Configure Resource in context.xml
Set defaultAutoCommit="false"
Add JDBC interceptors
Update deploy.properties to use datasource
Remove direct JDBC configuration (comment out for reference)
Add sql.default configuration
Test connection with simple query
Verify transaction behavior
Monitor pool metrics after deployment

References

Support

For Heirloom-specific issues:

Visit: https://support.heirloom.cc
Check the Heirloom Computing knowledge base for platform-specific guidance

For Tomcat connection pool issues:

Review Tomcat logs in <tomcat>/logs/catalina.out
Enable connection pool logging in logging.properties