CODEWITHSUNDEEP
.netsqlentity-frameworkdb2
Borophyll
Borophyll

Reputation: 1119

DB2 queries get occasional SQL0925N: Processing cancelled due to interrupt error

I have a .Net application which interacts with a DB2 database (Entity Framework, not sure if that detail is important). Occaisionally I will get the following error on queries:

ERROR [57014] [IBM][DB2] SQL0952N  Processing was cancelled due to an interrupt.

This is the entire error text (the inner exception). It does not have a SQLSTATE.

Again, this does not happen every time (even for the same query). For instance, I can run the same query over and over again and only see the error happen 1 out of 5 times. The duration of the query is always fairly consistent.

I have searched for some documentation and found the following: http://www-01.ibm.com/support/docview.wss?uid=swg21450816

Just in an attempt to troubleshoot, I followed a suggestion in the above docs and set QueryTimeout=0 in my connection string. This did not help, I still get the error at about the same rate. I even tried QueryTimeout=500... same result. What is interesting is that in testing, when this error occurs it happens at roughly the 30 seconds mark (which the documentation mentions is the default timeout). Am I still using the default timeout setting somehow?

Connection string below:

<add name="myConn" connectionString="(entity framework stuff);provider=IBM.Data.DB2;provider connection string=&quot;Database=myDB;User ID=myId;Server=myServer;QueryTimeout=0&quot;" providerName="System.Data.EntityClient" />
  1. Could there be some other timeout setting that is overriding or taking precedence over the setting I am specifying in the connection string?
  2. Is it possible I am not dealing with a timeout issue but some other issue? I have read that this can be an indication of locking... but I can reproduce this in test being the only person accessing the database at the time. Could it be something else?

EDIT

I just tried setting QueryTimeout=1 thinking that this would for sure cause every query to time out. This seems to have had no affect. 4 out of 5 queries still completed after taking WELL over a second. What am I missing here?

Upvotes: 4

Views: 17534

Answers (2)

Fredd Minda
Fredd Minda

Reputation: 1

The error is SQL0952N

To solve, I executed the command from DB2 Command line

db2 UPDATE CLI CFG FOR SECTION COMMON USING QUERYTIMEOUTINTERVAL 0

https://www.ibm.com/support/pages/sql0952n-error-returned-long-running-sql-statement

Upvotes: 0

Borophyll
Borophyll

Reputation: 1119

I was able to solve this issue by setting the CommandTimeout on my EF data context like this:

using (dataContext = new myDB2Entities())
{
     dataContext.CommandTimeout = 60;
     // DB CODE
}

I understand now that the QueryTimeout setting I had been trying to use is simply a time interval, and at every interval the database checks to see if the application is still waiting for a response. I had not addressed the fact that the application was issuing an interrupt after the default 30 seconds.

Upvotes: 5

Related Questions