Enabling Automatic Client Reroute for Availability when using DB2onCloud(aka dashDB for Transactions) HA plan

Question

Currently DB2onCloud in IBM Bluemix has as HA plan, in order for apps to seamlessly connect to the database during planned maintenance or if there is failover, how can we do that at an application side?

Answer 1

For better security it's always recommended to use SSL when connecting to Db2 on Cloud databases. For example to connect via JDBC your connection string would look like this:

"jdbc:db2://<Db2OnCloudServer>:50001/BLUDB:sslConnection=true;user=bluadmin;password=<Password>;enableSeamlessFailover=true;"

You can get this string and credentials from your Db2 on Cloud service instance under "Service Credentials". If you don't already see the credentials there, click "new credentials" and they will appear. Another benefit of using SSL is also that if there is a failover and you are using the above connection string it will seamlessly reconnect as the server and client exchange alternate server information.

When you are not using SSL connections you will need to specify additional parameters for applications to reconnect because the alternate server information that is sent from the server is only for SSL connections. To do that you can use a connection string like the following:

"jdbc:db2://<DB2OnCloudServer>:50000/BLUDB:user=bluadmin;password=<Password>;enableClientAffinitiesList=1;maxRetriesForClientReroute=10;retryIntervalForClientReroute=5;clientRerouteAlternateServerName=<Db2OnCloudServer>,<Db2OnCloudServer>;clientRerouteAlternatePortNumber=50000,50000;enableSeamlessFailover=true;"

Note you will specify the same server as alternate servers in the string. This is because the IP for the servers will move when a failover happens so the connection is always done through the same IP. By specifying the clientRerouteAlternateServerName and clientRerouteAlternatePortNumber it will override the values returned from the server which would mean it would connect to the ssl port instead.

The above will take care of the actual connection to the database but your application will also need to have appropriate retry logic. Here is a rough sample of code which shows usage:

    import java.sql.*;
    
    public class JDBCSampleEx {
    
        public static void main(String args[]) {
    
            String connectionURL = "jdbc:db2://169.48.134.122:50000/BLUDB:user=bluadmin;password=MmM5OWQ3ZWUyZmNm;enableClientAffinitiesList=1;maxRetriesForClientReroute=10;retryIntervalForClientReroute=5;clientRerouteAlternateServerName=169.48.134.122,169.48.134.122;clientRerouteAlternatePortNumber=50000,50000;enableSeamlessFailover=true;";
            Connection con = null;
    
            try {
    
                // Load the JCC Driver class (db2jcc4.jar).
                Class.forName("com.ibm.db2.jcc.DB2Driver");

                //Create the connection using the static getConnection method
                con = DriverManager.getConnection(connectionURL);

                Statement stmt = con.createStatement();
                ResultSet rs = null;
                con.setAutoCommit(false);
                try {
                    rs = stmt.executeQuery("select FIRSTNME, SALARY from EMPLOYEE");
                    // Print results
                    while (rs.next()) {
                        System.out.println("Name= " + rs.getString("FIRSTNME") + " SALARY= " + rs.getString("SALARY"));
                    }
                    // do a random update
                    String sql = "update  EMPLOYEE set FIRSTNME = '" + RandomAlphaNum.gen(10) + "'";
                    stmt.executeUpdate(sql);
                    con.commit();
                } catch (java.sql.SQLException e) {
                    //Catch return code to do any retry
                    if (e.getErrorCode() == -30108 || e.getErrorCode() == -4498 || e.getErrorCode() == -4499) {
                        // Add any logic to reply the current in flight transaction
                        // if necessary
                        System.out.println("Replay any transactions if necessary");
                    } else {
                        throw e;
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                     if (con != null) {
                         con.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }