Virtual IP with DB2 HADR - A demo

Body

Using Virtual IP (VIP) provides a more elegant solution for Automatic Client Reroute between HADR Primary and Standby servers. The client does not have to be aware of the IP address of the Primary or the Standby server and can connect to the database only via the VIP. In cases where there have to be changes made to the Primary and Standby IP address/hostname,  the client is still able to connect to the Primary using the VIP.  Here is a demo showing VIP in action

Prereqs:
--
Working HADR database pair (Step by Step Procedure to set up HADR replication between DB2 databases)
VIP configured using db2haicu (Automated Cluster Controlled HADR Configuration Setup using db2haicu)
java client which connects to the database using <ipaddr>, <service_port>, <database_name>, <userid> and <password>. This program connects to the database and selects COL1 from table T1. The traditional way would be to provide the hostname/ipaddress of the current primary server, but we will use a virtual IP (9.25.137.5) in this exercise.

Demo:
-
Current Primary: ts-johng

$ db2pd -db hadr -hadr

Database Member 0 -- Database HADR -- Active -- Up 3 days 16:40:14 -- Date 2013-09-28-09.36.26.101811

                            HADR_ROLE = PRIMARY
                          REPLAY_TYPE = PHYSICAL
                        HADR_SYNCMODE = NEARSYNC
                           STANDBY_ID = 1
                        LOG_STREAM_ID = 0
                           HADR_STATE = PEER
                  PRIMARY_MEMBER_HOST = ts-johng
                     PRIMARY_INSTANCE = db2v10
                       PRIMARY_MEMBER = 0
                  STANDBY_MEMBER_HOST = ts-john
                     STANDBY_INSTANCE = db2v10
                       STANDBY_MEMBER = 0
                  HADR_CONNECT_STATUS = CONNECTED

lssam to show where the VIP resides (currently on ts-johng)

[db2v10@ts-johng ~]$ lssam | grep -i ser
        '- Online IBM.ServiceIP:db2ip_9_25_137_5-rs
                |- Offline IBM.ServiceIP:db2ip_9_25_137_5-rs:ts-john
                '- Online IBM.ServiceIP:db2ip_9_25_137_5-rs:ts-johng

We now call java client using VIP:

C:\Users\IBM_ADMIN\Documents\HELP\JavaClient>java test 9.25.137.5 50001 hadr db2v10 db2v10
  Connecting to database ....
  ... Connected to database.
COL1 = 2
COL1 = 2
COL1 = 2
  Closing connection...
  ...connection closed.

==

We issue a takeover on the standby to make ts-john the primary:

[db2v10@ts-john ~]$ db2 takeover hadr on db hadr
DB20000I  The TAKEOVER HADR ON DATABASE command completed successfully.
 
Current Primary: ts-john
$ db2pd -db hadr -hadr

Database Member 0 -- Database HADR -- Active -- Up 3 days 16:41:54 -- Date 2013-09-28-04.28.53.208142

                            HADR_ROLE = PRIMARY
                          REPLAY_TYPE = PHYSICAL
                        HADR_SYNCMODE = NEARSYNC
                           STANDBY_ID = 1
                        LOG_STREAM_ID = 0
                           HADR_STATE = PEER
                  PRIMARY_MEMBER_HOST = ts-john
                     PRIMARY_INSTANCE = db2v10
                       PRIMARY_MEMBER = 0
                  STANDBY_MEMBER_HOST = ts-johng
                     STANDBY_INSTANCE = db2v10
                       STANDBY_MEMBER = 0
                  HADR_CONNECT_STATUS = CONNECTED

lssam shows that the VIP is currently online on ts-john:

[db2v10@ts-john ~]$ lssam | grep -i ser
        '- Online IBM.ServiceIP:db2ip_9_25_137_5-rs
                |- Online IBM.ServiceIP:db2ip_9_25_137_5-rs:ts-john
                '- Offline IBM.ServiceIP:db2ip_9_25_137_5-rs:ts-johng

==

Call the java client with the same VIP:

C:\Users\IBM_ADMIN\Documents\HELP\JavaClient>java test 9.25.137.5 50001 hadr db2v10 db2v10
  Connecting to database ....
  ... Connected to database.
COL1 = 2
COL1 = 2
COL1 = 2
  Closing connection...
  ...connection closed.


Bada bing bada boom!! ;)

========

PS: Here is an article about setting up ACR using Alternate Server hostname/port number:
100 Tech Tips, #96 Automatic Client Reroute and HADR
 

Java Client:
---

import java.sql.*;

class test {

  public static void main(String argv[]) {
    
    Connection con = null;
      
    try {
      int prt=Integer.parseInt(argv[1]);
      
      javax.sql.DataSource ds=null;
      
      ds=new com.ibm.db2.jcc.DB2SimpleDataSource();

      ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setServerName(argv[0]);
      ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setPortNumber(prt);
      ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDatabaseName(argv[2]);
      ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDriverType(4);
      ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setTraceDirectory("\\temp");
            
      System.out.println("  Connecting to database ....");
       con = ds.getConnection(argv[3],argv[4]);
      System.out.println("  ... Connected to database.");
      
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM t1");
     
     //Return the result
      while (rs.next())
       {
          String col1 = rs.getString(1);
          System.out.println("COL1 = " + col1);
          
    }    
    
      
      System.out.println("  Closing connection...");
       con.close();
      System.out.println("  ...connection closed.");
    
    } catch (Exception e) {

      System.out.println(e);
      e.printStackTrace();
      System.exit(1);

    }
  } // end main
} // end test