Support MC Press - Visit Our Sponsors

 

Forums Sponsor

POPULAR FORUMS Rpg sql insert into qtemp table (2020 views) What is better, free code or traditional code ? (1748 views) cant ping workstation from system i (1417 views) was 7 nd server not starting (1250 views) SQL Error 802 Type 6 - Numeric Handling for Joins? (570 views) ODF Release April 28, 2012 (495 views) Forums

 

 

Search Sponsor

Search

 

SQL Cancel support has been greatly enhanced and simplified.

Occasionally, a programmer or user issues an SQL-based report that runs much longer than anticipated. The long-running query may be caused by the user underestimating the complexity of the query or by the size of the data sets being searched. In these situations, it's nice to be able to cancel the SQL request midstream and free up the system resources that the long-running request is consuming. Cancelling the query also gives the submitter an immediate opportunity to review the SQL request instead of waiting for the request to complete.

DB2 for i has supported an SQL Cancel interface for several releases to help customers resolve the long-running query issue. While a long-running SQL statement can be terminated with an End Job (EndJob) CL command that specifies the Immediate end (*IMMED) option, the SQL Cancel interface offers a more granular approach. SQL Cancel support just terminates the long-running SQL operation within a job instead of terminating the entire job and forcing a user to start a brand new session.

Prior to IBM i 6.1, the DB2 for i SQL Cancel support was limited to the following programming interfaces:

• IBM System i Access for Windows ODBC—SQLCancel function
• IBM System i Access for Windows ADO.NET—SQLCommand.Cancel method
• IBM Toolbox for Java JDBC Driver—Statement.cancel method
• IBM Native JDBC Driver—AS400JDBCStatement.cancel method
• SQL Call Level Interface (CLI) —SQLCancel function

None of these SQL Cancel interfaces can be easily used from an interactive interface or an embedded SQL program. As a result, with recent IBM i 6.1 PTFs, IBM has delivered a new system stored procedure named CANCEL_SQL in library QSYS2.

The CANCEL_SQL stored procedure can be called from any SQL interface. The procedure has a single parameter: an input parameter that accepts a 28-byte character string. That parameter must contain a fully qualified IBM i job name (all uppercase) that identifies the IBM i job with the long-running SQL statement that needs to be cancelled.

Here's a sample invocation:

CALL QSYS2/CANCEL_SQL('411395/QUSER/QZDASOINIT')

Once the SQL Cancel request has been submitted, DB2 for i will do its best to end the request as soon as possible in the specified target job. How "deep" the currently running SQL request is within the IBM i operating system will determine how quickly DB2 can terminate the request. In addition, the operating system may discover that the SQL request is not currently in a safe state within the specified job and therefore ignore the cancellation request. Thus, there's no guarantee of the SQL request being cancelled or how quickly that occurs.

The CANCEL_SQL stored procedure call will return an error only if the specified job is not found or the user invoking the stored procedure doesn't have proper authority. Invokers of the CANCEL_SQL stored procedure must have *JOBCTL (Job Control) special authority.

The CANCEL_SQL stored procedure requires that Level 12 of the Database Group PTF for IBM i 6.1 be loaded or the following individual PTFs be loaded:  SI36317, SI36318, SI36319.

When any of the SQL Cancel interfaces are successful in cancelling an SQL statement, the application in the specified target job will typically receive an SQLSTATE value of 57014 and an SQLCODE value of -952. In some rare cases, the target job may receive an SQLSTATE value of 58004 or an SQLCODE value of -901.

You may not be aware that IBM also provides an SQL Cancel interface that doesn't require any API or stored procedure call with its IBM System i Navigator tooling. The figure below shows the simple one-click SQL Cancel support that's available from the Run SQL Scripts window. When the SQL Cancel icon is clicked on the toolbar, the System i Navigator interface invokes the IBM Toolbox JDBC driver method on behalf of the user.

Figure 1: System i Navigator offers one-click SQL Cancel support from Run SQL Scripts. (Click image to enlarge.)

This capability makes Run SQL Scripts a great interface for testing out SQL statements that you don't know the run duration of. And don't forget, the Run SQL Scripts window also provides the ability to use the Visual Explain and SQL Performance Monitor tools for in-depth performance analysis.

Hopefully, you now have a clear understanding of how the SQL Cancel interface can be leveraged through a simple stored procedure call on IBM i 6.1 to help you deal with any long-running SQL statements on your system.