FAQ: How to configure the JDBC driver for automatic client re-route

https://www.ibm.com/support/pages/faq-how-configure-jdbc-driver-automatic-client-re-route

Recommended Resources

Abstract

There are multiple methods to configure the JDBC driver (db2jcc.jar/db2jcc4.jar) for automatic client reroute. What are the pros and cons of each method? How does this relate to High Availability Disaster Recovery (HADR)?

Content

For configuring non-Java applications refer to HOWTO: Configure non-Java for automatic client reroute.

What is Automatic Client Reroute (ACR) ?
Automatic client reroute is an IBM® Data Server feature that redirects client applications from a failed server to an alternate server so the applications can continue their work with minimal interruption.

How is ACR related to High Availability And Disaster Recovery (HADR) ?
The ACR functionality is entirely separate from HADR. They are two different functionalities which are independent of each other. Some configurations use only the ACR portion without HADR because the application performs SELECT queries without any INSERT/UPDATE/DELETEs.

How is ACR configured with DB2 pureScale or DB2 for z/OS SYSPLEX?

For more information, see the following articles:

DB2 PureScale enablement: Workload balancing, automatic client reroute, and client affinities

Increase scalability and failure resilience of applications with IBM Data Server Driver for JDBC and SQLJ - Explore benefits of Sysplex and IBM DB2 pureScale data-sharing technologies

What are the methods to configure ACR ?
There are three methods to configure ACR, each with their advantage/disadvantage.

For methods (1) and (2) below lets assume that:
The port number is port used by DB2 to listen for connections (DBM CFG SVCENAME parameter).

Primary: test1.ibm.com:50000
Secondary: test2.ibm.com:60000
Database is named SAMPLE on both systems.

(1) DB2 Alternate Server

Pros: Easy to implement.

If the client is unable to make an initial connection to test1.ibm.com to retrieve the alternate server list, then the client checks to see what values are set in the parameters clientRerouteAlternateServerName and clientRerouteAlternatePortNumber. If the connection to test1.ibm.com is successful, then the retrieved alternate server information overrides clientRerouteAlternateServerName and clientRerouteAlternatePortNumber.

On the Client system:
clientRerouteAlternateServerName=test2.ibm.com
clientRerouteAlternatePortNumber=60000

On the DB2 Primary system:

db2 "update alternate server for sample using hostname test2.ibm.com port 60000"

Check the configuration to confirm that the alternate server information has been set.

db2 "list db directory"

Database 1 entry:

Database alias = SAMPLE
Database name = SAMPLE
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = -1
Alternate server hostname = test2.ibm.com
Alternate server port number = 60000

On the DB2 Secondary system:
db2 "update alternate server for sample using hostname test1.ibm.comport 50000"

Check the configuration to confirm the alternate server information has been set.
db2 "list db directory"

Database 1 entry:

Database alias = SAMPLE
Database name = SAMPLE
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = -1
Alternate server hostname = test1.ibm.com
Alternate server port number = 50000

(2) JDBC Driver Properties

Pros: Easy to implement

enableClientAffinitiesList=1
clientRerouteAlternateServerName=test1.ibm.com,test2.ibm.com
clientRerouteAlternatePortNumber=50000,60000

Note: If you are setting the JDBC driver properties from the Websphere Admin Console:

enableClientAffinitiesList Integer type
clientRerouteAlternateServerName String type
clientRerouteAlternatePortNumber String type

See "JDBC Driver Properties" for other JDBC property data types

This method hard codes the primary and secondary connectivity information in the JDBC client. There are no server-side dependencies, but it may be labor intensive to set these values if there are many JDBC clients.

For more information, see Client affinities for Db2 on Linux, UNIX, and Windows systems.

(3) JNDI via Websphere

Pros: Alternate server information is persistent

Cons: More complex to implement
For more information, see Building a high availability database environment using Websphere Part1.

Additional ACR Parameters

For more details on the keywords below please refer to JDBC Driver Configuration Keywords in the Related Links section.

enableSeamlessFailover: Determines if application receives SQLException to let it know that ACR has occurred.

maxRetriesForClientReroute: Controls number of tries to primary system

retryIntervalForClientReroute: Amount of time to wait before retries

interruptProcessingMode: See notes in regards to how Statement.cancel() is handled with ACR

TCP/IP keepalive parameters may also have to be adjusted. These are outside the scope and expertise of DB2 Defect Support.

Additional Notes:

If using hostnames for the alternate servers, ensure these hostnames are valid on the client side.

nslookup <host name>

If hostname cannot be found add it to the Domain Name Server or:

Linux/Unix: /etc/hosts

Windows: %WINDIR%\System32\drivers\etc\hosts