December 7, 2009
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
statement.
Procedure Definition
CREATE PROCEDURE QSYS2.CANCEL_SQL (
IN VARCHAR(28) )
LANGUAGE PLI
SPECIFIC
QSYS2.CANCEL_SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL
INPUT
EXTERNAL NAME
'QSYS/QSQSSUDF(CANCEL_SQL)'
PARAMETER STYLE SQL
;
Example
CALL QSYS2.CANCEL_SQL('483456/QUSER/QZDASOINIT');
Authorization
The QSYS2/CANCEL_SQL procedure requires that the
authorization ID associated with the statement has *JOBCTL special authority.
Description
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.
Failures
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.
Commitment Control
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.
Useful Tool
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!
Posted December 7, 2009| Permalink
