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
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
No comments:
Post a Comment