To ensure optimal system performance and prevent resource exhaustion, it's essential to 
proactively identify and cancel long-running 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. 
Open the Query Monitoring section. it displays the running and finished queries on 
the current accelerator. To cancel a running query, select it in the list and click on 
“Cancel”.

Option 2: Cancellation via 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 needs to be cancelled in the accelerator 
and call stored procedure ACCEL_CONTROL_ACCELERATOR with cancelTasks subcommand. The usage of the stored procedure is described here https://www.ibm.com/docs/en/daafz/7.5?topic=procedures-sysprocaccel-controlaccelerator

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 monitored threshold. The default value for this parameter is 2 hours. 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 configuration file as 
an override parameter which requires a review by IBM. The following JSON snippet sets 
the threshold value to 30 minutes. IBM support 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 triggered DSNX881I event in syslog 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 and after the cancellation using the 
getAcceleratorTasks command of the ACCEL_CONTROL_ACCELERATOR stored procedure, to verify the effectiveness of the cancellation 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: Cancellations Based on Varying Thresholds Across User Groups 
Users may want to refine cancellation criteria by setting specific execution elapsed time thresholds 
for different user groups. For example, based on the enterprise requirement, the system programmer has 
set up the following criteria for their query execution: 
• Individual user IDs: query elapsed time should not exceed 15 minutes 
• Functional user ID or service IDs: query elapsed time should not exceed 30 minutes 
• Batch job user IDs: query elapsed time 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 the IDAA server configuration parameter SQL_STATEMENT_EXECUTION_TIME_WARNING_THRESHOLD alone, as described in Option 3, is insufficient to meet the various cancellation threshold requirements. This parameter defines only a single value and therefore cannot accommodate different thresholds for diverse user groups or query contexts. 

Achieving this functionality requires a more sophisticated level of automation, which can be implemented in multiple ways.
In the example below, we develop a custom stored procedure ACCEL_CANCEL_SQL_ON_THRESHOLD to handle this process.
This stored procedure is structured into four distinct parts:

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-storequery-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 meet 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); 
...