DBQuery Cluster Connections

Question

We run our CPS10 environment in a clustered SQL Server active/passive environment so I believe I can refer to my SQL Server cluster IP in a DBQuery to retrieve data from whatever the active node is.

What do we do with Oracle?  We have an Oracle High Availability set up where we have a primary and a failover server and use a single TNS name that will refer to whatever the active server is.  How do we refer to our TNS name vs. a hard coded server name in a DBQuery?

Similarly, we also use an Intersystems Cache database in a 2 node active/active cluster.  There really isn't a global name we can use to refer to either server like we can with Oracle or SQL Server.  How do we code a DBQuery to be flexible enough to look at a different node in this cluster if one node is offline?

Answer 1

For Database Connections, we use JDBC Drivers.  With your license to Intersystems Cache, you will have a JDBC Driver that you can load into QIE (see here).  Use the "Custom" Driver option to create the proper Connection URL for the driver.

For Cache queries, I believe you will need to need to use the alternate qie.doSelectQuery() and qie.doUpdateQuery() methods, which can be found in Code Wizard > QIE System Functions > Database > Alternate Query Methods.

For using Oracle TNS names, it would be the same type of approach of relying on the JDBC implementation, and defining the connection URL. 

You can manually set the connection URL and define your TNS names.

jdbc:oracle:thin:@(description=(address=(host=HOSTNAME)(protocol=tcp)(port=PORT))(connect_data=(service_name=SERVICENAME)(server=SHARED)))

This TNS names example was found on StackOverFlow.

Comments

Thank you for the connection string for Oracle.  That will be a huge help in connecting to my highly available TNS name.

Do you have a suggested connection string for Intersystems Cache that will connect to a Cache cluster?

---

It looks like Intersystems has some documentation on how to connect via JDBC: http://docs.intersystems.com/cache20141/csp/docbook/DocBook.UI.Page.cls?KEY=BGJD_connecting
When you get to that point, we can help you implement.

---

We've got good connection strings to connect to individual nodes in a cluster but not to the cluster in general.  My research yesterday afternoon seems to show that Intersystems Cache JDBC implementation does not support connections to a cluster.

---

That's unfortunate.  If they have another way to connect to the cluster using Java API's, we can help you use those API's and wrap them in some Published Functions.  You could then call your Published Function to perform your queries using the cluster.