Database connections

Databases contain business data that is written and accessed by deployed message flows. You must create connections from the integration node to the database by using ODBC or JDBC.

A database connection is a configuration file where you specify a database's physical details such as database type and version, and parameters to enable a JDBC connection from IBM® Integration Toolkit to the database.

ODBC connections to databases are managed internally by the integration node, and therefore any configurable connection pooling options that are available on the ODBC driver should not be used.

The integration node requires a database connection for each data source name (DSN) that is referenced in the message flow, even if different DSNs resolve to the same physical database. If the message flow is operating in coordinated mode, then a separate XA connection to each DSN participating in the globally coordinated flow is also required.

The number of connections to a database that an integration node requires depends on the actions of the message flows that access the database. For each message flow thread, an integration node that accesses a database makes one connection for each data source name (DSN). If a different node on the same thread uses the same DSN, the same connection is used, unless a different transaction mode is used, in which case another connection is required. For further information about transactions, see Database connections for coordinated message flows.

Normally, the integration node makes the connections when it needs to use them in the message flow. However, in the case of connections to ESQL-based data sources, you can make the initial connection before a flow receives a message, and so remove any connection latency from the message processing. Set this option by selecting Connect before flow starts property on the data source node.

If the message flow contains a DatabaseInput node, at least one database connection remains open while the message flow is running.

Linux platform Windows platform UNIX platform On Linux®, UNIX, and Windows systems, database connections are released under the following circumstances:

Database connections that have been started with the Connect before flow starts option selected are not released when the message flow becomes idle. These types of connections are only released in the following circumstances:

An error occurs, while accessing the database, that requires a new connection to be made.
The message flow is stopped.
The integration node is stopped.

All other database connections remain open until one of the following events occurs:

An error occurs, while accessing the database, that requires a new connection to be made.
The message flow has been idle for 1 minute.
The message flow is stopped.
The integration node is stopped.

z/OS platform On z/OS®, database connections are released if the database has not been accessed for 1 minute.

The following values for database connections can be altered by using the mqsichangeproperties command:

expireXAConnections
Database connections that are used as part of a globally coordinated (XA) transaction are not released by default. To alter this behavior, set expireXAConnections to true. Globally coordinated database connections are then released when they are idle for longer than the maxConnectionAge setting. This action involves disconnecting from and reconnecting to the default WebSphere® MQ queue manager.

Database connections that are used in XA transactions are released by default when they become idle for longer than the maxConnectionAge setting.
maxConnectionAge
The length of time in seconds that an idle database connection is kept open before being disconnected. This setting does not apply to database connections that started when the Connect before flow starts option was selected. The default is 60 seconds. If the value is set to -1, database connections are not released until the integration server or integration node is stopped.
maxStatementAge
The length of time in seconds that previously executed database queries are cached before removal. The default is 600 seconds and starts from the time when the query was last executed. Executing a query again resets its timer.
statementCacheSize
The maximum number of previously executed database queries that can be cached. The default cache size is 40 unique queries. When the statement cache becomes full, queries are purged from it starting with those queries that were executed the longest time ago.

To change the time after which a database connection for an idle message flow is released on a single integration server, use the following command:

mqsichangeproperties integration_node -e integration_server -o ComIbmDatabaseConnectionManager -n maxConnectionAge -v newValue

or the following command, to change the default time for all integration servers:

mqsichangeproperties integration_node -o ComIbmDatabaseConnectionManager -n maxConnectionAge -v newValue

where maxConnectionAge is specified in seconds.

If you are using IBM DB2® for your database, the default action is to limit the number of concurrent connections to a database to the value of the maxappls configuration parameter. The default for maxappls is 40. If you believe that the connections that the integration node might require exceeds the value for maxappls, increase this parameter and the associated maxagents parameter to new values based on your calculations.

For z/OS, the number of connections does not change when you use ODBC CAF (Call Attachment Facility) connections or RRSAF (Recoverable Resource Services Attachment Facility). For more information on the number of connections required, see You do not know how many database connections an integration node requires.

