DB2 Version 10.1 for Linux, UNIX, and Windows

Example of enabling client affinities for non-Java clients for DB2® for Linux, UNIX, and Windows connections

Before you can use client affinities for automatic client reroute in CLI or .NET applications, you need to include elements in the <acr> section of the db2dsdriver.cfg configuration file to indicate that you want to use client affinities, and to identify the primary and alternate servers.

The following example shows how to enable client affinities for failover without failback.

Suppose that your db2dsdriver configuration file looks like this:

<database name="SAMPLE" host="v33ec065.svl.ibm.com" port="446">
  <acr>
    <parameter name="enableAcr" value="true"/>
    <parameter name="maxAcrRetries" value="1"/>
    <parameter name="acrRetryInterval" value="2"/>
    <alternateserverlist>
      <server name="server1"
              hostname="v33ec067.svl.ibm.com" 
              port="446">
      </server>
      <server name="server2"
              hostname="v33ec066.svl.ibm.com" 
              port="446">
      </server>
      <server name="server3"
              hostname="v33ec065.svl.ibm.com" 
              port="446">
      </server>
    </alternateserverlist>          
    <affinitylist>
      <list name="list1" serverorder="server1,server2,server3">
      </list>
      <list name="list2" serverorder="server3,server2,server1">
      </list>
    </affinitylist>          
    <clientaffinitydefined>
    <!- this section has specific defined affinities -->                    
      <client name="client1" 
              hostname="appsrv1.svl.ibm.com" 
              listname="list2">
      </client>
      <client name="client2" 
              hostname="appsrv2.svl.ibm.com" 
              listname="list1">
      </client>
    </clientaffinitydefined>
    <clientaffinityroundrobin>
      <client name="client3" hostname="appsrv3.svl.ibm.com">
          <!- This entry is index 0. The number of servers is 3.
              0 mod 3 is 0, so the first that is tried
              during automatic client reroute is the server whose
              index in <alternateserverlist> is 0 (server1).
              The next server has index 1 mod 3, which is 1
              (server2). The final server has index 2 mod 3, 
              which is 2 (server3). -->
      </client>
      <client name="client4" hostname="appsrv4.svl.ibm.com">
          <!- This entry is index 1. The number of servers is 3.
              1 mod 3 is 1, so the first that is tried
              during automatic client reroute is the server whose
              index in <alternateserverlist> is 1 (server2).
              The next server has index 2 mod 3, which is 2
              (server3). The final server has index 3 mod 3, 
              which is 0 (server1). -->
      </client>
    </clientaffinityroundrobin>
  </acr>
</database>

Suppose that a communication failure occurs during a connection from the client with host name appsrv4.svl.ibm.com (client4) to the server that is identified by v33ec065.svl.ibm.com:446. The following steps demonstrate the process that occurs for automatic client reroute with client affinities.

The driver tries to connect to v33ec066.svl.ibm.com:446 (server2).
The connection to v33ec066.svl.ibm.com:446 fails.
The driver waits two seconds.
The driver tries to connect to v33ec065.svl.ibm.com:446 (server3).
The connection to v33ec065.svl.ibm.com:446 fails.
The driver waits two seconds.
The driver tries to connect to v33ec067.svl.ibm.com (server1).
The connection to v33ec067.svl.ibm.com fails.
The driver waits two seconds.
The driver returns error code SQL30081N.

The following example shows how to enable client affinities for failover with failback.

Suppose that your db2dsdriver configuration file looks like this:

<database name="SAMPLE" host="v33ec065.svl.ibm.com" port="446">
  <acr>
    <parameter name="enableAcr" value="true"/>
    <parameter name="maxAcrRetries" value="1"/>
    <parameter name="acrRetryInterval" value="2"/>
    <parameter name="affinityFailbackInterval" value="300"/>
    <alternateserverlist>
      <server name="server1"
              hostname="v33ec067.svl.ibm.com" 
              port="446">
      </server>
      <server name="server2"
              hostname="v33ec066.svl.ibm.com" 
              port="446">
      </server>
      <server name="server3"
              hostname="v33ec065.svl.ibm.com" 
              port="446">
      </server>
    </alternateserverlist>          
    <affinitylist>
      <list name="list1" serverorder="server1,server2,server3">
      </list>
      <list name="list2" serverorder="server3,server2,server1">
      </list>
    </affinitylist>          
    <clientaffinitydefined>
    <!- this section has specific defined affinities -->                    
      <client name="client1" 
              hostname="appsrv1.svl.ibm.com" 
              listname="list2">
      </client>
      <client name="client2" 
              hostname="appsrv2.svl.ibm.com" 
              listname="list1">
      </client>
    </clientaffinitydefined>
    <clientaffinityroundrobin>
      <client name="client3" hostname="appsrv3.svl.ibm.com">
          <!- This entry is index 0. The number of servers is 3.
              0 mod 3 is 0, so the first that is tried
              during automatic client reroute is the server whose
              index in <alternateserverlist> is 0 (server1).
              The next server has index 1 mod 3, which is 1
              (server2). The final server has index 2 mod 3, 
              which is 2 (server3). -->
      </client>
      <client name="client4" hostname="appsrv4.svl.ibm.com">
          <!- This entry is index 1. The number of servers is 3.
              1 mod 3 is 1, so the first that is tried
              during automatic client reroute is the server whose
              index in <alternateserverlist> is 1 (server2).
              The next server has index 2 mod 3, which is 2
              (server3). The final server has index 3 mod 3, 
              which is 0 (server1). -->
      </client>
    </clientaffinityroundrobin>
  </acr>
</database>

Suppose that the database administrator takes the server that is identified by v33ec065.svl.ibm.com:446 down for maintenance after a connection is made from client appsrv2.svl.ibm.com (client2) to v33ec065.svl.ibm.com:446. The following steps demonstrate failover to an alternate server and failback to the primary server after maintenance is complete.

The driver successfully connects to v33ec065.svl.ibm.com:446 on behalf of client appsrv1.svl.ibm.com.
The database administrator brings down v33ec065.svl.ibm.com:446.
The application tries to do work on the connection.
The driver successfully fails over to v33ec066.svl.ibm.com:446.
After 200 seconds, the work is committed.
The driver tests whether the failback interval (300 seconds) has elapsed. It has not elaspsed, so no failback occurs.
The application does more work on the connection to v33ec066.svl.ibm.com:446.
After 105 seconds, the work is committed.
The driver tests whether the failback interval (300 seconds) has elapsed. It has elapsed, so failback to v33ec065.svl.ibm.com:446 occurs.