DBQuery Cluster Connections

0 votes
asked Jan 29, 2015 by chris-m-8014 (760 points)
retagged Jan 29, 2015 by chris-m-8014
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?

1 Answer

0 votes
Best answer

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.


This TNS names example was found on StackOverFlow.

answered Jan 29, 2015 by mike-r-7535 (13,700 points)
selected Jan 29, 2015 by chris-m-8014
commented Jan 29, 2015 by chris-m-8014 (760 points)
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?
commented Jan 29, 2015 by mike-r-7535 (13,700 points)
It looks like Intersystems has some documentation on how to connect via JDBC:
When you get to that point, we can help you implement.
commented Jan 30, 2015 by chris-m-8014 (760 points)
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.
commented Jan 30, 2015 by mike-r-7535 (13,700 points)
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.