Technical Blog Post
Abstract
100 Tech Tips, #96 Automatic Client Reroute and HADR
Body
You have setup a HADR database and it is in peer state, if your primary server goes down how does the client connect to the standby server ?
You can use automatic client reroute with HADR to make client applications connect to the new primary database after a takeover operation. If automatic client reroute is not enabled, client applications will receive error message SQL30081. Automatic Client Reroute (ACR) enables a DB2 client application to recover from a loss of communications so that the application can continue its work with minimal interruption.
To enable the automatic client reroute feature, you must use the UPDATE ALTERNATE SERVER FOR DATABASE command. Automatic client reroute does not use the HADR_REMOTE_HOST and HADR_REMOTE_SVC database configuration parameters. Automatic client reroute is only supported with TCP/IP protocol.
Here is an example: (9.25.xxx.xyz is the Primary IP address and 9.25.xxx.abc is the Standby IP address)
1. Setup a HADR database pair and make sure it is in peer state (Instruction to setup HADR can be found here)
2. Update the ALTERNATE SERVER and Port number parameter on both the primary and the standby. The Port number has to be the same port number in SVCENAME
On Primary:
db2 update alternate server for database proddb using hostname 9.25.xxx.abc port 50008
DB20000I The UPDATE ALTERNATE SERVER FOR DATABASE command completed
successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
On Standby:
db2 update alternate server for database proddb using hostname 9.25.xxx.xyz port 50008
DB20000I The UPDATE ALTERNATE SERVER FOR DATABASE command completed
successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
3. Issue 'db2 terminate' on both primary and standby for the changes to take affect.
4. To test ACR, catalog the primary database on a remote server:
db2 list db directory
System Database Directory
Number of entries in the directory = 5
Database 1 entry:
Database alias = MYALIAS
Database name = PRODDB
Node name = MYNODE
Database release level = d.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname = 9.25.xxx.xyz
Alternate server port number = 50008
db2 "select * from testacr"
COL1 COL2
----------- -----------
1 2
2 3
3 4
4 5
5 6
6 7
6 record(s) selected.
5. Issue a takeover on the standby server to switch the HADR role:
db2 takeover hadr on db proddb
DB20000I The TAKEOVER HADR ON DATABASE command completed successfully.
6. Now run the select query from the client again:
db2 "select * from testacr"
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: "9.25.xxx.abc". Service name or port number of the new connection:
"50008". Reason code: "1". SQLSTATE=08506
The above statement indicates that the connection to the 'Old Primary' has failed but has been re-established to the 'New Primary' after takeover.
7. Now issue the select again, to get the result:
db2 "select * from testacr"
COL1 COL2
----------- -----------
1 2
2 3
3 4
4 5
5 6
6 7
6 record(s) selected.
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
UID
ibm11141762