Db2 Query Monitor – Canceling Db2 Threads

Tom Glaser; tglaser@rocketsoftware.com

IBM Db2 Query Monitor for z/OS (also referred to as Db2 Query Monitor) is a query analysis tool that analyzes SQL statements and provides accounting details about these queries. 

Features

Db2 Query Monitor enables you to:

• Collect, summarize, and display SQL resource consumption down to the level of the individual SQL text statement.
• Collect, summarize, and display Db2 object access statistics down to the individual table and index.
• Access data from multiple Db2 Query Monitor subsystems.
• View data from the perspective of a data sharing group.
• View active SQL statements that are currently being executed by Db2.
• Capture the full text of long SQL statements.
• Disable summary reporting for specific workloads, include or exclude specific -SQLCODES from exception or alert processing, set exception limits and thresholds, and define alert notification thresholds.
• View the expanded text description for an SQLCODE that is supplied by the IBM® utility program DSNTIAR.
• Display host variables.
• View information about the Db2 commands that are executed on monitored Db2 subsystems.
• Collect information relating to exceptional SQL-related events.
• Configure proactive notification to alert users of problems.
• Automate curative actions to deal with exceptional events as they occur.
• Access consolidated data and events for Db2 subsystems, whether within a single z/OS image or across multiple Db2 images, independent of the existence of a coupling facility.
• View and configure monitoring across your enterprise from a single console.
• …and more.

The purpose of this blog: Db2 Query Monitor gives you the ability to automatically cancel threads.  Maybe a query has consumed more than 20 minutes of CPU and you don’t want to allow this workload on the Db2 subsystem; you can use the CAE (Consolidation and Analysis Engine), also known as the Web Client interface to Db2 Query Monitor, to set up an alert to cancel this thread.  You can cancel a thread based on:

-        CPU Time

-        Elapsed Time

-        Getpage Activity

-        Number of SQL Calls

You can manually cancel a thread in Db2 Query Monitor by following these steps:

Procedure                                                                                                                                          

1. On the Db2 Query Monitor main menu, type 3 in the Option field and press Enter.
2. Locate the SQL thread you want to cancel.
3. Type E in the CMD field next to the SQL activity of interest and press Enter.

 

4. Verify that the jobname, plan, and token that are shown on the Cancel Db2 Thread window correspond to the thread you want to cancel.
5. Type Y in the Cancel Thread field.
6. Specify whether or not you want to produce a dump when the thread is canceled.
• Type Y in the With Dump field if you want to produce a dump when the thread is canceled.
• Type N in the With Dump field if you do want to produce a dump when the thread is canceled.
7. Specify whether or not you want Db2 to attempt to backout the data during transaction rollback processing.
• Type N in the NOBACKOUT field if you want Db2 not to attempt to backout the data during transaction rollback processing.
• Type Y in the NOBACKOUT field if you want to leave objects in an inconsistent state. You should not set this option to Y unless you plan to resolve the data inconsistency it creates. For more information, refer to the Db2 Command Reference.
8. Press Enter.

Cancel thread actions

There are two built-in cancel thread actions included in Db2 Query Monitor.

Automatic Cancel Thread

Cancel a thread associated with the SQL statement without user authentication.

• This cancel thread action uses the AUTHID of the CAE agent address space to cancel the thread.

• This cancel thread action is intended for use in responses.

Cancel Thread

Cancel a thread associated with the SQL statement after authentication and under the user's AUTHID.

• This cancel thread action is available for use in custom launches.

Note: Cancel thread actions cannot be used in action schedules.

Security: CAE browser client actions such as cancel thread, host variable viewing, and SQL text viewing are protected by the RACF facility classes defined for the user ID and password you use when logging in to the CAE browser client. Password verification to a given system is good for the life of the CAE browser client session. If you don’t have authority in Db2 to cancel a thread/SQL statement, you will not be able to do this with Db2 Query Monitor.

EXAMPLE:

In this example, we will cover the steps required to cancel a thread.  This process will also work for getpage monitoring, CPU time, or the number of calls.  Steps needed are as follows:

1.     Update Db2 Query Monitor Profile

2.     Create an Alert

3.     Create a Scope

4.     Create a Response

