Skip to main content

CANCEL QUERY (SQL)

Cancels a query that is currently running on the system.

Synopsis

CANCEL QUERY pid [ IDENTIFIED BY sql-id ]
  [ TIMEOUT timeout ]

Description

If a query is consuming too many system resources, you may cancel its execution. The CANCEL QUERY command cancels the execution of a query. Queries are canceled by specifying the process ID the query is running in and, optionally, the SQL Statement ID of the query. A canceled query is still prepared, so canceling a query that is running for the first time will still produce a cached query.

A SQL Statement ID, stored in the Statement Index, is assigned the first time the statement is run and never changes. It can also be found by querying INFORMATION_SCHEMA.STATEMENTS, or, for statements that are currently running on your instance, by querying INFORMATION_SCHEMA.CURRENT_STATEMENTS.

Queries may also be canceled by using the $SYSTEM.SQL.CancelQuery()Opens in a new tab method.

The CANCEL QUERY command will fail with SQLCODE -400 if the provided process ID is not running a query.

Privileges

Any user that attempts to cancel a query, either with CANCEL QUERY or with $SYSTEM.SQL.CancelQuery() executed issued by a different user must have the %CANCEL_QUERY privilege.

Arguments

pid

A process ID that identifies a process in which a SQL query is running. Use $JOB to determine a process ID.

If the sql-id argument is not specified, then the system cancels the first query found running within the process; to cancel a specific query, you must provide the sql-id argument.

sql-id

An optional argument that specifies the ID of the SQL query stored within the SQL Statement Index. If this argument is omitted, the system will cancel the first query found running within the specified process; to cancel a specific query, you must provide the sql-id argument.

timeout

An optional argument that specifies how many seconds to wait before canceling the specified query. If omitted, the default is to immediately cancel the query.

Examples

The following example cancels a query running in process 8044.

CANCEL QUERY 8044

The following example cancels a query running in process 12889 that has a SQL Statement ID of 68.

CANCEL QUERY 12889 IDENTIFIED BY 68

The following example cancels a query running in process 10455 that has a SQL Statement ID of 104 with a timeout of 30 seconds.

CANCEL QUERY 10455 IDENTIFIED BY 104 TIMEOUT 30

See Also

FeedbackOpens in a new tab