· May 15

Linked Table connection through JDBC

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

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.


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


/// 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.


/// 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 name="%%ID">







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.

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?


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?


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)

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(
        at com.intersystems.jdbcgateway.JDBCGateway.isValid(
        at com.intersystems.jdbcgateway.JDBCGatewayHelper.processMessage(
        at com.intersystems.gateway.JavaGateway.getJDBCGatewayMessage(
        at com.intersystems.gateway.JavaGateway.processMessage(

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..


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?


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.

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 <clinit>.WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path.

  • Driver name:
  • URL: jdbc:sqlserver://;database=CPD;trustServerCertificate=true;integratedSecurity=true;domain=osumc;authentication=NotSpecified
  • Class path: