Linked Table connection through JDBC

Question

Question

Scott Roth · May 15, 2023

#JDBC #Key Question #SQL #Health Connect #InterSystems IRIS

Ran into an issue with our upgrade from HealthShare Health Connect 2018.1.3 to IRIS HealthShare Health Connect 2022.1, that I thought I would reach out about, I already have a ticket open with WRC and started a chat on Discord. We have a couple of MS SQL tables that I have setup as Linked Tables within Cache to query NPI, Patient Account Information, Visit information, etc... When we upgraded last week we found that within a day or even a half a day that we were having problems with our JDBC connections.

Upon investigating with WRC we were able to track it down to our Queries against Linked Tables. These were simple select statements that query the table via SQL within a DTL or a function that was created. But the interesting part is that when the query was executed a new %JDBC server connection would be spawned, and when it finished it would not drop the connection. The next time the query was executed it would spawn an additional connection.

This happened to the point that at 1 time we had over 28,000 + connections that were traced back to the %JDBC server.

WRC has not been able to reproduce the issue, but has had me add "kill %JDBCGateway()" after the query has been executed. I monitored the connections over the weekend and the system seems to be stable.

But the question is why would a query against a Linked Table spawn a new connection every time, wouldn't it reuse a connection? Do I need to specify the connection string within the query? I thought the point of Linked Tables would to save time with not having the additional objects and be connected all of the time to the table. To disconnect/reconnect or even kill every time seems like would take a lot of additional resources.

Product version: IRIS 2022.1

$ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2022.1 (Build 209U) Tue May 31 2022 12:13:24 EDT

Discussion (19)3

Log in or sign up to continue

Enrico Parisi · May 16, 2023

It's an issue of the jTDS, using the connection the second time and IRIS try to reuse the connection there is an error:

ERROR #5023: Remote Gateway Error: java.lang.AbstractMethodError
at net.sourceforge.jtds.jdbc.JtdsConnection.isValid(JtdsConnection.java:2833)
at com.intersystems.jdbcgateway.JDBCGateway.isValid(JDBCGateway.java:1982)
at com.intersystems.jdbcgateway.JDBCGatewayHelper.processMessage(JDBCGatewayHelper.java:642)
at com.intersystems.gateway.JavaGateway.getJDBCGatewayMessage(JavaGateway.java:2015)
at com.intersystems.gateway.JavaGateway.processMessage(JavaGateway.java:519)
at com.intersystems.gateway.JavaGateway.run(JavaGateway.java:458)
at com.intersystems.gateway.JavaGateway.run(JavaGateway.java:421)

My guess is that for IRIS the connection "is not valid" therefore open a new connection.

It's a known issue already reported.

If you search for "jTDS AbstractMethodError isValid()" you will find many entries..

Enrico

0 0

score 0 · Answer 1 · 2023-05-16T08:22:59-04:00

why would a query against a Linked Table spawn a new connection every time, wouldn't it reuse a connection?

AFAIK it should work exactly that way and reuse the existing connection.

Evidently in your case it is not reusing the existing connection and create a new connection leaving the old connection "floating".

The "kill %JDBCGateway()" suggested by WRX simply force the close connection so you don't have "floting" connections left over.

There is probably some peculiar behavior in your code......if it's not a bug in your IRIS version.

In fact connections are stored in %JDBCGateway() array, it would be interesting, after disabling the kill, to see the full content of that array before & after using the linked tables a few times.

Enrico

score 0 · Answer 2 · 2023-05-16T09:15:03-04:00

I used the Linked Table Wizard to create the code for the table, then added the Class Method as a way to query the table in the one case...

/// Generated by the Link Table wizard on 2020-12-07 13:29:25. Note that you can access the data in this class only when the external database is accessible.

Class osuwmc.CPD.vProviderNPIEns Extends %Library.Persistent [ Not ProcedureBlock, SqlRowIdPrivate, SqlTableName = vProviderNPIEns, StorageStrategy = GSQLStorage ]

