Database Connection Lifetime

By default, when MFDBFH establishes a database connection it will keep the connection open for the remainder of the process's execution. This is done to avoid the unnecessary performance overhead of establishing and disconnecting database connections. However, there may be times when the lifetime of database connections needs to be restricted. For example:

  • The SQL Server master, and PostgreSQL postgres database connections are only required when determining whether a database exists, or when creating a new database. These operations are generally performed the very first time that a cross-region, region, or datastore needs to be accessed by a process, but after that are not required.
  • A database server, such as PostgreSQL, may not efficiently handle more than a few hundred active connections at any one time. In such circumstances, it would be preferable to only establish database connections when they are actually needed, and then disconnect them once the associated database operations are complete (for example, the lifetime of a batch job).
  • When optimised I/O is enabled, MFDBFH will perform I/O operations for a file on a background thread. A separate database connection to the one used for database operations on the main execution thread will be used. Once the file is closed, the background thread is stopped, and the database connection is returned to an internal cache for later re-use. By default, MFDBFH will cache up to 10 connections. Instead of caching the connection, it may be preferable to close the connection instead to avoid unnecessary database server resources being tied up for a connection that may never get re-used.

Use the dbfhconfig -connopts command line utility to update the database configuration file to override the default behavior. The following table shows the entries that each option creates and an explanation of the altered behavior:

Attribute Description Example
connection.close.heartbeat="true|false" Specify whether or not a CAS process's heartbeat thread should return the cross-region database connection to the connection cache, or disconnect it after each heartbeat (every 5 seconds). Whether it is cached or disconnected is determined by the size of the connection cache that has been configured. When the connection has been disconnected, it will be re-connected the next time an operation on the database is required. Default 'false'. <dsn name="SS.MYFILES" type="datastore" dsname="MYFILES" connection.close.heartbeat="false"/>
connection.close.syncpoint="true|false" Specify whether or not the database connection should be returned to the connection cache, or disconnected after each CAS syncpoint. Whether it is cached or disconnected is determined by the size of the connection cache that has been configured. When the connection has been disconnected, it will be re-connected the next time an operation on the database is required. Default 'false'. <dsn name="SS.MYFILES" type="datastore" dsname="MYFILES" connection.close.syncpoint="false"/>
connection.close.task="true|false" Specify whether or not the database connection should be returned to the connection cache, or disconnected at the end of each CAS task (e.g. CICS transaction, or batch job). Whether it is cached or disconnected is determined by the size of the connection cache that has been configured. When the connection has been disconnected, it will be re-connected the next time an operation on the database is required. Default 'false'. <dsn name="SS.MYFILES" type="datastore" dsname="MYFILES" connection.close.task="false"/>
connection.max.cached=<n/> Specify the maximum number of connections that MFDBFH will keep in its connection cache for later reuse. Setting the value to 0 will ensure that no connections are cached and get immediately disconnected once they are no longer required. Default 10. <dsn name="SS.MYFILES" type="datastore" dsname="MYFILES" connection.max.cached=0/>
connection.max.dbopts=<n/> Specify the maximum number of database operations that MFDBFH is allowed to perform on the connection before the connection is returned to the connection cache or disconnected. Default INT_MAX (2147483647). <dsn name="SS.MYFILES" type="datastore" dsname="MYFILES" connection.max.dbopts=5000/>
Note: You can also apply these connection options at a program/process level, meaning different programs/processes for a single connection can have different connection behavior. Use the dbfhconfig -program:<prog-name> option to configure this; see The dbfhconfig Command Line Utility for more details.[1]

Example

Multiple attributes can be configured for a single <dsn> entry; for example, to ensure that the connection to a SQL Server master database is disconnected after checking for the existence of a database, or when creating a new database, the following connection lifetime attributes should be specified:

<dsn name="SS.MASTER" type="database" dbname="master" connection.max.cached=0 connection.max.dbopts=0/>