DB2 Server for VSE & VM: Interactive SQL Guide and Reference

CANCEL

>>-CANCEL------------------------------------------------------><

CANCEL is an ISQL command that you can use to cancel a command, an SQL statement, or logical unit of work in progress. CANCEL can be typed anytime you are entering data, commands, or statements.

DB2 Server for VSE
Canceling a command or statement also causes a ROLLBACK to be processed.

DB2 Server for VM
Canceling a command or statement also causes a ROLLBACK RELEASE to be processed. If you have previously issued an explicit CONNECT command, you must reconnect to the database manager.

If AUTOCOMMIT is on and the previous statement was an SQL INSERT, UPDATE, or DELETE statement that affected more than one row, all changes made by the previous statement are rolled back.

If AUTOCOMMIT is off, all work since the last COMMIT, or since the beginning of the logical unit of work performed by the following, is rolled back:

• SQL statements
• ISQL commands that cause changes to table data, stored SQL statements, or routines.

DB2 Server for VSE
Except for long-running SQL statements, to start a cancel operation, type: cancel

DB2 Server for VM
To start a cancel operation, type: cancel

When canceling a command with AUTOCOMMIT off, you are prompted to verify if a CANCEL should be performed. If you type NO, the CANCEL processing is not performed. If you answer YES, all changes made to the data since the last COMMIT, or since the start of the logical unit of work if there was no COMMIT, are rolled back.

When you cancel an ISQL INPUT command with AUTOCOMMIT on, all changes since the last SAVE command, or since the start of the INPUT command if there was no SAVE command, are rolled back.

DB2 Server for VSE

Long-running SQL statements are those for which message ARI7044I is issued to tell you that the terminal is free. These statements are cancelled by pressing CLEAR and typing the CANCEL command prefixed by the ISQL transaction identifier, ISQL. (If your location has redefined the ISQL transaction identifier, prefix the CANCEL command with the transaction identifier that your location has defined.) The command looks like: isql cancel When canceling long-running SQL statements, a ROLLBACK is always performed.