5.     Activate the monitoring (normally started upon creating the response)

STEP 1: Updating the profile

The first step is to modify your PROFILE to tell Db2 Query Monitor what threshold you want to set.  Remember, alerts are not stored in the Db2 performance database.  It’s good practice to include an exception when you set up an alert.  This alert can be modified either by using the Web Client interface or under TSO.  In my example, I’ll be using TSO.  In this example, I’ve set up an alert to cancel a thread if it consumes 2 minutes of elapsed time targeting the ID of TS4430 and using the plan for SPUFI:

  

Notice I also set up an exception?  An exception is tracked in Db2 Query Monitor and even stored in the performance database.  Alerts are not.  I recommend setting an exception whenever you set up an alert.  Also notice the profile above is from TSO.  You can see the same thing in the Web Client interface: Configuration > QM Subsystem Management:

NOTE:  Remember to refresh the subsystem ID under option 6 of Db2 Query Monitor (ISPF or Configuration/QM Subsystems Management) after you update the profile.  Click on the following link and Joern Thyssen will talk to you in this video on Profiles:

https://community.ibm.com/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=98ed2f51-3976-80f9-50b0-1fa78b295841

STEP 2: Creating an Alert

There’s a good blog on the topic of setting up alerts and working with the CAE by Christoph Theisen.  Here’s the link:

https://community.ibm.com/community/user/blogs/preethi-n/2023/04/03/generating-alerts-and-automated-actions-from-db2

Here’s another link to an article on monitoring getpage activity and using the CAE:

https://community.ibm.com/community/user/blogs/preethi-n/2023/04/28/support-for-monitoring-alerts-on-getpage

Let’s create an alert and I suggest two of them: one to cancel the query, the other to send an email note indicating that a thread had been canceled.  Let’s create the alert for the cancel thread.

 

Add a new alert:

Create a command action:

This name can be anything:

The next step is to indicate how you would like to cancel the thread.  This can be accomplished using one of the following:

While the Db2 CANCEL THREAD command can be issued directly, your company may have audit requirements in place to track this work.  I would suggest building a batch job to issue the Db2 Cancel Thread command.  The JCL will be generated and the job automatically submitted; now you have a sysout you can refer to with any questions. 

//CANCELTH JOB CARD

//**  This thread has automatically been canceled by Query Monitor.

//**  Elapsed/CPU times are adjusted by the Query Monitor Profiles.

//CANCEL   EXEC PGM=IKJEFT01

//*STEPLIB  DD DSN=MCI.DB.${subject.db2Ssid}.SDSNLOAD,DISP=SHR <- Example for Query Monitor

//STEPLIB  DD DSN=DSN.VC10.SDSNEXIT,DISP=SHR

//         DD DSN=DSN.VC10.SDSNLOAD,DISP=SHR

//         DD DSN=DSN.VC10.RUNLIB.LOAD,DISP=SHR

//DSNTRACE DD DUMMY

//SYSPRINT DD SYSOUT=*

//SYSTSPRT DD SYSOUT=*

//SYSUDUMP DD SYSOUT=D

//SYSTSIN  DD *

DSN  SYSTEM (${subject.db2Ssid})

-DISPLAY THREAD(*)

-CANCEL THREAD (${event.getAttribute("ThreadToken")})

-DISPLAY THREAD(*)

