Connecting clients to the Db2 Warehouse HADR configuration
To connect to a Db2
Warehouse high availability disaster recovery (HADR)
configuration on Cloud Pak for Data, external clients can
use the hostname and IP address of the master node and the NodePort
service that is
associated with the ${SVCENAME}-db2u-engn-svc
service as you would in a non-HADR
deployment.
Prerequisites
- Enable support for the Db2 HADR Automatic Client Reroute (ACR) feature by running the following
command against the primary deployment. For example, if your primary deployment is
db2wh-1573144443
:oc exec -it db2wh-1573144443-db2u-0 -- manage_hadr --enable_acr db2u-engn-svc:[port]
- Discover the service name and the associated
NodePort
values for the Db2 Warehouse service on the primary and standby deployments. For example, you can get thedb2u-engn-svc
value from the primary and standby HADR deployments and note down the serviceNodePort
values.
Connection methods
The connection method differs for command-line interface (CLI) or ODBC applications and Java™ applications that use JDBC:
- CLI or ODBC applications
- If the HADR alternate servers are configured by using manage_hadr
-enable_acr, then Db2® Automatic Client Reroute
automatically routes the connection to the standby database by using the
ClusterIP
service (intra-cluster deployment) orExternalName
service (inter-cluster deployment).You must set the following parameters in the db2dsdriver.cfg file:
- Database section
- name: primary
- hostname: IP address/hostname (if DNS resolution works) of the master node (or load balancer if the master is not exposed outside of the cluster).
- port: HADR Primary
db2u-engn-svc
serviceNodePort
or HADR Primary load balancer service port
- <acr> ... </acr> section
- enableAcr: true
- maxAcrRetries: 30
- acrRetryInterval: 2
- enableseamlessACR: true
- enableAlternateServerListFirstConnect: true
- alternateserverlist section
- name: standby
- hostname: IP address/hostname (if DNS resolution works) of the master node (or load balancer if the master is not exposed outside of the cluster).
- port: HADR Standby
db2u-engn-svc
serviceNodePort
or HADR Standby load balancer service port
For applications with their own odbc.ini configuration files, add the same keywords to the respective DSN sections.
The following is an example db2dsriver.cfg:
<configuration> <dsncollection> <dsn alias="bludb" name="bludb" host="c-db2wh-1628012388316353-db2u-engn-svc" port="50000"> </dsn> </dsncollection> <databases> <database name="bludb" host="c-db2wh-1628012388316353-db2u-engn-svc" port="50000"> <acr> <parameter name="enableAcr" value="true"/> <parameter name="maxAcrRetries" value="100"/> <parameter name="acrRetryInterval" value="2"/> <parameter name="enableAlternateServerListFirstConnect" value="true"/> <alternateserverlist> <server name="standby" hostname="c-db2wh-1628012684942463-db2u-engn-svc" port="50000"/> </alternateserverlist> </acr> </database> </databases> </configuration>
The hostname of the primary and standby instances must be recorded in a reliable DNS, or in a UNIX environment the hostname should be added to the /etc/hosts file. If the client is within the cluster, use the fully qualified domain name (FQDN) of the
db2u-engn-svc
along with the IP address, and if outside the cluster use the external route that was created to connect to the client. For example:172.30.127.129 c-db2wh-1628012388316353-db2u-engn-svc.zen.svc.cluster.local
172.30.138.32 c-db2wh-1628012684942463-db2u-engn-svc.zen.svc.cluster.local
You might also need to configure TCP/IP kernel parameters. This is a system-level configuration and is outside the scope of Db2 Warehouse. So, system administrators should configure the settings based on their needs. Suggestions for kernel parameters:
- net.ipv4.tcp_retries2 = 2: The tcp_retries2 value
tells the kernel how many times to retry before killing a live TCP connection. To make a permanent
change so that the value persists on node reboot, use this command:
sysctl -w net.ipv4.tcp_retries2=2 >> /etc/sysctl.conf
. - The following kernel parameter values should be considered depending on the kind of workload and
the environment:
- net.ipv4.tcp_keepalive_time = 300
- net.ipv4.tcp_fin_timeout = 30
- net.ipv4.tcp_keepalive_intvl = 30
- net.ipv4.tcp_keepalive_probes = 3
For more information, see the following topics:
- Database section
- Java applications
-
- If you use a DriverManager interface for connections, set the clientRerouteAlternateServerName and clientRerouteAlternatePortNumber properties.
- If you use a DataSource interface for connections, you might also need to configure JNDI for automatic client reroute by using a DB2ClientRerouteServerList instance to identify the primary server and alternate server.
You must set the following JDBC driver parameters:
- serverName: IP address/hostname (if DNS resolution works) of the master node (or load balancer if master is not exposed outside the cluster)
- portNumber: HADR Primary
db2u-engn-svc
serviceNodePort
or HADR Primary load balancer service port - clientRerouteAlternateServerName: IP address/hostname (if DNS resolution works) of the master node (or load balancer if Master is not exposed outside the cluster)
- clientRerouteAlternatePortNumber: HADR Standby
db2u-engn-svc
serviceNodePort
or HADR Standby load balancer service port - enableSeamlessFailover: true
- maxRetriesForClientReroute: 30
- retryIntervalForClientReroute: 2
For more details, see the following topics: