Triton Db2 Geek

Confessions of a DB2 geek

IBM Gold Consultant Program and IBM Premier business Partner

Client Connections in a TSA/HADR cluster- Part 4

June 6th, 2017 - by

In Part 1 I showed how to setup a simple TSA/HADR cluster consisting of two servers, and what happens to an active client connection in case of a failover.

In Part 2 and Part 3 we saw how ACR and VIP independently affect a client’s connection in case of a failover in the cluster.

In this final part, we will once again reconfigure the TSA/HADR cluster, this time to use VIP and ACR together and check their combined effect on the client processing during and after a failover.

TSA/HADR failover with ACR+VIP

With VIP already set up in Part 3, what remains is to configure ACR.
Only, this time we will use the VIP address for the alternate server specification, rather than the real TCP/IP addresses of servers Itchy and Scratchy:

Primary (ITCHY, 192.168.20.64):

Standby (SCRATCHY, 192.168.20.65):

The fresh client’s connection to the SAMPLE database proves the VIP and ACR settings are in place:

Executing the failover:

And this is what happened on the client this time:

We can see that the “in-flight” transaction failed again as expected, but the next transaction executed successfully, meaning that the client has automatically recovered its database connection following a failover and continued its batch job processing, with no need for any user intervention!

The client is still connected to the database on the same TCP/IP address, the VIP address:

Conclusion

Using the VIP address and the ACR setting together in a TSA/HADR cluster enabled the client to automatically recover from a database failover with minimum damage – only the in-flight transaction was rolled back – and to continue its batch job processing as if nothing happened.

When the DBAs arrive at work next day, they might notice that the failover happened (but only if they look in the DB2 logs, or at the results of a batch process that was active during the failover).

On the other side – the AppDev team, test team and/or end users will never know that anything happened at all. Unless the DBA tells them J

Of course, care should be taken to reexecute all failed transactions where necessary, that is – where the client itself has not done that automatically.

What more could a DBA want?

 

Comments

  • Miguel A. Celi says:

    Dear Damir,
    Thank you for sharing these excellent documentation.
    I have been checking your tests that you have been using Linux. In the past, I was working with HADR using AIX and it worked perfect with HACMP.

    I am planning to recommend the client the installation of HADR as a good solution for HA and DR, but before I do the recomendation, I have been looking any installation using DB2 LUW on Windows and nothing appear regarding TSA.

    Do you know or did you install the HADR by using TSA on Windows? does it work?.

    I just want to know if TSA will work on Windows before I make the recommendation.

    Thank you for your time to answer these questions,

    Miguel Celi

    DB2 DBA

    HADR with TSA using db2haicu (A DB2 v9.7 utility) on AIX/Linux

    • Damir Wilder Damir Wilder says:

      Hi Miguel,
      thank you for your inquiry!
      Unfortunately I have not installed TSA on Windows myself (yet), so I cannot definitely confirm that it works, and more importantly – how it works.
      But, according to the DB2 Knowledge Center, Windows is one of the supported platforms, so it should work OK.
      Take a look at this link:
      https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.qb.server.doc/doc/r0051895.html
      One notable difference between UNIX platforms and Windows is:
      “On Windows, SA MP is bundled separately as part of DB2 server”

      I hope this answers your question, at least to some degree.
      Finally, thank you for your interest in my TSA articles!
      (if you get around to installing TSA on Windows, I would like to hear back from you on that!)

      Kind regards,
      Damir

«

Tag Archives