0

We have a DB2 environment in a HADR cluster. The Java application connects to the database via JDBC. In the event of an error, this should connect to the backup database.

This connection takes about 50 seconds

How can I speed that up?

This is my code:

public class JDBCDB2Trial {

    private static String connectionURL = "jdbc:db2://X.XXX.db2.1:50000/db2";
    private static String fieldUserName = "Administrator";
    private static String fieldPassword = "XXXXX";
    private static String enableClientAffinitiesList ="1";
    private static String clientRerouteAlternateServerName ="X.XXX.db2.1,X.XXX.db2.2";
    private static String clientRerouteAlternatePortNumber ="50000,50000";
    private static String maxRetriesForClientReroute ="1";
    private static String retryIntervalForClientReroute ="0";
    private static String enableSeamlessFailover ="1";

    public static void main(String args[]) {

    try {
        Driver driver = (Driver) Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
        DriverManager.registerDriver(driver);
        Properties info = new Properties();
        info.put("user", fieldUserName);
        info.put("password", fieldPassword);
        info.put("enableClientAffinitiesList", enableClientAffinitiesList);
        info.put("clientRerouteAlternateServerName", clientRerouteAlternateServerName);
        info.put("clientRerouteAlternatePortNumber", clientRerouteAlternatePortNumber);
        info.put("maxRetriesForClientReroute", maxRetriesForClientReroute);
        info.put("retryIntervalForClientReroute", retryIntervalForClientReroute);
        info.put("enableSeamlessFailover", enableSeamlessFailover);
            
            
        System.out.println("Start connection Test ACR");
        final long timeStart = System.currentTimeMillis();  
        Connection theCon = DriverManager.getConnection(connectionURL, info);
        
                final long timeStop = System.currentTimeMillis(); 
        if(theCon != null){
           long time = (timeStop - timeStart)/1000;
           System.out.println("Connection successful! Needed time: " +time+"Seconds");
        }
Thomas
  • 1
  • You don't say anything about the general setup. What needs to be started on failover? How is the error detected? – data_henrik May 26 '23 at 07:27
  • Problem is when starting First database not available. Switching to an alternative database takes about 50 seconds. 2x DB2 11.5 auf windows, current DB2 drivers, Java 8 – Thomas May 26 '23 at 10:41
  • @Thomas try to set the `loginTimeout=5` connection property (in seconds). And you don't have to specify the same `X.XXX.db2.1` server properties in the `clientRerouteAlternate*` properties again. That is, use `clientRerouteAlternateServerName ="X.XXX.db2.2"` and `clientRerouteAlternatePortNumber ="50000"` instead. Retry timeout and count shouldn't be so small. `loginTimeout x 2` (visible behavior at least) should be roughly equal to a "connection wait time", if your `X.XXX.db2.1` is dead. – Mark Barinstein Jun 04 '23 at 12:29

0 Answers0