A new DB2 for IBM i supplied procedure is included with Database Group PTF for IBM i 6.1 (Level 12). The procedure, QSYS2.CANCEL_SQL(), can be called to request the cancellation of an SQL statement for a target job.
SQL cancel support provides an alternative to end job immediate, when deciding how to deal with an orphaned or runaway process. End job immediate is a hammer, where SQL cancel is more like a tap on the shoulder. Prior to this improvement, the SQL cancel support was only available to ODBC, JDBC and SQL CLI applications. The CANCEL_SQL() procedure extends the SQL cancel support to all application and interactive SQL environments.
When an SQL cancel is requested, an asynchronous request is sent to the target job. If the job is processing an interruptible, long-running machine operation, analysis is done within the job to determine whether it’s safe to cancel the statement. When it’s 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’s
no active SQL statement, the request to cancel is ignored. The caller of the
cancel procedure will observe a successful return code 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
If it isn't safe to end the SQL statement, or if there’s no active SQL statement, the request to cancel is ignored. The caller of the cancel procedure will observe a successful return code 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.
CREATE PROCEDURE QSYS2.CANCEL_SQL (
IN VARCHAR(28) )
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'QSYS/QSQSSUDF(CANCEL_SQL)'
PARAMETER STYLE SQL ;
The QSYS2/CANCEL_SQL procedure requires that the authorization ID associated with the statement has *JOBCTL special authority.
The procedure has a single input parameter, the qualified job name of the job that should be cancelled. The job name must be uppercased. If that job is executing an interruptible SQL statement or query, the statement will be cancelled. The application will most likely receive an SQLCODE = SQL0952 (-952). In some cases, the failure returned could be SQL0901 or the SQL0952 could contain an incorrect reason code.
This procedure takes advantage of the same cancel technology used by the other SQL cancel interfaces:
- System i Navigator's Run SQL Scripts - Cancel Request button
- SQL Call Level Interface (CLI) - SQLCancel() API
- JDBC method - native Statement.cancel() and toolbox com.ibm.as400.access.AS400JDBCStatement.cancel()
- Extended Dynamic Remote SQL (EDRS) - Cancel EDRS Request (QxdaCancelEDRS) API
- QSYS2/CANCEL_SQL() procedure
If the cancel request occurs during the act of committing or rolling back a commitment-control transaction, the request is ignored.
The procedure will fail with a descriptive SQL0443 failure if the target job isn’t found.
The procedure will fail with SQL0443 and SQL0552 if the caller doesn't have *JOBCTL user special authority.
When the target application is running without commitment control (i.e. COMMIT = *NONE or *NC), the cancelled SQL statement will terminate without rolling back the partial results of the statement. If the cancelled statement is a query, the query merely ends. However, if the cancelled 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 will be running under the umbrella of a transaction savepoint level. When those same long running INSERT, UPDATE or DELETE SQL statements are cancelled, 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’s up to the application to determine the next action.
The QSYS2.CANCEL_SQL() provides a useful tool to database administrators for IBM i machines. Once you have the latest DB Group PTF installed (or the individual PTFs listed below) applied, you can start calling this procedure to stop long-running or expensive SQL statements. Leave the hammer in the toolbox and try calling CANCEL_SQL() instead.
- LICPGM 5761SS1 PTF SI36317
- LICPGM 5761SS1 PTF SI36318
- LICPGM 5761SS1 PTF SI36319
I'd like to thank Scott Forstie for writing this blog post. Scott is a senior software engineer at IBM, and he's the SQL development leader for DB2 for IBM i in Rochester, Minn. Before working on DB2, he worked on UNIX enablement for the AS/400 and S/390 systems. Thanks, Scott!