Have you ever been in the position of having a SQL statement running in ACS's Run SQL Scripts, RSS, and you realize that the reason it is taking so long is something in your selection criteria is too large. I am sure the same thought has crossed all of our minds: "Wouldn't it be nice if I could just cancel this statement!"
There is a way to do this using the procedure: CANCEL_SQL. The documentation describes this as:
CANCEL_SQL() procedure provides an alternative to end job immediate
This is true I could cancel the RSS job with the End Job command, ENDJOB, with the *IMMED option:
ENDJOB JOB(588656/QUSER/QZDASOINIT)
OPTION(*IMMED)
SPLFILE(*YES)
LOGLMT(0)
|
When I do I get the following message in my RSS session:
SQL State: 08S01 Vendor Code: -99999 Message: Communication link failure. (Connection was dropped unexpectedly.) No database connection |
The database connection is dropped. I can always reconnect using Ctrl-F11 or: Connection > Reconnect. When I do I start a new job, with all that involves. For example: the loss of any output files, tables, etc. in QTEMP.
As an alternative if I use the CANCEL_JOB Procedure the SQL statement ends, and the job is still active.
CANCEL_JOB has one parameter the job name of the RSS session. How do I find that? The quickest way I know is in the RSS session window itself.
At the bottom of the session window are two buttons:
When I click on the "Environment" button all of the session's Global Variables and Special Registers for the job are displayed in the lower half of the window. One of the Global Variables is QSYS2.JOB_NAME.
I highlight the job name with my mouse and copy it.
I open a new RSS session and type the following into it, inserting the job name as the Procedure's parameter:
CALL QSYS2.CANCEL_SQL('588656/QUSER/QZDASOINIT')
|
When I execute the Procedure it will end the execution of SQL statement, and display the following error:
The SQL statement has been ended, and the same job is still active.
You can learn more about the CANCEL_SQL SQL Procedure from the IBM website here.
This article was written for IBM i 7.5, and should work for some earlier releases too.
I have always used the Run, Cancel Request function within Run SQL Statements. Does this perform a CANCEL_SQL behind the scenes?
ReplyDeleteThat is a good question. I have to admit I do not the answer to it.
DeleteI believe it does according to post by Scott Forstie (IBM):
ReplyDelete"This procedure takes advantage of the same cancel technology used by the other SQL cancel interfaces:
Access Client Solution’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 "