Question
· Jan 7

How does Cache handle client-side query timeouts (ODBC / SQL Server linked server)?

#ODBC #SQL #Caché

Hello,

We are accessing an InterSystems Cacha database from Microsoft SQL Server using a linked server over ODBC (ODBC35). Queries are executed using OPENQUERY.

On the SQL Server side, there is a linked server property called Query Timeout. By default it is set to 0 (no timeout). We are considering setting it to 15 seconds and would like to understand how Cache behaves in this scenario.

Specifically, I would like clarification on the following points:

  1. When SQL Server sets a query timeout (for example, 15 seconds), is this timeout visible to or enforced by Cache itself, or is it handled entirely on the client/ODBC driver side?
  2. Does the Cache ODBC driver honor the ODBC attribute?
    • Is the timeout enforced during query execution?
    • Or only during row fetching?
    • Or not enforced at all?
  3. If SQL Server times out and cancels the request:
    • Does Caché stop executing the query immediately?
    • Or can the query continue running on the Caché server in the background?
Product version: Caché 2018.1
Discussion (5)2
Log in or sign up to continue
Vitaliy Serdtsev · Jan 9

Here is what is in the documentation for Caché:

Disable Query Timeout — Optional. If selected, causes the ODBC client driver to ignore the value of the ODBC query timeout setting.

The ODBC query timeout setting specifies how long a client should wait for a specific operation to finish. If an operation does not finish within the specified time, it is automatically cancelled. The ODBC API provides functions to set this timeout value programmatically. Some ODBC applications, however, hard-code this value. If you are using an ODBC application that does not allow you to set the timeout value and the timeout value is too small, you can use the Disable Query Timeout option to disable timeouts.

java.sql Exceptions
The following exceptions are listed here for completeness, but are not required and are never used:
...
SQLTimeoutException
...

You can empirically verify all your considerations by calling a custom stored procedure from Microsoft SQL Server, for example:

Class dc.a [ Abstract ]
{

Query DbgSqlProc(
  qMaxRows = 10,
  qTimeWait As %SmallInt = 3) As %Query(ROWSPEC = "nrow:%Integer,name:%String") [ SqlProc ]
{
}

ClassMethod DbgSqlProcExecute(
  ByRef qHandle As %Binary,
  qMaxRows = 10,
  qTimeWait As %SmallInt = 3) As %Status
{
  #define Log(%s) Set ^dbgSqlProc(qHandle("id"),%s)=$ZDateTime($NOW(),1,1,6)
  
  Set qHandle("id")=$Increment(^dbgSqlProc)
  Set qHandle("qMaxRows")=qMaxRows
  Set qHandle("qTimeWait")=qTimeWait

  $$$Log("1_Execute")

  For i=1:1:qMaxRows Set qHandle(i)=$ListBuild(i,"name"_i)
  Set qHandle=0
  
  ; Hang qHandle("qTimeWait")*3
  
  Quit $$$OK
}

ClassMethod DbgSqlProcFetch(
  ByRef qHandle As %Binary,
  ByRef Row As %List,
  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = DbgSqlProcExecute ]
{
  $$$Log("2_Fetch")
  
  If qHandle>qHandle("qMaxRows") {
    Set Row=""
    Set AtEnd=1
  }else{
    Set Row=qHandle($Increment(qHandle))
    Hang qHandle("qTimeWait")
  }
  Quit $$$OK
}

ClassMethod DbgSqlProcClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = DbgSqlProcExecute ]
{
  $$$Log("3_Close")
  
  Quit $$$OK
}

}

Example of a call: select * from dc.a_DbgSqlProc(11,5)

See Customized Class Queries

0 0