50 DB2 Nuggets #5 : Tech Tip - Automatic Client reroute in a JDBC application

Body

Looking for a way to test Automatic Client Re-route using a simple JDBC program that uses IBM data server driver for JDBC & SQLJ (JCC) & JDBC DriverManager Connection?

Here are the steps to follow:

Step 1:  UPDATE ALTERNATE SERVER INFORMATION in BOTH PRIMARY & STANDBY SERVERS:

Lets assume that following are the Primary and Standby Server's hostnames and Port numbers.

Primary: test1.ibm.com:50000
Secondary: test2.ibm.com:50000
Database: SAMPLE on both systems.

a) On DB2 Primary system:
db2 "update alternate server for sample using hostname test2.ibm.com port 50000"

Check the configuration to confirm the alternate server information has been set.
db2 "list db directory"

Database 1 entry:

Database alias = SAMPLE
Database name = SAMPLE
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = -1
Alternate server hostname = test2.ibm.com
Alternate server port number = 50000

b) On DB2 Secondary system:
db2 "update alternate server for sample using hostname test1.ibm.comport 50000"

Check the configuration to confirm the alternate server information has been set.
db2 "list db directory"

Database 1 entry:

Database alias = SAMPLE
Database name = SAMPLE
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = -1
Alternate server hostname = test1.ibm.com
Alternate server port number = 50000

Step 2: UPDATE THE JDBC CONNECTION URL WITH THE clientRerouteAlternateServerName and clientRerouteAlternatePortNumber PROPERTIES:

Sample program to test the automatic Client re-route:

import java.sql.*;
import java.io.*;
public class Jcc_reroute{
public static void main(String[] args)
{
try
{
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
String url = "jdbc:db2://test1.ibm.com:50000/sample"+ ":clientRerouteAlternateServerName=test2.ibm.com;clientRerouteAlternatePortNumber=50000;";
Connection con = DriverManager.getConnection(url,"userID","PWD");
System.out.println("Connected");
con.close();
  }  catch (Exception ex) {
      System.out.println("Exception: " + ex);
        ex.printStackTrace();
   }
  }
}

To compile the program:

javac Jcc_reroute.java

To run:

java Jcc_reroute

Step 3:  Once you run the above program, it connects to the primary. On the DB2 server, do a failover to the standby. Now the application will automatically re-route the connection to the former standby.

Refer: Java client re-route