"Processing was cancelled due to an interrupt" with SQL0952N

Toad for IBM DB2
1

Hi all,

“Processing was cancelled due to an interrupt”

with SQL0952N

I have already googled and tried resolving the issue

one was asking me to update the QUERYTIMEOUTINTERVAL=0 in the db2cli.ini file.

which I have already done .however, there was no improvement

Moreover, when i tried to resolve the issue from application and bypass the issue by using the CommandTimeout=0; then, it is not throwing the exception. However, we are not getting the desired output as well.

Please help me out.

Thanks,

Deepshika

2

Hi all,

Another Error is also showing in db2diag.log which is given below:

“FUNCTION: DB2 UDB, sort/list services, sqlsOptimizeNumMergeRuns, probe:10
MESSAGE : ADM9000W Prefetching was disabled during sort merge; performance may
be suboptimal. If this message persists, consider increasing the
buffer pool size for temporary table space “TEMPSPACE1” (ID “1”) or
increase the value of the SORTHEAP DB configuration parameter to
reduce the extent of sort spilling.”

In that case what should we do.

Thanks

Deepshikha

3

Parameter QUERYTIMEOUTINTERVAL=0 is not applicable in all cases… If is the error is thrown by e.g. .Net application, there can be no impact of this parameter.

Please see the following 2 links for solutions:

http://www-01.ibm.com/support/docview.wss?uid=swg21450816
http://www-01.ibm.com/support/docview.wss?uid=swg21424265

If this is not helpful, please provide more info about the error.

  • How long does the command take?
  • How is the query executed (CLI, .Net)?
4

“FUNCTION: DB2 UDB, sort/list services, sqlsOptimizeNumMergeRuns, probe:10
MESSAGE : ADM9000W Prefetching was disabled during sort merge; performance may
be suboptimal. If this message persists, consider increasing the
buffer pool size for temporary table space “TEMPSPACE1” (ID “1”) or
increase the value of the SORTHEAP DB configuration parameter to
reduce the extent of sort spilling.”

This is a warning. Not an error. Try the following:

  1. Get the value of SORTHEAP param for the database. On Linux:
    db2 get db cfg for | grep SORTHEAP

  2. Increase the value by the command:
    db2 update db cfg for using SORTHEAP

  3. Use db2look command and check what buffer is used by the TEMPSPACE1.
    db2look -d (then you need to go through the output generated by this command)

  4. Increase the number of pages of this bufferpool using ALTER BUFFERPOOL.
    This is a general recommendation. You need to check the amount of RAM on your server, consumption of resources by other databases and so on and use values that reflect your environment.

Sources:

db2look - https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002051.html

ALTER BUFFERPOOL - https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000885.html