CANCEL_SQL procedure

Authorization: None required if the caller’s user profile is the same as the job user identity of the qualified job which is being canceled. Otherwise, the caller must have *JOBCTL special authority or be authorized to the QIBM_DB_SQLADM function usage ID.

The schema is QSYS2.

job-name

A character string containing the qualified job name to be canceled. It must be in upper case.

The CANCEL_SQL() procedure provides an alternative to end job immediate. It supports all application and interactive SQL environments.

When an SQL cancel is requested, an asynchronous request is sent to the job identified by job-name. If the job is processing an interruptible, long-running machine operation, analysis is done within the job to determine whether it is safe to cancel the statement. When it is determined to be safe to cancel the statement, an SQL0952 escape message is sent, causing the statement to terminate.

If it isn't safe to end the SQL statement, or if there is no active SQL statement, the request to cancel is ignored. The caller of the cancel procedure will observe a successful return which only indicates that the caller had the necessary authority to request a cancel and that the target job exists. The caller of the CANCEL_SQL() procedure has no programmatic means of determining that the cancel request resulted in a cancelled SQL statement.

If the cancel request occurs during the act of committing or rolling back a commitment-control transaction, the request is ignored.

Errors: The procedure will fail with a SQL0443 if the target job is not found. The procedure will fail with SQL0443 and SQL0552 if the caller does not have authority.

Commitment control: When the target application is running without commitment control (COMMIT = *NONE), the canceled SQL statement will terminate without rolling back the partial results of the statement. If the canceled statement is a query, the query ends. However, if the canceled statement was a long-running INSERT, UPDATE, or DELETE SQL statement, the changes made prior to cancellation remain intact.

If the target application is using transaction management, the SQL statement is running under a transaction savepoint level. When a long running INSERT, UPDATE, or DELETE SQL statement is canceled, the changes made prior to cancellation are rolled back.

In both cases, the application receives control back with an indication that the SQL statement failed. It is up to the application to determine the next action.

   CALL QSYS2.CANCEL_SQL('483456/QUSER/QZDASOINIT')