Open topic with navigation

KILL STATEMENT

KILL STATEMENT — kill a long running or hanging query

Syntax

KILL STATEMENT { execid | ( CONNID num HANDLE num COUNT num ) }

Description

Kill a statement by either specifying an execution ID, as reported in the EXECID column in SYSTEM.CONNECTIONS, or specifying a statement connection id, handle and count, as reported in the CONNID, HANDLE and COUNT columns respectively, in SYSTEM.CONNECTIONS.

Parameters

execid

The execution if of the executing SQL statement that you want to terminate, as reported in the EXECID column in SYSTEM.CONNECTIONS. Use SELECT execid FROM system.connections to find this parameter.

num

A number representing the statement connection id, handle or count as reported in the CONNID, HANDLE and COUNT columns respectively, in SYSTEM.CONNECTIONS. Use SELECT connid,handle,count FROM system.connections to find these parameters. If not specifying execid, these keywords are required and are case insensitive.

Example

Step 1 Find query IDs in the system.connections table

SELECT SQLSTRING, COUNT, CONNID, HANDLE, EXECID FROM system.connections;
                SQLSTRING                COUNT  CONNID  HANDLE  EXECID 
 
 --------------------------------------- ------ ------- ------- -------------------- 
 
 select count(*) from client_sql_logs;     28   12942    2      36893543732885848067       
 
  select * from system.connections;        17   13320    2      36893545356383485955 

Step 2 Issue KILL STATEMENT using EXECID or CONNID,HANDLE, and COUNT

KILL STATEMENT 36893543732885848067;
 
/* or */
 
KILL STATEMENT connid 12942 handle 2 count 28;

Step 3 Returns error if the query is no longer running

KILL STATEMENT CONNID 12942 HANDLE 2 COUNT 28;
 
On connection 12942, Statement handle 2 has already been killed