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?