When working with databases in IBM App Connect Enterprise (ACE), especially via ODBC, it’s crucial to understand how connection are made, reuse and how they are released behind the scenes. To make these concepts easier to understand, I’ll walk you through examples and include an activity diagram to illustrate the connection lifecycle clearly.

1. Connection Pooling

IBM explicitly advises against using manual connection pooling mechanisms for ODBC connections. Because brokers internally manage ODBC connection pooling, ensuring optimal performance and resource management without any need for external pooling logic. Long story short, do not use manual connection pooling on ODBC.

2. How Are ODBC Connections Made?

Each DSN (Data Source Name) in ACE maps to a single connection, regardless of the physical details of the underlying database. If multiple compute nodes on the same thread use the same DSN, they will reuse the same connection only if their transaction modes match.

For example:

  • If Compute Node A’s and Compute Node B’s Transaction Mode property is Yes, they will share the same database connection.
  • However, if Compute Node A’ has Transaction Mode = Yes and Compute Node B’s Transaction mode = No, broker will establish a new separate connection for Compute Node B’s DSN.

This behavior ensures transactional integrity and resource isolation when necessary.

Reducing Latency with “Connect Before Flow Starts”

By default, broker establishes a database connection only when needed during message flow execution. However, to reduce initial latency, you can configure broker to establish the connection before the flow starts. To enable this: checked the “Connect before flow starts” property on the Compute Node.

This can be particularly helpful in scenarios where minimizing the delay in message processing is critical.

3. How Are Connections Released?

The connection release behavior depends on how the connection was initiated:

If “Connect before flow starts” is checked:

Connections are not released during idle periods. Instead, they are released only when:

  • A database access error occurs that requires a new connection.
  • The message flow is stopped.
  • The integration node is stopped.
For all other connections:

Connections are released when:

  • A database access error requires a new connection.
  • The message flow has been idle for 1 minute (configurable).
  • The message flow is stopped.
  • The integration node is stopped.

4. Using maxConnectionUseCount & maxConnectionAge for Connection Reuse and Released Control

Broker provides the maxConnectionUseCount & maxConnectionAge configuration parameter to manage how often a connection is reused before being released.

  • When maxConnectionUseCount set to a value greater than zero, broker will release and renew the connection after the specified number of transactions on a thread.
  • This works in combination with maxConnectionAge, and the connection will be released whichever threshold is hit first.

To understand this let’s say: maxConnectionUseCount = 100 transactions and maxConnectionAge = 60 seconds

Here’s how connections are released:

  • High traffic: If 100 transactions happen within 30 seconds, the connection is released immediately after the 100th transaction—age doesn’t matter.
  • Idle connection: If only a few transactions occur and the connection is idle for 60+ seconds, it’s released based on age, even if use count is low.
  • Steady traffic: If 10 transactions happen every 30 seconds, the connection stays active until it reaches 100 transactions—released after 300 seconds. The maxConnectionAge says that a connection should be released if it remains idle for 60 seconds. However, in this case, the connection stays active for the full 300 seconds because it is never idle long enough to hit the 60-second threshold. Note: Activity diagram didn’t cover this scenario.

Refer to below detail activity diagram that shows how ODBC connections are released.

maxConnectionUseCount & maxConnectionAge Activity Diagram

5. Breakdown of the Activity Diagram

  1. Start
    The process begins with the creation of a new ODBC connection.
  2. Initialize Counters
    • useCount = 0 (how many transactions this connection has handled)
    • connectionAge = 0 (how long this connection has been alive)
  3. Process Transaction
    A transaction is handled using the current connection.
  4. Increment Counters
    • useCount is incremented by 1.
    • connectionAge is updated accordingly.
  5. Decision: Has maxConnectionUseCount Been Reached?
    • Yes → Release the connection and create new for other transaction.
    • No → Check the next condition.
  6. Decision: Has maxConnectionAge Been Reached?
    • Yes → Release the connection and create new.
    • No → More decisions needed.
  7. Decision: More Transactions Incoming?
    • Yes → Wait for the next transaction and go back to processing.
    • No → This means the thread is idle.
  8. Wait Time Increment & Update connectionAge
    The thread is idle, so the system:
    • Increments the wait time.
    • Updates the connectionAge.
  9. Check Again: Has maxConnectionAge Been Reached?
    • Yes → Release the connection.
    • No → Continue waiting for a new transaction or timeout.
  10. Final Check: Is the Connection Still Active?
    If yes, the cycle continues.

The maxConnectionUseCount parameter is monitored per thread. This means that if you have multiple instances of a message flow running in parallel (i.e., multiple threads), the database connection is released only on the threads that hit the specified threshold. See below.

maxConnectionUseCount Per Thread

Use below commands to set the desire connection age and usage count.

For maxConnectionUseCount : mqsichangeproperties integration_node -e integration_server -o ComIbmDatabaseConnectionManager -n maxConnectionUseCount -v 100
For maxConnectionAge : mqsichangeproperties integration_node -e integration_server -o ComIbmDatabaseConnectionManager -n maxConnectionAge -v 120

6. Conclusion

IBM App Connect Enterprise handles ODBC database connections intelligently, with built-in support for connection pooling, reuse, and automatic release. By configuring options like maxConnectionUseCount, maxConnectionAge, and “Connect before flow starts,” you can optimize performance and ensure stable, efficient database access across your integration flows. Need help setting up an ODBC DSN? This post walks you through the process.

https://www.ibm.com/docs/en/app-connect/12.0?topic=overview-database-connections