Thursday, August 31, 2023

Cancelling a SQL job

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.

3 comments:

  1. I have always used the Run, Cancel Request function within Run SQL Statements. Does this perform a CANCEL_SQL behind the scenes?

    ReplyDelete
    Replies
    1. That is a good question. I have to admit I do not the answer to it.

      Delete
  2. I believe it does according to post by Scott Forstie (IBM):

    "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 "

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.