Using SQL interruption

Interrupt long running SQL queries, or interrupt queries waiting for locked data.

If the database server supports SQL interruption, a program can interrupt a long running SQL statement.

Important: Not all database servers support SQL interruption.

SQL interruption is not enabled by default. Use the OPTIONS SQL INTERRUPT ON instruction to enable SQL interruption.

The OPTIONS SQL INTERRUPT ON instruction must be used together with signal handling instructions DEFER INTERRUPT and DEFER QUIT, otherwise the program will stop immediately in case of an interruption event.

Note: Depending on the type of database server and client, SQL interruption handling may require additional overhead. Consider enabling SQL interruption only for SQL queries that can take a while to execute or can be blocked for a long time because of concurrent access (locks).
When the program receives an interruption event (either a SIGINT signal from the system, or an interrupt event from the front-end) and SQL interrupt is enabled with OPTIONS SQL INTERRUPT ON, the following happens:
  • The running SQL statement is stopped,
  • The INT_FLAG global variable is set to TRUE,
  • The SQLCA.SQLCODE is set with error -213.

SQL interruption results in abnormal SQL statement execution and raises a runtime error. Therefore, the SQL statement that can be interrupted must be protected by a WHENEVER ERROR exception handler or TRY/CATCH block.

MAIN
  DEFINE cnt INTEGER
  DEFER INTERRUPT
  DATABASE test1
  WHENEVER ERROR CONTINUE
  OPTIONS SQL INTERRUPT ON
  -- Start long query (self join takes time)
  -- From now on, user can hit CTRL-C in TUI mode to stop the query,
  -- or use the special "interrupt" action (button) in GUI mode. 
  SELECT COUNT(*) INTO cnt FROM customers a, customers b 
       WHERE a.cust_id <> b.cust_id 
  OPTIONS SQL INTERRUPT OFF
  IF SQLCA.SQLCODE == -213 THEN
     DISPLAY "Statement was interrupted by user..."
     EXIT PROGRAM 1
  END IF
  WHENEVER ERROR STOP
END MAIN

When SQL interruption is supported by a database server type other than IBM® Informix®, the database drivers will return error -213 in case of interruption, to behave as in IBM Informix.

Note: When FGLSQLDEBUG is set, OPTIONS SQL INTERRUPT ON/OFF instructions are traced.
Table 1. Database server support of SQL interruption
Database Server Type SQL Interruption API SQL error code for interrupted query
IBM DB2® LUW (Since version 9.x) SQLCancel() Native error -952
IBM Informix sqlbreak() Native error -213
IBM Netezza® SQLCancel() Native error 46
Microsoft™ SQL Server (Only 2005+ with SNC driver) SQLCancel() SQLSTATE HY008
Oracle® MySQL KILL QUERY Native error -1317
Oracle Database Server OCIBreak() Native error -1013
PostgreSQL PQCancel() SQLSTATE 57014
SAP HANA® SQLCancel() Native error -139
SQLite sqlite3_interrupt() Native error SQLITE_ABORT