Troubleshooting
Problem
Application running a complex long running statement may fail with SQL0952N error message.
Symptom
SQL0952N Processing was cancelled due to an interrupt SQLSTATE=57014
From a JDBC application it may be displayed in this form:
DB2 SQL Error: SQLCODE=-952, SQLSTATE=57014
Cause
If the application sets a query timeout value, it will stop the execution of the statement if it exceeds the timeout resulting in a SQL0952N error.
Resolving The Problem
If the application is a CLI based application, QUERYTIMEOUTINTERVAL=0 can be added to the [Common] section in the db2cli.ini file. This will cause the CLI driver to wait for the execution of the query without timing out before returning to the application.
In the db2cli.ini manually add
[Common]
QUERYTIMEOUTINTERVAL=0
(or)
Run the command from DB2 Command line.
db2 UPDATE CLI CFG FOR SECTION COMMON USING QUERYTIMEOUTINTERVAL 0
It is also possible to avoid this error by adjusting the timeout value set by the application to a larger value based on how long it is expected for the SQL statement to complete.
CLI/ODBC based application
- Value to adjust is the statement attribute SQL_ATTR_QUERY_TIMEOUT in the application
- Default value is 0 meaning that DB2 CLI will wait indefinitely for the server to complete execution of the SQL statement
- Adding QUERYTIMEOUTINTERVAL=0 to db2cli.ini will disable query timeout in this scenario
OLEDB based application (IBMDADB2 provider)
- Value to adjust is the OleDbCommand.CommandTimeout property
- Default value is 30 seconds as defined by Microsoft OLEDB specification
- Adding QUERYTIMEOUTINTERVAL=0 to db2cli.ini will disable query timeout in this scenario
.Net based application (IBM.Data.DB2 provider)
- Value to adjust is the DB2Command.CommandTimeout property
- Default value is 30 seconds as defined by Microsoft .Net specification
- Please note, QUERYTIMEOUTINTERVAL=0 may not take affect for .Net. See .NET application receives SQL0952N error for long running queries even though QUERYTIMEOUTINTERVAL=0 is set
JDBC based application
- Value to adjust is using the Statement.setQueryTimeout() API. For more information on this API, please look up this API call online in the JDK specification.
- Default value is 0 indicating that there is no limit
- It is only possible to using QUERYTIMEOUTINTERVAL=0 in the db2cli.ini to disable timeouts when using the Legacy JDBC Type 2 App driver (db2java.zip) as this driver is CLI based.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21424265