Open topic with navigation

KILL STATEMENT

KILL STATEMENT — kill a long running or hanging query

Syntax

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

Note: If the NODEID parameter and value is omitted, the node ID defaults to the (Transaction Engine) node ID being connected to.

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

Closedexecid

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.

Closednum

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