Use the maxConnectionUseCount configuration parameter to release an ODBC connection to the database after a specified number of transactions on a thread is reached. To activate this function, set maxConnectionUseCount to a value that is greater than zero.

maxConnectionUseCount is monitored on a per thread basis. For example, if you have additional instances of a message flow, meaning that you have multiple threads running in parallel, the connection to the database is released only on those threads that reach the value specified in maxConnectionUseCount.

Set the value of maxConnectionUseCount by using the mqsichangeproperties command. For example, to reset the ODBC connection after every 100 transactions, including commit and rollback processes, for each datasource connection on a thread, issue the following command:

mqsichangeproperties integration_node -e integration_server -o ComIbmDatabaseConnectionManager -n maxConnectionUseCount -v 100

Use the mqsireportproperties command to discover the current number of completed transactions on each datasource connection. This value is reported in the transactionCount field. For example:

mqsireportproperties integration_node -e integration_server -o ComIbmDatabaseConnectionManager -r


ComIbmDatabaseConnectionManager
  uuid='ComIbmDatabaseConnectionManager'
  userTraceLevel='none'
  traceLevel='none'
  userTraceFilter='none'
  traceFilter='debugTrace'
  statementCacheSize='40'
  maxStatementAge='600'
  maxConnectionUseCount='100'
  maxConnectionAge='60'
  useDefaultSchemaForStoredProcedures='true'
  enableODBCTraceBridge='true'
  expireXAConnections='false'
DatabaseContext
  threadNumber='3819'
  ODBC_Version_3
    envHandle='139961038995136'
DatabaseContext
  threadNumber='3824'
  ODBC_Version_3
    envHandle='139961240340928'
    DataSource
      name='TESTDB1'
      timeoutEnabled='true'
      coordinated='false'
      state='2'
      transactionCount='2'
      connectionAge='33'
      ManagedStatement
        text='INSERT INTO TEST VALUES (?)'
        createTime='2020-07-14 07:30:23.280630 (1594711823)'
        lastExecuteTime='2020-07-14 07:30:23.282599 (1594711823)'
        executeCount='1'
    DataSource
      name='TESTDB2'
      timeoutEnabled='true'
      coordinated='false'
      state='2'
      transactionCount='2'
      connectionAge='33'
      ManagedStatement
        text='INSERT INTO TEST VALUES (?)'
        createTime='2020-07-14 07:30:23.369659 (1594711823)'
        lastExecuteTime='2020-07-14 07:30:23.372099 (1594711823)'
        executeCount='1'

When the release function that is activated when maxConnectionUseCount is set to a value greater than zero, connection release is prompted when whichever of the values in maxConnectionAge or maxConnectionUseCount is reached first. To serve as an example, consider a scenario where maxConnectionUseCount is set to 100, and maxConnectionAge is set to 60. In such a scenario:

If 100 transactions are processed within 30 seconds of the creation of an ODBC connection, the ODBC connection is released immediately after the number of transactions reaches 100. A new ODBC connection will be established on the next message.
If 10, say, transactions are processed within 30 seconds of the creation of an ODBC connection, and for next 60 seconds, or more, no messages are processed on this ODBC connection, the value specified in maxConnectionAge is reached and the connection is released.
If 10, say, transactions are processed every 30 seconds, and the connection is never idle for more than 60 seconds, the ODBC connection is released after the total transaction count reaches 100, which, in this example, is after 300 seconds.

Note: The connectionAge field that is returned as part of the output from the mqsireportproperties command is not a configurable property; it is a runtime metric that indicates the age of the connection. There is no relationship between connectionAge and maxConnectionAge. The value in connectionAge can exceed the value in maxConnectionAge as seen in the example scenario description above where connectionAge would be 300 and maxConnectionAge is 60.

If you are using another database, check the database documentation for information about connections and the limits or restrictions that might apply.

When a message flow is idle, the integration server periodically releases database connections that have not been started with the Connect before flow starts option. Therefore, connections held by the integration node reflect its current use of these resources. This situation allows the integration node to respond when a database quiesces, if the database manager supports quiescing. Not all databases support the quiesce function, and not all databases quiesce in the same way. Check your database documentation for information about database quiescing.