For example, we have a two (or more) nodes DB2 cluster having a shared database and we want to configure Optim Perfromance Manager (OPM) for the database monitoring. The questions comes – which node should OPM connect to and what if that node is down for any reason. Is there a way that OPM could connect to another node automatically?
The answer is YES. This is how you do it.
Step-1: Register your DB2 pureScale database in OPM and use the IP address or host name of the first DB2 member.
Step-2: In same registration database form, specify second database member information in additional JDBC properties as shown in the picture below.
The additional JDBC properties defined are :
- clientRerouteAlternateServerName
- clientRerouteAlternatePortNumber
- maxRetriesForClientReroute
- retryIntervalForClientReroute
But, we have a catch here: The DB2 table which holds this additional JDBC property is only 255 bytes long and if we specify all above properties, it may not fit into the field so we have to increase the size of the field. To do this:
Go to your PERFDB database and change the length of the column to 1024.
db2 connect to perfdb db2 "ALTER TABLE IBMPDQ.PROFILE_PROPS ALTER COLUMN PROPERTY_VALUE SET DATA TYPE VARCHAR(1024)" db2 connect reset
After above is done, the additional JDBC properties can be defined as:
clientRerouteAlternateServerName=10.77.19.13,10.77.19.12; clientRerouteAlternatePortNumber=50001,50001; maxRetriesForClientReroute=1; retryIntervalForClientReroute=5
Please make sure that all 4 field definitions are in the same line and substitute IP addresses and port number accordingly. You can use host names also if DNS is set properly. And, you can add as many hosts as there are DB2 members.
Once above is done, you will have the following behavior.
If connection failure occurs for the first DB2 member, JCC driver will wait for 5 seconds and attempt to connect to the first IP address given in the list and it will try only for the 1st attempt since we have defined maxRetriesForClientReroute=1 and retryIntervalForClientReroute=5. You can tweak these as per your requirements.
By using above, OPM can connect to the second or other members if the connection to the member it connected to fails. This way, you can have OPM connected to the DB2 pureScale regardless the availability of members.
You can also use the same approach for HADR but you should change the following parameters considering that it can take few seconds to minutes for the failover to take place.
maxRetriesForClientReroute=20; retryIntervalForClientReroute=5
The above means that the JCC driver will try 20 times at an interval for 5 seconds to try to connect to the other DB2 member if a failover happens. This gives 100 seconds and these values can be adjusted for your HADR case.
If you are using a virtual IP address for HADR pair, you should still use this method as VIP does not make JCC driver to automatically connect to the other member if primary is down. For example, if 10.77.19.100 is the virtual IP address for both HADR pair then you should use the following in the additional JDBC properties.
clientRerouteAlternateServerName=10.77.19.100; clientRerouteAlternatePortNumber=50001; maxRetriesForClientReroute=20; retryIntervalForClientReroute=5
Please understand that Automatic Cleint Reroute (ACR) has nothing to do with OPM or pureScale or HADR. It is a driver capability which allows a Java program (OPM is Java) to connect to another member should the connected member is not reachable. So, it would happen for any situation where:
If JCC driver is not able to establish connection or connection dies in the middle, JCC will attempt to go to the other member defined in the list. The retry logic is in the JCC driver which does the magic so OPM will not even know that the connection was switched to some other db2.
If this helps you, please send me 25 cents peanuts gift wrapped in a package to vikram at zinox.com