{

/// Specifies details for the SQL Gateway Connection that this class uses

Parameter CONNECTION = "jTDS-CPD-Prod,NOCREATE";

/// Specifies the external database that this class uses

Parameter EXTDBNAME = "Microsoft SQL Server";

/// Determines if INSERT statements for this external table attempt to retrieve auto-generated keys. Set this to 0 if this external table does not support auto generated keys.

Parameter EXTERNALGENERATEDKEYS = 1;

/// Specifies the external table to which this class refers

Parameter EXTERNALTABLENAME = "dbo.vProvider_NPI_Ens";

Property DoctorNumber As %String(EXTERNALSQLNAME = "DoctorNumber", EXTERNALSQLTYPE = 1, MAXLEN = 6) [ ReadOnly, Required, SqlColumnNumber = 2, SqlFieldName = DoctorNumber ];

Property NPI As %String(EXTERNALSQLNAME = "NPI", EXTERNALSQLTYPE = 12, MAXLEN = 10) [ ReadOnly, SqlColumnNumber = 3, SqlFieldName = NPI ];

Index MainIndex On (DoctorNumber, NPI) [ IdKey, PrimaryKey ];

Index ProviderDoctorNumber On DoctorNumber;

ClassMethod FindNPI(DoctorNumber As %String) As %String [ SqlProc ]

{

set NPI = ""

&sql(SELECT NPI into :NPI

FROM osuwmc_CPD.vProviderNPIEns WHERE DoctorNumber=:DoctorNumber)

IF SQLCODE <0 {write "SQLCODE error ",SQLCODE," ",%msg QUIT}

ELSEIF SQLCODE = 100 {write "Query return no results" QUIT}

quit NPI

}

Storage GSQLStorage

{

</Property>

<StreamLocation>^osuwmc.CPD.vProviderNPIEnsS</StreamLocation>

<Type>%Storage.SQL</Type>

}

In the Second Case I used the Linked Table Wizard, but call the sql code from the DTL, so I am not sure how that would be an issue.

score 0 · Answer 3 · 2023-05-16T09:43:53-04:00

So, if you run the FindNPI() method multiple times you end up with miltiple connections to SQL Server?

I'm curious, what do you get if from terminal you run:

ZWrite %JDBCGateway
Set x=##class(osuwmc.CPD.vProviderNPIEns).FindNPI(123)
ZWrite %JDBCGateway
Set x=##class(osuwmc.CPD.vProviderNPIEns).FindNPI(123)
ZWrite %JDBCGateway
Set x=##class(osuwmc.CPD.vProviderNPIEns).FindNPI(123)
ZWrite %JDBCGateway

How many connection to SQL Server are there before and after these commands?

Enrico

score 0 · Answer 4 · 2023-05-16T09:59:41-04:00

Yes with WRC we saw a new Connection using the %JDBCGateway created each time. So if we already had %JDBCGateway running and executed each statement it would spawn a new connection that would say Established when you do a netstat -anp at the OS Level.

score 0 · Answer 5 · 2023-05-16T10:48:56-04:00

Just tested using "mssql-jdbc-12.2.0.jre8.jar", Java 8 and IRIS:

IRIS for Windows (x86-64) 2022.1 (Build 209U) Tue May 31 2022 12:16:40 EDT

It works fine, the connection is reused correctly. No additional connections are made if I run a query (like your) multiple times from terminal.

I'm using IRIS for Windows, you use same version for RedHat, maybe a platform specific bug?

What jdbc driver & java are you using?

Enrico

score 0 · Answer 6 · 2023-05-16T10:54:29-04:00

We are using the jTDS (jtds-1.3.1.jar) driver because we are using Domain Service Accounts to access the databases. We are doing this because I could not get the Microsoft JDBC drivers to authenticate properly using the Domain Service Accounts in Active Directory being on Unix/Linux.

:>java -version
openjdk version "1.8.0_362"
OpenJDK Runtime Environment (build 1.8.0_362-b09)
OpenJDK 64-Bit Server VM (build 25.362-b09, mixed mode)

score 0 · Answer 7 · 2023-05-16T11:47:33-04:00

Wow how did you pin point that down? I never did see a ERROR #5023: Remote Gateway Error: java.lang.AbstractMethodError within the Operation/Process that would of shown me that.

Is there a better method to using Domain Service Accounts in Active Directory to connect to MS SQL through JDBC on Unix/Linux other than jTDS?

score 1 · Answer 8 · 2023-05-16T12:29:18-04:00

Wow how did you pin point that down?

The second time I called the method/query (like your) I looked at the variables and found that %objlasterror was set, then from terminal:

d $system.OBJ.DisplayError()

To get properly formatted error message.

BTW, what is a "Domain Service Account"? From a JDBC authentication I guess it's just a normal domain account with user/password.

Are having the authentication issue with Microsoft JDBC driver only with Domain Service Account or with standard domain user accounts as well?

Enrico

score 0 · Answer 9 · 2023-05-16T12:34:07-04:00

We were using a Local Windows Authentication account with a Hardcoded user name and password for our jobs to connect to MS SQL for the longest time, but it was frowned upon as everyone knew the user and password to sign into the database. So we were asked to make a Service Account on the Domain through Active Directory. In our AIX/Linux world we had not use Active Directory authentication so it became an issue to try to figure out how to make it work. That's when I discovered jTDS. I am going to try what @Jeff pointed out below to see if I can make it work as I could never find the correct connection string to make the Microsoft Driver to work for us.

score 0 · Answer 10 · 2023-05-16T12:21:53-04:00

According to a StackOverflow thread I just read, the connection url below is purported to work on Linux and authenticate with the MS JDBC driver:

jdbc:sqlserver://[server]:[port];database=[db];trustServerCertificate=true;integratedSecurity=true;user=[user without domain];password=[pw];authenticationScheme=NTLM;domain=[domain];authentication=NotSpecified

score 0 · Answer 11 · 2023-05-16T12:34:40-04:00

Scott Roth · May 16, 2023

Do I need a Certificate installed on Linux for that to work?

0 0

score 0 · Answer 12 · 2023-05-16T12:41:22-04:00

Enrico Parisi · May 16, 2023

Not if you add in SQL GW Connection config ";trustServerCertificate=true" in the URL, as Jeffrey example

0 0

score 0 · Answer 13 · 2023-05-16T12:51:50-04:00

Instead of adding user & password to the url string, can be entered in the SQL GW configuration page and still works. This way the password is masked and not in clear text.

score 0 · Answer 14 · 2023-05-16T13:56:44-04:00

I tried the URL and when I went to test the connection I got an error about "authenticationScheme=NTLM" being invalid. Once I removed attribute, I am now getting "Driver can not be loaded" at least from the Management Portal. If I try it manually via Terminal to test connection it fails with a -1. When I look at the JDBC Gateway log I am seeing the following..

.%Net.Remote.Gateway..%WriteOutput.....May 16, 2023 1:48:05 PM com.microsoft.sqlserver.jdbc.AuthenticationJNI <clinit>.WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path.

Driver name: com.microsoft.sqlserver.jdbc.SQLServerDriver
URL: jdbc:sqlserver://CPDDB.osumc.edu:1739;database=CPD;trustServerCertificate=true;integratedSecurity=true;domain=osumc;authentication=NotSpecified
Class path:

score 0 · Answer 15 · 2023-05-16T14:25:27-04:00

Why not use the latest jdbc driver version?

https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jd...

score 0 · Answer 16 · 2023-05-16T14:32:01-04:00

Scott Roth · May 16, 2023

6.2 is what I already had loaded so I went with it. I will try the latest.

0 0

score 0 · Answer 17 · 2023-05-16T17:01:33-04:00

Upgraded to the latest driver, without any luck. Everything I read says it is not possible to Authenticate against Active Directory when you are not coming from a non Windows environment.

score 1 · Answer 18 · 2023-06-06T06:48:27-04:00

Developer Commu... · Jun 6, 2023

💡 This question is considered a Key Question. More details here.

1 0