Using JDBC Gateway to call External SQL Calls - Best Practices???
I am trying to pinpoint and troubleshoot an issue we have been seeing lately. We have several SQL Outbound Adapter Operations that are seeing issues trying to connect to our MS SQL Servers using the JDBC Gateway for calling insert, select, update, and stored procedure calls.
ERROR <Ens>ErrFailureTimeout: FailureTimeout of 20 seconds exceeded in osuwmc.Visit.ADTDBWriteBusinessOperation; status from last attempt was ERROR <Ens>ErrOutConnectExpired: JDBC Connect timeout period (15) expired for 'jTDS-Visit-Dev'(jdbc:jtds:sqlserver://SQL-VD21:1739/Visit;instance=tp;domain=osumc;useNTLMv2=true;cacheMetaData=true;prepareSQL=2;) / ''+
ERROR #5023: Remote Gateway Error: <READ>%ClassForNameSynch+11 ^%Net.Remote.Gateway.1
When this error happens the Operation does not recover, we have to bounce the Operation and the Gateway it is associated with it. It sometimes causes multiple orphaned connections, or causes locking on messages.
Typically we use the same settings for each of our Outbound SQL Adapters...
Method OnInit() As %Status
{
Set ..InitDSN = ..Adapter.DSN
//Set ..Adapter.ConnectAttrs = "QueryTimeout:45" ; try this too just in case...
Kill $$$EnsRuntimeAppData(..%ConfigName)
Quit $$$OK
}
And we set StayConnected = 30
I am wondering if we are missing something somewhere to handle the connections better.
- Does anyone have any Best Practices when using SQL Outbound Adapters with a JDBC gateway and calling stored procedures when you have an a single HL7 message that might call multiple stored procedures through the SQL Outbound Adapter using JDBC?
- Am I missing connection handling somewhere?
- Or after each call do I need to be doing something different to prevent the Orphaned connections and or locks on the messages?
Comments
A few practices and diagnostics are supported by the available material:
-
Use the SQL Gateway connection definition carefully and validate all connection parameters: username, password, driver name, URL, and class path. If these are wrong, connection failures are likely. You can validate them with the Test Connection button in System Administration > Configuration > Connectivity > SQL Gateway Connections. [1][2]
-
For troubleshooting intermittent JDBC Gateway failures, enable JDBC SQL Gateway logging. The gateway log is specifically the recommended tool for monitoring JDBC connection problems and debugging what is being sent between InterSystems and the downstream database. In the portal, go to System Administration > Configuration > Connectivity > JDBC Gateway Settings and specify a full log file path. [1][2]
Note: Controlling SQL Gateway Logging and Other JDBC Settings
To monitor problems when connected via JDBC, you can enable JDBC logging for the SQL Gateway connection (see "SQL Gateway Logging"). The same dialog also allows you to specify JAVAHOME and other JDBC settings. [1]
-
Make sure
JAVAHOMEis configured correctly for the%JDBC_Serverexternal language server, because a valid Java setup is required to establish the SQL Gateway JDBC connection. The%JDBC_Serveralso requires its associated resource, which by default is%Gateway_SQL. [1] -
If connection failures occur, one recommended diagnostic is to verify whether the gateway process is running. If it is not running, the likely causes mentioned are Java installation problems or an incorrect port. If it is running, the likely issue is with the connection parameters. On modern systems, you can check System Administration -> Configuration -> Connectivity -> JDBC Gateway Server to see whether it shows that the JDBC Gateway is running. [2]
-
You can also test connectivity from code or terminal using
$SYSTEM.SQLGateway.TestConnection(). This is explicitly listed as one of the supported ways to verify the connection independently of the production flow. [2] -
For query timeout handling, one documented approach for Ensemble is to set statement attributes in the Business Operation
OnInit()method, for example:
Set ..Adapter.StatementAttrs="QueryTimeout:12"[3] -
On a related connection-management point, one community recommendation for repeated JDBC Gateway connection failures is to avoid very long-lived TCP connections by using a lower Stay Connected value rather than
-1; an example suggested value is 30. The comment specifically notes: “Longrunning tcp connections might be an issue. I would definitely try setting it to something lower.” [4] -
The SQL inbound and outbound adapters use the underlying capabilities of the InterSystems SQL and JDBC Gateways, but the retrieved material does not provide any instruction that you must manually close or reset the connection after each stored procedure call in a message flow. [5]
If you need to isolate whether the issue is in InterSystems, the JDBC driver, or the remote SQL Server/database behavior, a documented approach is to:
- analyze the gateway log, and
- reproduce the same operation outside InterSystems by writing a small Java program that uses the same JDBC driver and connection details. [2]
Based on the retrieved material, the strongest practical recommendations for your case are:
- verify and retest the DSN definition and driver settings,
- enable and inspect the JDBC Gateway log,
- confirm the gateway process and Java configuration are healthy,
- use
$SYSTEM.SQLGateway.TestConnection()for direct testing, and - keep Stay Connected at a bounded value such as 30 rather than an indefinite connection lifetime. [1][2][4][3]
Sources: