Db2 for z/OS and its ecosystem

 View Only

Setting Up Automatic Query Cancellation in IBM Db2 Analytics Accelerator

By Huiyan Roy posted 20 days ago

  

To ensure optimal system performance and prevent resource exhaustion, it's essential to proactively identify and cancel long-running analytical queries in Db2 Analytics Accelerator. By implementing timely query cancellation, users can significantly improve response times, avoid system lockups, and maintain system stability. Several options can be utilized to selectively terminate these queries, effectively reclaiming system resources and prioritizing more critical tasks.

Option 1: Manual Cancellation on Data Studio

A manual cancellation can be done from the accelerator GUI interface of Data Studio.

Opens the Query Monitoring section, which displays the running and finished queries on the currently viewed accelerator. To cancel a running query, select it in the list and click on "Cancel".

Option 2: Cancellation per Stored Procedure ACCEL_CONTROL_ACCELERATOR

The cancellation can also be done by running ACCEL_CONTROL_ACCELERATOR stored procedure. Identify the Task ID of the query that need to be cancelled in the accelerator and call stored procedure ACCEL_CONTROL_ACCELERATOR with cancelTasks sub-command. The usage of the stored procedure is described here https://www.ibm.com/docs/en/daafz/7.5?topic=procedures-sysprocaccel-control-accelerator

Here is an example to call the stored procedure cancelTasks command.

CALL SYSPROC.ACCEL_CONTROL_ACCELERATOR

(accelerator_name, command, result, message)

with 

<?xml version="1.0" encoding="UTF-8" ?>

<aqt:controlCommand xmlns:aqt=http://www.ibm.com/xmlns/prod/dwa/2011 

 version="1.0">

            <cancelTasks>

                        <task id="123456" />

            </cancelTasks>

</aqt:controlCommand>

Option 3: Query cancellation Based on DSNX881I Warning Message

The accelerator server has a config parameter that helps monitoring the execution time of queries: SQL_STATEMENT_EXECUTION_TIME_WARNING_THRESHOLD. This parameter monitors the running SQL statements and raises a DSNX881I event message if a query has exceeded the time threshold. The default value for this parameter is 2 hours. Open a Salesforce case and ask IBM support to change this value if a different range is needed. For IDAA on IIAS customers, this value is changed during an MS Teams or Webex meeting by IBM support on the appliance. For zLinux or IDAA on z customers, this value can be specified in the JSON config file as override parameters which need to be reviewed by IBM. The following JSON snippet sets the threshold value to 30 minutes. IBM will provide an updated review token if the change is fine. 

"override_parameters": { 

  "ibm_review_token": "xxxxxxxxxxxxxxxxxx", 

    "customer_name": "<IBM_customer_name>", 

    "dwa_server_conf": { 

        "SQL_STATEMENT_EXECUTION_TIME_WARNING_THRESHOLD ": "30min" 

    } 

}, 

This is an example of the DSNX881I event message triggered when the 30-minute threshold is exceeded:

DSNX881I -DB2T 2001 W 33327 (2024-10-21 22:33:26 UTC) DB1TACCL(192.168.100.65) SQL statement with task ID 15667303 is running for more than 1928 seconds"

 

Note that no automatic query cancellation is carried out by this parameter. Users can determine how to handle this message by building scripts to automatically detect and parse, then cancelling the query based on the task ID using the approach described earlier. A step of verification can be built into the script prior to the cancellation using the getAcceleratorTasks command of the ACCEL_CONTROL_ACCELERATOR stored procedure. This is an example:

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">    <getAcceleratorTasks />
</aqt:controlCommand>

Option 4: Cancellation per Thresholds for Different User Groups

Users may want to refine cancellation criteria by setting specific execution time thresholds for different user groups. For example, based on the enterprise requirement, the user has set up the following criteria for their query execution:

  • Individual user IDs: queries should not exceed 15 minutes
  • Functional user ID or service IDs: queries should not exceed 30 minutes
  • Batch job user IDs: queries should not exceed 1 hour

Queries may be executed through various interfaces, such as Advanced Query Tool, DSNTEP2 batch SQL execution, Data Studio, Java applications, etc. Make sure the user IDs of these executions are all included in your threshold specification.

