IBM Support

100 Tech Tips, #96 Automatic Client Reroute and HADR

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":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11141762