Introduction to the new query cancellation feature in SQLFire

Products

• Pivotal GemFire XD

Issue/Introduction

This article introduces the new query cancellation feature introduced in SQLFire 1.1.2.3.

Environment

Resolution

To manage expensive queries better, SQLFire includes two new query cancellation features starting with SQLFire 1.1.2.3.

Use of sqlfire.query-timeout

Control maximum query time by setting the SQLFire property "sqlfire.query-timeout" at the system level. This query timeout is in seconds (Default=0--No limit).

You can set the query timeout in the sqlfire.properties file of the SQLFire server:

sqlfire.query-timeout=60

Use of system procedure

You can cancel a long running query by using the system procedure "SYS.CANCEL_STATEMENT(STATEMENT_UUID)".

The query below is CPU intensive and takes a excessive amount of time to execute:

Select eqp_id, cntxt_id from CONTEXT_HISTORY where eqp_id||cast(cntxt_id as char(100)) in ( select eqp_id||cast(t.cntxt_id as char(100)) from RECEIVER_LOG t where 1=1);

Identify the query using session id:

sqlf> select id, session_id, current_statement_uuid, current_statement, current_statement_status from sys.sessions;
ID|SESSION_ID |CURRENT_STATEMENT_UUID|CURRENT_STATEMENT |CURRENT_STATEMENT_STATUS
-------------------------------------------------------------------------------
pnq-rdiyewar(7194)<v2>:37098|2 |8589934609-8589934687-1 |SYSLH0001 select eqp_id, cntxt_id from CONTEXT_HISTORY where eqp_id||cast(cntxt_id as char(100)) in (select eqp_id||c& |EXECUTING STATEMENT

Cancel the query using CURRENT_STATEMENT_UUID(A unique identifier for statement):

sqlf> call sys.cancel_statement('8589934609-8589934687-1');
Statement executed.

The running query fails instantly with SQLSTATE "XCL56":

sqlf> select eqp_id, cntxt_id from CONTEXT_HISTORY where eqp_id||cast(cntxt_id as char(100)) in (select eqp_id||cast(t.cntxt_id as char(100)) from RECEIVER_LOG t where 1=1 );
ERROR XCL56: SQLSTATE=XCL56,SEVERITY=-1: (Server=pnq-rdiyewar[1529],Thread[DRDAConnThread_15,5,sqlfire.daemons]) The statement has been cancelled due to a user request. ... 
java.sql.SQLException: SQLSTATE=XCL56,SEVERITY=-1: (Server=pnq-rdiyewar[1529],Thread[DRDAConnThread_15,5,sqlfire.daemons]) The statement has been cancelled due to a user request. ...