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

  1. 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]
    
  2. 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 the db2u-engn-svc value from the primary and standby HADR deployments and note down the service NodePort 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) or ExternalName 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 service NodePort 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 service NodePort 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:

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 service NodePort 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 service NodePort or HADR Standby load balancer service port
  • enableSeamlessFailover: true
  • maxRetriesForClientReroute: 30
  • retryIntervalForClientReroute: 2

For more details, see the following topics: