Jump to content
We have recently updated our Privacy Statement, available here ×
  • 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:

    1*uGcZqTH5uPNwSyejpTI1Fw.png
     

    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.

    1*8TOxDG8IJgzM0gBmDBGapw.png

    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:

    1*D2Gz-ryehYWLnjdDlei5ww.png

    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:

    1*YbILTdyyzodagWGwutceCg.png

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

    1*1nhI1D00HS6rFDgT_Kbm-g.png

    User Feedback

    Recommended Comments

    There are no comments to display.



    Create an account or sign in to comment

    You need to be a member in order to leave a comment

    Create an account

    Sign up for a new account in our community. It's easy!

    Register a new account

    Sign in

    Already have an account? Sign in here.

    Sign In Now

×
×
  • Create New...