Technical Blog Post
Abstract
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
UID
ibm11141642