Cancels a query that is currently running on the system.
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
