When using the SQLDB HA Premium Bluemix Service via JDBC, how can I reduce the connection and read timeouts?

Question

The SQLDB Bluemix Service's Premium Plan enables DB2 High Availability. However, the default JDBC URL provided by service credentials uses more forigiving timeouts than I would like to use. Here is an example of the default JDBC URL:

jdbc:db2://1.1.1.1:50001/DBName:clientRerouteAlternateServerName=2.2.2.2;clientRerouteAlternatePortNumber=50001;sslConnection=true;

From various experiments, it looks like the default timeouts are:

• initial connection timeout: 2 minutes
• socket read timeout: whatever is the default for the operating system. I've read that it can be more than an hour.

In a failure scenario where the primary server is suddenly unavailable, existing connections will remain "open" until the socket read timeout is reached. This can make it appear that requests on that connection have "hung". Additionally, any attempts to make a new connection will take 2 minutes or more because of the default connection timeout of 2 minutes.

I would like to use smaller timeouts to ensure a higher degree of application availability. I tried tweaking various JDBC URL parameters myself, but I managed to break failover a few times. So I though it was time to ask the experts:

What DB2 JDBC timeout-related parameters should I set or change in order to control the initial connection timeout and the socket read timeouts (and without breaking failover to the secondary server)?

Answer 1

There are a couple important properties that you'll want to set:

• loginTimeout sets the timeout for opening a socket to each server for new JDBC Connection objects.
• blockingReadConnectionTimeout sets the timeout for reading from a socket after the initial connection is established, when executing requests.
• commandTimeout limits the maximum amount of time for which any SQL request can execute.

So, your JDBC URL will look something like this:

jdbc:db2://1.1.1.1:50001/DBName:clientRerouteAlternateServerName=2.2.2.2;clientRerouteAlternatePortNumber=50001;sslConnection=true;loginTimeout=10;blockingReadConnectionTimeout=10;commandTimeout=30;

The first two properties are important for situations in which you have no network connectivity to a server or if you lose network connectivity to the server in the middle of a request, respectively.

It's good practice to set the commandTimeout, but doing so also covers you in an edge case: if you lose connectivity to a server in the middle of a request, after the blockingReadConnectionTimeout is reached, the connection will attempt to open a new socket connection to the server. Unfortunately, there is no property to set the timeout for opening the new socket^[1], so the default OS timeout is used (usually about 2 minutes in Linux) unless you have the commandTimeout set. In which case, it will use the remaining time as the timeout for opening the new socket.

If you really want do get your hands dirty, you can observe all of this behavior and when various timeouts are used by enabling trace logging in the driver and simulating network failures (blocking network traffic) at different points while using the driver.

Documentation for all of the JDBC properties can be found here.

[1] This is mostly due to the fact that at this point during the failure, the driver's Connection object is not closed.

Answer 2

See this link

https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.1.0/com.ibm.db2.udb.apdv.java.doc/doc/rjvdsprp.htm

It has all the properties description and helps you to understand

blockingReadConnectionTimeout The amount of time in seconds before a connection socket read times out. This property applies only to IBM DB2 Driver for JDBC and SQLJ type 4 connectivity, and affects all requests that are sent to the database server after a connection is successfully established. The default is 0. A value of 0 means that there is no timeout.