Enhance JDBC Connection Health Monitoring to Validate Existing Pool Connections

Many customers use JDBC Adapter connections with connection pooling enabled and rely on Heartbeat monitoring and pub.art.connection:queryConnectionState for proactive detection of database connectivity issues.

Currently, queryConnectionState validates whether a new connection can be established using the configured JDBC credentials. It does not validate the actual physical connections already present in the JDBC pool. As a result, stale or broken pooled connections may remain undetected after database outages, maintenance windows, database failovers, network interruptions, or server decommissioning. 

This can create a situation where:

• JDBC connection status appears healthy.
• Heartbeat reports no issue.
• queryConnectionState returns hasError=false.
• Production services still fail because a stale pooled connection is assigned to the request.

Proposed Enhancement

Introduce an enhanced connection validation capability for JDBC Adapter connections:

Option 1: Enhanced queryConnectionState

Extend the existing service with an optional parameter:

validationMode = connectivity | poolValidation

Where:

• connectivity = current behavior.
• poolValidation = validates pooled connections before reporting status.

Option 2: Pool Validation Mode

Add a configurable setting:

Validate Existing Pool Connections

When enabled, the adapter periodically validates all idle connections within the pool instead of only testing the ability to create a new connection.

Option 3: New Built-In Service

Introduce a service such as:

pub.art.connection:validatePoolConnections

Which:

• Validates all currently pooled JDBC connections.
• Returns counts of:
  • Healthy connections
  • Stale connections
  • Invalid connections
• Optionally removes invalid connections from the pool.

Option 4: Pool Refresh Service

Introduce a service such as:

pub.art.connection:refreshConnectionPool

Which:

• Detects stale connections.
• Invalidates affected pooled connections.
• Rebuilds the pool without requiring a disable/enable cycle.

Benefits

• Earlier detection of JDBC failures.
• Prevention of production incidents caused by stale pooled connections.
• Reduced requirement for custom "SELECT 1" monitoring services.
• Reduced operational overhead for customers running large environments with many Integration Servers and JDBC connections.
• More reliable monitoring after:
  • Database restarts
  • Failovers
  • Network interruptions
  • Planned or unplanned outages
  • Database decommissioning

Example Scenario

1. Database becomes unavailable.
2. Existing pooled JDBC connections become stale.
3. Database returns online.
4. queryConnectionState reports healthy because it opens a new connection.
5. Application receives a stale pooled connection and fails.

Expected behavior:

• Pool validation detects stale connections.
• Invalid pooled connections are removed automatically.
• Monitoring reports degraded state until the pool is healthy again.

Business Impact

In large production environments (50+ Integration Servers and hundreds of JDBC connections), implementing custom monitoring services that execute periodic SELECT 1 queries for every connection creates additional development, maintenance, and infrastructure overhead. A native capability within Integration Server would provide a scalable and consistent solution for proactive JDBC health monitoring.

Expected Outcome

Allow customers to proactively detect, report, and remediate stale JDBC pooled connections before application failures occur, without requiring custom SQL-based monitoring or manual connection restarts.