IBM Support

SQL0952N error returned for a long running SQL statement.

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)
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.

[{"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"OTHER - Uncategorised","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21424265