/*

Click on the red checkmark to close the Action.

A side note:  If you wanted to execute the CANCEL THREAD command dynamically and not use JCL, you can set up a “CQM Operator Command” like the following example:

STEP 3: Creating a Scope

A scope defines a set of domain elements and/or events based on several criteria.  We will be working with constraint-based scopes; defined by a set of elements that meet specified criteria.  Look at “Scope” as setting up the criteria for your alert.  For example, do you want to monitor all Db2 subsystems or just a specific one?  Do you want to only look at certain ID or package?  This is all defined in the scope.  Click on the ELEMENTS tab and Constraint Based Scope.  Set up your definition to look at the subject, like the following example:

Select Constraint Based Scope:

 

Enter any name for the Scope:

At this point, you are finished with the scope.  However, Query Monitor might be monitoring many Db2 subsystems.  If this is the case and you want to specify a certain Db2 subsystem ID, then you need to add this to the Scope.  In this example, I’m telling the scope to only look at Db2 I9A2.

Continuing on with the Scope, I also want to specify a certain user ID.  When ID is equal to TS4430 and the subsystem ID is I9A2, and if the threshold criteria is met, then cancel this thread.

Click the red checkmark to create the scope…

Let’s create another SCOPE calling it Cancel Inflight, but do this under EVENTS tab, not the ELEMENTS tab. 

Since we are monitoring the elapsed time for an SQL statement, we are going to expand the tree structure and drill down until you see “SQL Elapsed Time Problem.”

We are now going to add a constraint to this scope which says when this event is TRUE, execute.

Click the red checkmark to create the scope…

A side note: If you wanted to verify the type of SQL statement that’s being executed, you could add the following to this scope:

(event is in scope "Everything")

and (event is a SqlElapsedTimeProblem)

and (not (

   (event.getAttribute("StatementType").toString() is "232")

or (event.getAttribute("StatementType").toString() is "233")

or (event.getAttribute("StatementType").toString() is "234")

))

…this will tell Db2 Query Monitor not to cancel threads that potentially may be updating a table.  What if the query spent hours updating and you canceled it?  Everything would need to be rolled back.

231=select; 232=insert; 233=delete; 234=update;

STEP 4: Creating a Response.   A response is an action that the Db2 Query Monitor executes automatically when specific events of interest occur. This will tie the Scopes to the Response and the Actions, such as sending the email note and canceling the thread.  Click on green + to add the Response:

Give it a meaningful name:

  

Click on the “Everything” button.   

Drill down until you see the action was created earlier.  In this example, we are connecting to the Cancel Thread JCL:

Click on the “Actions to Execute” tab:

Change Event Types to Event Scope:

Under Event Scope, select the Scope “Cancel Inflight,” the name we used earlier when we created the Scope.

 

Click on “Actions to Execute.” There are two sections to this panel. 

There are 3 actions we want to perform:

Response is triggered when:

-        An Event is Posted: Triggers a response when an event has been posted to the message board.

-        An Event is Acknowledged: Triggers a response when an event is acknowledged by a user.

-        The Repetition count of an event increments: Triggers a response when the event has taken place again; thus the number of times the event has occurred is incremented.

Execute the following actions:

-        Cancel Thread JCL:  When true, execute the action to submit the JCL to cancel the SQL statement.

-        Cancel Thread Email: When true, execute the action to send an email note indicating an SQL statement was canceled.

STEP 5: Activate the monitoring.  Normally, after creating the Response, the monitoring will automatically be turned on.  A checkmark next to the Response will show the Response is “active.”  No checkmark means the Response is turned off. 

FINAL RESULT:

In this example, a batch job is submitted.  This could be anything, including an online transaction.  If the elapsed time runs longer than we want it to, then cancel this SQL statement:

Db2 Query Monitor determines an SQL statement has reached a threshold value and automatically submits the job that was created under the Actions section.  Here’s the sysout from this job:

The job is automatically canceled by Db2 Query Monitor.  The job failed with a return code of 12:

The Db2 MSTR address displays this message:

If we look at the Web Client interface, click on the TOOLS drop down menu and select “Action Console.”  The Action Console is an administrative tool that displays information about actions that are running or have previously run on the CAE server or CAE agent.  You can see that Cancel Thread JCL was submitted as well as the email note.  Notice you can also see the actual sysout from the batch job that canceled the SQL statement.

This is an example of the email note that was sent to us displaying the output that we created in the Action section.   The email note displays the actual query that was canceled. 

In Summary: There may be specific reasons why you need to automatically cancel Db2 threads/SQL statements.  Db2 Query Monitor is a good option, if you can specify a threshold setting:

You can cancel a thread based on:

-        CPU time

-        Elapsed time

-        Getpage Activity

-        Number of SQL Calls

Do you have an idea?  Below is a link to take you to IBM’s aha web page to submit ideas for Db2 Query Monitor.  Aha is IBM’s term used for product enhancement requests: 

https://ibm-data-and-ai.ideas.ibm.com/?project=DB2ZQUEMON

#IBMChampion