BW6.X - BWCE - Useful things to know about JDBC activities in BusinessWorks and BusinessWorks Container Edition

In BusinessWorks JDBC connections are managed in a pool. By default, JDBC connections are managed using the approach described below.

Default behavior

When a JDBC activity needs a connection to send an SQL statement the BusinessWorks engine will first check if there is an available connection in the pool, if this is the case the connection is tested by sending a test SQL statement to the target database and if the test is successful, the connection is assigned to the activity. It is my understanding, but I am not 100 % sure, that if a connection appears to be inactive, BusinessWorks automatically tries with another connection from the pool.

In the case there would be no connection available from the pool, and the number of Maximum Connections defined in the JDBC Connection shared resource won’t be reached BusinessWorks will try to open a new connection.

In the case the Maximum Connections would be reached the activity has to wait until a connection from the pool is available. If no connection becomes available before the expiry of the activity Time out, the activity execution ends with a timeout error.

Note also that JDBC connections from the pool are closed by BusinessWorks if they are not used within the duration defined by the ‘Connection Idle Timeout’ property in the JDBC Connection shared resource.

The above explanations are summarized in the diagram below, note that this is indicative and may not be 100 % accurate:

Overall this is beneficial to manage JDBC connections in a pool but always testing the selected JDBC connection before executing a JDBC activity has a negative impact on performances.

JDBC performances improvements

This is why since BusinessWorks 6.8 and BusinessWorks Container Edition 2.7, two properties have been introduced to change this behavior.

bw.application.jdbc.validateConnection : with this property set to false, available connections from the pool are assigned to activities without being tested. Instead of testing a connection everytime it is used, connections from the pool are tested periodically.

bw.application.jdbc.validateConnection.frequency : this property defines the frequency of the test of the pool connections. The value is in seconds and should not be above the Connection Idle Timeout property in the JDBC Connection shared resource.

Example:

bw.application.jdbc.validateConnection=false
bw.application.jdbc.validateConnection.frequency=50

Those properties can be set in an appnode config.ini file.

When using those properties, it is important to include the JDBC activities in a retry mechanism, possibly using the approach described in this other article:
https://emarchiset.medium.com/how-to-implement-a-retry-mechanism-in-businessworks-6-x-and-businessworks-container-edition-564b7b33ebc6

The above explanations are summarized in the diagram below, note that this is indicative and may not be 100 % accurate:

Summary

For applications sending JDBC queries at a relatively high frequency, say more than a few queries per minute, it is recommended to use the properties described above to make sure application performances are optimal. In such case this is important to implement a retry mechanism for JDBC activities in the application logic.

Reference elements

Extract from BusinessWorks 6.8 release notes:

The default JDBC Connection shared resource configuration (note that the Connection Idle Timeout property value is in seconds):