Automatically reroute your client requests in DB2 HADR in the event of failure

You can use automatic client reroute (ACR) with high availability disaster recovery (HADR) to transfer client application requests from a failed database server to a standby database server.

Today i will show how to implement it practically and see how it works. I'm using the same setup that i have did in my earlier post on how to setup DB2 HADR, so refer to that and setup in your environment.


Basic steps are like these.

1) Setup HADR pair and see that they are in peer mode.
2) UPDATE ALTERNATIVE SERVER configuration on the primary and standby database.
3) Catalog the primary database at the standby node/instance
4) takeover the hadr on standby database
5) run the query to test it and see if we are connected to standby from client


1) Setup HADR and check if HADR pair is in peer state or not

db2pd -db char -hadr

2)
on primary:
db2 update alternate server for database char using hostname sairam port 65000

on standby:
db2 update alternate server for database char using hostname sairam port 64000

3)
on standby:

db2 catalog tcpip node primeno remote sairam server 64000
db2 catalog database char as apple at node primeno

from standby instance connect to primary database which is cataloged as local database

db2inst2@sairam:~$ db2 connect to apple user db2inst1
Enter current password for db2inst1:

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = APPLE

db2inst2@sairam:~$ db2 "select * from  tab1"

COL1    
-----------
          1
          2
          3

  3 record(s) selected.

Note : Dont close this session as we need to check the same query from same session as described in step 5

4)

on standy: use a different session to give this command

Issue a takeover on the standby database to switch the HADR role

db2inst2@sairam:~$ db2 takeover hadr on database char
DB20000I  The TAKEOVER HADR ON DATABASE command completed successfully.

5)

from the same session from step 3 - Issue the same select query again, It will display us the warning message as connection to old database has been lost and trying to connect to new primary database. As show below If you give select query with out ur isolation level it throws error because we are connected to standby database and we need to maintain UR isolation level.

db2inst2@sairam:~$ db2 "select * from  tab1"
SQL30108N  A connection failed but has been re-established. Special register
settings might have been replayed. Host name or IP address of the new
connection: "sairam". Service name or port number of the new connection:
"64000".  Reason code: "1".  SQLSTATE=08506

db2inst2@sairam:~$ db2 "select * from  tab1"

COL1    
-----------
SQL1773N  The statement or command requires functionality that is not
supported on a read-enabled HADR standby database. Reason code = "1".

db2inst2@sairam:~$ db2 "select * from  tab1 with ur"

COL1    
-----------
          1
          2
          3

  3 record(s) selected.

All commands that i used in this post are highlighted in ITALIC 

8 steps to setup DB2 HADR solution

High Availability disaster Recovery solution HADR in DB2 is one of robust ways of maintaining two db2 databases in SYNC for high availability solutions.

We can use primary database for read,write operations and standby database for read operations or completely for disaster solution in case if primary fails.
Setting up HADR in DB2 is one of the easiest one that i ever worked, it took me only half hour to setup and test the solution.

Basic step are like these.
create a database on one machine/instance
set HADR DB parameters on primary database
backup database on primary
restore the image in standby machine/instance
set HADR DB parameters on standby database
start hadr on standby first then on primary database
Validate the HADR setup

Here is how my setup look like

1) db2 create database samsung

2) db2 update db cfg for samsung using logarchmeth1 DISK:/home/db2inst1/logs

3) Setting up HADR cfg parameters on primary database

update db cfg for samsung using HADR_LOCAL_HOST sairam
update db cfg for samsung using HADR_LOCAL_SVC 64000
update db cfg for samsung using HADR_REMOTE_HOST sairam
update db cfg for samsung using HADR_REMOTE_SVC 65000
update db cfg for samsung using HADR_REMOTE_INST db2inst2
update db cfg for samsung using LOGINDEXBUILD ON

4) Take an offline backup to be used for setting HADR, move the backup image (from the primary machine/instance) to the standby machine/instance

5) restore the database on standby instance

db2 restore db samsung taken at 20140509113342 on /home/db2inst2/ into apple

6) Setting up HADR cfg parameters on standby database

update db cfg for apple using HADR_LOCAL_HOST sairam
update db cfg for apple using HADR_LOCAL_SVC 65000
update db cfg for apple using HADR_REMOTE_HOST sairam
update db cfg for apple using HADR_REMOTE_SVC 64000
update db cfg for apple using HADR_REMOTE_INST db2inst1

7) Starting up HADR on the standby server

db2 start hadr on database apple as standby

8) Starting up HADR on the primary server

db2 start hadr on database samsung as primary

Finally validating the HADR setup is important.

create a table and populate some 5 rows at primary site, switch the between the roles and check if the table available in standby database.