In a WAS Liberty connection pool, can I validate connections on borrow?

Question

We are currently migrating an applications to run on a Liberty server (8.5.5.9). We have found that connections between the app server and the database are occasionally terminated by the firewall, for being idle for an extended period of time. When this happens, on the next HTTP request, the application will receive one of these broken connections.

Previously, we had been using Apache Commons DBCP to manage the connection pool. One of the configuration parameters in a DBCP conneciton pool is to "testOnBorrow", which prevents the application from being handed one of these bad connections.

Is there such a configuration parameter in a Liberty-managed datasource?

So far, we have configured our datasource like this:

    <dataSource jndiName="jdbc/ora" type="javax.sql.DataSource">
        <properties.oracle 
          user="example" password="{xor}AbCdEfGh123=" 
          URL="jdbc:oracle:thin:@example.com:1521:mydb"
        />
        <connectionManager 
          minPoolSize="3" maxPoolSize="10" maxIdleTime="10m"
          purgePolicy="ValidateAllConnections"
        />
        <jdbcDriver id="oracle-driver" libraryRef="oracle-libs"/>
    </dataSource>

The purgePolicy currently is set to validate all connections if one bad one is found (e.g., overnight when all connection have been idle for a long time). But all this does is prevent multiple bad connection from being sequentially handed to the applications.

One option in the connectionManager would be to set an agedTimout="20m" to automatically remove connections that are old enough to have already been terminated by the firewall. However, this would also terminate connections that have been recently used (which prevents the firewall from breaking them).

Am I missing something obvious here? Thanks!

Answer 1

In this scenario I would reccommend using the maxIdleTime, which you are already using, but reduce your minPoolSize to 0 (or remove it, since the default value is 0).

Per the maxIdleTime doc:

maxIdleTime: Amount of time after which an unused or idle connection can be discarded during pool maintenance, if doing so does not reduce the pool below the minimum size.

Since you have your minPoolSize=3, the pool maintenence won't kick in if there are only 3 bad connections in the pool for example, because the maintenance thread won't won't take the pool size below the minimum according the the doc. So setting the minPoolSize=0 should allow the maxIdleTime to clean up all of the bad connections like you would expect in this scenario.

So here is the final configuration that I would suggest for you:

<dataSource jndiName="jdbc/ora" type="javax.sql.DataSource">
    <properties.oracle user="example" password="{xor}AbCdEfGh123=" 
                       URL="jdbc:oracle:thin:@example.com:1521:mydb"/>
    <connectionManager maxPoolSize="10" maxIdleTime="18m"/>
    <jdbcDriver id="oracle-driver" libraryRef="oracle-libs"/>
</dataSource>

The value of maxIdleTime assumes that your firewall kills the connections after 20 mins, and to trigger cleanup after 18 mins in order to give the cleanup thread a 2 minute window to clean up the soon-to-be-bad connections.