Let us understand client affinity and automatic client reroute.
For example: We have 3 members in a DB2 pureScale cluster and I have one application that connects to one member from any application server. The same application connects to the second member from another application server and so forth.
Purpose: Each application server have their designated hosts and the work is done in a shared database environment.
Goal: If one member goes down, connections from the failed member should reroute automatically to the alternate servers. When failed member becomes available again, the connections should fail back automatically to that home host when that becomes available again.
In order to achieve this, we need to set the following properties for each connection in each application server.
Example: The first application server makes connections to node01 and its alternate servers are node02, node03. The second application server makes connections to node02 and its alternate servers are node03,node01. The third application server makes connections to the node03 and its alternate servers are node01 and node02.
First Application Server connection properties for each connection in each JVM.
enableClientAffinitiesList=1 clientRerouteAlternateServerName=node02,node03 clientRerouteAlternatePortNumber=50001,50001 affinityFailbackInterval=3600 enableSeamlessFailover=1 maxRetriesForClientReroute=3 retryIntervalForClientReroute=2
Second Application Server connection properties for each connection in each JVM.
enableClientAffinitiesList=1 clientRerouteAlternateServerName=node03,node01 clientRerouteAlternatePortNumber=50001,50001 affinityFailbackInterval=3600 enableSeamlessFailover=1 maxRetriesForClientReroute=3 retryIntervalForClientReroute=2
First Application Server connection properties for each connection in each JVM.
enableClientAffinitiesList=1 clientRerouteAlternateServerName=node01,node02 clientRerouteAlternatePortNumber=50001,50001 affinityFailbackInterval=3600 enableSeamlessFailover=1 maxRetriesForClientReroute=3 retryIntervalForClientReroute=2
Now let us understand the significance of main properties.
The property enableSeamlessFailover allowes JCC to retry connection automatically (on failure of its home host) and the application will not receive -4499 error. If connection failed at the transaction boundary (before start of first SQL or after commit), it is seamless and the client would not even notice that the server has changed. However, if connection fails in middle of the transaction, client will receive -4498 error which says that a connection was reestablished but the current transaction was rolled back. In that situation, the application should have a retry logic for -4498 and replay the transaction again without having any need to do the rollback as that was already done.
The properties maxRetriesForClientReroute and retryIntervalForClientReroute allows JCC to try connection 3 times at an interval of 2 seconds to the home host before going to the alternate server. In a pureScale environment, we want to go to the alternate server as soon as possible so these numbers are good. (However, if you are in a Active / Passive environment in which it may take 5 – 10 minutes, setting these properties to 10 and 60 may be appropriate.
The property affinityFailbackInterval is most important. It tells to JCC to test if the home host is available or not at the specified interval. This test happens at the transaction boundary and if there are many connections, it may result in a response time decrease since JCC will be testing the availability of the home host. In above example, it is set to 3600, which means an hour. So, it may take up to an hour for the connections to go back to the home host when that becomes available again. For example you have taken down a server for maintenance and you do not want to test aggressively if the home host is available or not. In that situation, it is better to keep this number to 30 min or 60 min. If you keep this param to a less number like 60 sec or 120 sec, you will see an impact on the response time of your application as each connection at the transaction boundary will test at specified interval if the home host is available or not.