Here are two templates that one can use to make Automatic Client Reroute to work properly in a HADR environment.
Please note that these instructions are only valid for HADR pair in which the second machine is ready to takeover. I will publish / update this article later to include instructions for a HADR pair in which the standby is used as RoS (Read on Standby) or multiple standbys are also used.
Use db2dsdriver.cfg file and place it in ~/sqllib/cfg directory. If you are using thin client and do not have sqllib directory then make sure that the environment variable – DB2DSDRIVER_CFG_PATH is set properly pointing to the location of the db2dsdriver.cfg file.
Even if you are using Virtual IP address (VIP), use the VIP address in both places for primary and standby.
Myth: People think that if they use VIP, their is no need of ACR. Even if you are using VIP, you still need ACR as that is the trigger for the DB2 drivers (Java and non-Java) to trigger retry logic for the connection reestablisment if connection is lost for the primary.
Here is a sample db2dsdriver.cfg file.
<configuration> <dsncollection> <dsn alias="ALIASNAME" name="DBNAME" host="serverPrimary" port="port_num"/> </dsncollection> <databases> <database name="PSDB" host="serverPrimary" port="port_num"> <parameter name="KeepAliveTimeout" value="15"/> <parameter name="tcpipConnectTimeout" value="5"/> <parameter name="ConnectionTimeout" value="15"/> <acr> <parameter name="enableAcr" value="true"/> <parameter name="enableSeamlessAcr" value="true"/> <parameter name="enableAlternateServerListFirstConnect" value="true"/> <parameter name="maxAcrRetries" value="60"/> <parameter name="acrRetryInterval" value="3"/> <alternateserverlist> <server name="server1" hostname="serverPrimary" port="port_num"/> <server name="server2" hostname="serverStandby" port="port_num"/> </alternateserverlist> </acr> </database> </databases> </configuration>
Additionally, on the client – you can set the following db2 registry variables to the following values:
db2set DB2TCP_CLIENT_CONTIMEOUT=15 db2set DB2TCP_CLIENT_RCVTIMEOUT=15 db2set DB2TCP_CLIENT_KEEPALIVE_TIMEOUT=15
On DB2 servers (both HADR pair), you must set the following:
UPDATE ALTERNATE SERVER FOR DATABASE <dbname> USING HOSTNAME <standbyhost> PORT <standbyport>
UPDATE ALTERNATE SERVER FOR DATABASE <dbname> USING HOSTNAME <primaryhost> PORT <primaryport>
Optionally, you can set the following additional registry variable depending upon the replication rate to tune the tcpip network send and receive buffer.
db2set DB2_HADR_SOSNDBUF=4096 --> small workload db2set DB2_HADR_SOSNDBUF=16385 --> medium workload db2set DB2_HADR_SOSNDBUF=65536 --> heavy workload db2set DB2_HADR_SORCVBUF=4096 --> small workload db2set DB2_HADR_SORCVBUF=16385 --> medium workload db2set DB2_HADR_SORCVBUF=65536 --> heavy workload
db2set DB2_HADR_SOSNDBUF=4096 --> small workload db2set DB2_HADR_SOSNDBUF=16385 --> medium workload db2set DB2_HADR_SOSNDBUF=65536 --> heavy workload db2set DB2_HADR_SORCVBUF=4096 --> small workload db2set DB2_HADR_SORCVBUF=16385 --> medium worload db2set DB2_HADR_SORCVBUF=65536 --> heavy workload
For Java set the following properties wherever applicable based upon the application server, or stand alone application for without read on standby HADR pair.
clientProgramName=<name of your program> driverType=4 enableSeamlessFailover=1 clientRerouteAlternateServerName=hadrprimaryservername,hadrstandbyservername clientRerouteAlternatePortNumber=hadrserverdb2portnumber,hadrserverdb2portnumber maxRetriesForClientReroute=60 retryIntervalForClientReroute=3 blockingReadConnectionTimeout=5 tcpipConnectTimeout=5 loginTimeout=15 keepAliveTimeOut=15
The other parameters like update alternate server, db2 registry variables should be same as set for Non-Java.
Please note: I have used TCP/IP timeout etc and they are all optional. These come into picture when you have network glitches and in absence of these parameters, the application may wait for long to switch to the standby. Each one of these parameters has a history and a story behind this and when they were implemented in DB2. So, without thinking too much – use these parameters as is and your life will be simpler.
For example: keepAliveTimeOut in java is useful for connections already established whereas tcpipConnectTimeout, loginTimeout and blockingReadConnectionTimeout come into picture for the new connection. Bottom line is : Don’t think too much and use the above template and be happy.