At this point, setting accelerator server configuration parameter SQL_STATEMENT_EXECUTION_TIME_WARNING_THRESHOLD alone is not sufficient for fulfilling the requirements, as this parameter lacks the flexibility to accommodate different thresholds for diverse user groups and query contexts.

A more sophisticated automation is required for these purposes. This can be done in many different ways. In the following example we build a self-defined stored procedure ACCEL_CANCEL_SQL_ON_THRESHOLD to do this work. The ACCEL_CANCEL_SQL_ON_THRESHOLD stored procedure consists of these three components: 

  1. If not yet done, create the Db2/z table QUERY_HISTORY_TABLE2. The definition and creation of this table is described here:  https://www.ibm.com/support/pages/how-store-query-history-db2-zos-table
  2. Call the stored procedure ACCEL_GET_QUERIES2 to populate the result set into the RESULT_XML string.  The snippets example to call ACCEL_GET_QUERIES2 and result population should look something like this:

    CREATE PROCEDURE SYSPROC.ACCEL_CANCEL_SQL_ON_THRESHOLD

       ...

      SP: BEGIN

        DECLARE ...

        ASSOCIATE RESULT SET LOCATOR ( QUERY_INFO_LOCATOR ) WITH PROCEDURE SYSPROC.ACCEL_GET_QUERIES2; 

        ALLOCATE QUERY_INFO_CURSOR CURSOR FOR RESULT SET QUERY_INFO_LOCATOR;   

        fetch_loop:  LOOP  SET QUERY_INFO_XML = ''; 

           FETCH QUERY_INFO_CURSOR INTO QUERY_INFO_SEQID, QUERY_INFO_XML; 

           IF SQLCODE != 0 THEN  SET RETURN_MESSAGE = 'FETCH000 ';

              LEAVE fetch_loop; 

           ELSE 

              SET RECEIVED_SOMETHING = 1; 

              SET RESULT_XML = CONCAT(RESULT_XML, QUERY_INFO_XML); 

           END IF;   

        END LOOP fetch_loop;   

        CLOSE QUERY_INFO_CURSOR; 
       ...



  3. Populate the results of RESULT_XML into QUERY_HISTORY_TABLE2. Define query cancellation criterion based on the need of the enterprise. Select the queries from QUERY_HISTORY_TABLE2 that meets the criterion and populate the result to a second table QUERY_CANCEL_HISTORY2.

     ...

     IF RECEIVED_SOMETHING != 0 THEN   

            INSERT INTO DB2ADMN.QUERY_HISTORY_TABLE2

            (   ...

                ...)

     ...

     INSERT INTO USER.QUERY_CANCEL_HISTORY2

                  (SELECT QRY.* FROM DB2ADMN.QUERY_HISTORY_TABLE2 QRY

                  WHERE  ACCELERATOR=HV_ACCEL AND

                  (ELAPSEDTIMESEC > 3600 AND QRY.USER = 'DBOUSER' AND (STATE LIKE 'RUNNIN%' or STATE LIKE 'FETCHIN%')

                   OR

                   ELAPSEDTIMESEC > 900 AND QRY.USER LIKE 'T%' AND (STATE LIKE 'RUNNIN%' or STATE LIKE 'FETCHIN%')

                   OR

                   ELAPSEDTIMESEC > 1800 AND QRY.USER LIKE 'E%' AND (STATE LIKE 'RUNNIN%' or STATE LIKE 'FETCHIN%'))

                   )

                 ;



  4. Call ACCEL_CONTROL_ACCELERATOR  with the cancelTasks subcommand with the taskID of the queries returned by the previous step and wrap up. 

          ...

SET FILTER_XML = '<?xml version="1.0" encoding="UTF-8"?><aqt:controlCommand xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.2"><cancelTasks><task id="'CONCAT LTRIM(DIGITS(TASK_ID),'0') CONCAT '"/></cancelTasks></aqt:controlCommand>';

         CALL SYSPROC.ACCEL_CONTROL_ACCELERATOR(HV_ACCEL,FILTER_XML,RESULT_XML,MESSAGE_XML);

...

Views and opinions expressed in this article are my own and do not represent those of IBM. While all reasonable efforts are made to ensure accuracy, comments, suggestions and corrections are encouraged.


#Spotlight
0 comments
18 views

Permalink