Interruptible SQL statements

You cannot cancel all SQL statements. Some types of database operations are not interruptible and others cannot be interrupted at certain points. The application can interrupt the following SQL statements.
  • ALTER INDEX
  • ALTER TABLE
  • CREATE INDEX
  • CREATE TABLE
  • EXECUTE FUNCTION
  • EXECUTE PROCEDURE
  • DELETE
  • INSERT
  • OPEN
  • SELECT
  • UPDATE

In addition to the preceding statements, you can also cancel the operation of a loop as it executes within an SPL routine.

The application and the database server communicate through message requests. A message request is the full round trip of the message that initiates an SQL task. It can consist of the message that the application sends to the database server as well as the message that the database server sends back in reply. Alternatively, a message request can consist of the message that the database server sends to the application as well as the message that the application sends in acknowledgment.

Most SQL statements require only one message request to execute. The application sends the SQL statement to the database server and the database server executes it. However, an SQL statement that transfers large amounts of data (such as a SELECT, an INSERT, or a PUT), can require more than one message request to execute, as follows:
  • In the first message request, the application sends the SQL statement to the database server to execute.
  • In subsequent message requests, the database server fills a buffer with data and then sends this data to the application. The size of the buffer determines the amount of data that the database server sends in a single message request.

In addition, the OPEN statement always requires two message requests.

The database server decides when to check for an interrupt request. Therefore, the database server might not immediately terminate execution of an SQL statement and your application might not regain control as soon as it sends the interrupt request.