This blog explores how to monitor SQL resource consumption on IBM Db2 Analytics Accelerator (IDAA) appliances by leveraging the Version 8 monitoring tables. In particular, it demonstrates how to correlate real‑time activity from SYSMONITOR_ACCEL_ACTIVITY_TABLE with Db2z‑hosted QUERY_HISTORY_TABLE2 table to gain deeper insight into query performance and resource usage.
Overview
IBM Db2 Analytics Accelerator (IDAA) version 8 introduces system monitoring via Accelerator-on-Table (AoT) objects under SYSACCEL.SYSMONITOR_* . By combining the real-time activity snapshot table with a Db2-hosted query history table QUERY_HISTORY_TABLE2 populated from stored procedure SYSPROC.ACCEL_GET_QUERIES2, you can correlate CPU time, elapsed time, temporary space, and memory usage for live queries executing on the accelerator.
- V8 AoT Activity snapshot table
SYSACCEL.SYSMONITOR_ACCEL_ACTIVITY_TABLE
is a real-time view of queries currently executing in the accelerator, including temp space usage, CPU usage, memory usage, io usage, etc. By default, the table is refreshed once per minute.
- Query history table
ACCEL.QUERY_HISTORY_TABLE2
stores execution metrics captured by calling SYSPROC.ACCEL_GET_QUERIES2.
Join Key
ACCEL_ACTIVITY_TABLE.APPLICATION_HANDLE = QUERY_HISTORY_TABLE2.SESSIONID
Important Limitation
The join is only reliable for queries whose state is RUNNING, QUEUED, or FETCHING. Do not join finished or cancelled queries because the accelerator reuses backend session IDs once a query completes and the join results could be wrong.
Step 1 — Create the Query History Table in Db2 z/OS
Run the following DDL in Db2 for z/OS to create the database, table, and index:
CREATE DATABASE SAMPDB;
CREATE TABLE ACCEL.QUERY_HISTORY_TABLE2(
planID BIGINT NOT NULL,
ACCELERATOR VARCHAR(128),
USER CHAR(8),
SESSIONID BIGINT,
PRODUCTID CHAR(8),
CLIENTUSER CHAR(8),
WORKSTATION CHAR(18),
APPLICATION CHAR(20),
LOCATIONNAME CHAR(16),
CONNNAME CHAR(8),
CONNTYPE CHAR(8),
CORRID CHAR(12),
AUTHID CHAR(8),
PLANNAME CHAR(8),
ACCOUNTING VARCHAR(201),
SUBSYSTEMID CHAR(8),
STATE CHAR(20),
SUBMITTIMESTAMP TIMESTAMP WITH TIMEZONE,
WAITTIMESEC DOUBLE,
PREPARETIMESEC DOUBLE,
APPLICATIONSTALLTIMESEC DOUBLE,
FETCHTIMESEC DOUBLE,
CPUTIMESEC DOUBLE,
ELAPSEDTIMESEC DOUBLE,
PRIORITY CHAR(20),
RESULTROWS BIGINT,
RESULTBYTES BIGINT,
ERRORDESCRIPTION VARCHAR(256),
TASK BIGINT,
SQLTEXT CLOB(32M)
) CCSID UNICODE IN DATABASE SAMPDB;
CREATE INDEX ACCEL.XSUBMIT2
ON ACCEL.QUERY_HISTORY_TABLE2
(SUBMITTIMESTAMP, ACCELERATOR);
Step 2 — Create the Stored Procedure to Populate History
The stored procedure ACCEL.GET_QUERY_HISTORY2 calls SYSPROC.ACCEL_GET_QUERIES2 and inserts parsed XML rows into ACCEL.QUERY_HISTORY_TABLE2. Refer to IBM support pages for more details: https://www.ibm.com/support/pages/how-store-query-history-db2-zos-table
Below are code snippets from the previously mentioned IBM support page.
-- Create or replace the history loader stored procedure
DROP PROCEDURE ACCEL.GET_QUERY_HISTORY2@
CREATE PROCEDURE ACCEL.GET_QUERY_HISTORY2(
IN ACCEL VARCHAR(128),
OUT RETURN_MESSAGE CLOB(64K))
VERSION V1
LANGUAGE SQL
ISOLATION LEVEL CS
BEGIN
-- Calls SYSPROC.ACCEL_GET_QUERIES2 and inserts results into ACCEL.QUERY_HISTORY_TABLE2
-- (Full listing omitted for brevity; use the IBM sample as provided in the support doc.)
END@
-- Example invocation
CALL ACCEL.GET_QUERY_HISTORY2('ACCEL1', CAST(? AS CLOB(64K)));
-- Schedule guidance: run every 10–20 minutes to keep history current.
Step 3 — Create the REXX Job to Populate the History Table
More code snippets from the IBM support page.
//GETQHIST JOB (ACCT),'GET QUERY HISTORY',
// CLASS=A,MSGCLASS=X,NOTIFY=&SYSUID
//*
//STEP1 EXEC PGM=IKJEFT01
//STEPLIB DD DSN=DB2.RUNLIB.LOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
%GETQHIST DB2A ACCEL1
/*
/* REXX */
PARSE ARG DBSSID ACCELNAME
ADDRESS TSO 'SUBCOM DSNREXX'
IF RC <> 0 THEN CALL RXSUBCOM 'ADD','DSNREXX','DSNREXX'
ADDRESS DSNREXX 'CONNECT' DBSSID
AcceleratorName = ACCELNAME
MsgInd = 1
MessageString = left(' ',1000000)
ADDRESS DSNREXX
'EXECSQL CALL ACCEL.GET_QUERY_HISTORY2 (:AcceleratorName,:MessageString INDICATOR :MsgInd)'
ADDRESS DSNREXX 'DISCONNECT'
EXIT
…
Step 4 — Verify Data Population of Monitoring Tables
SELECT *
FROM ACCEL.QUERY_HISTORY_TABLE2
FETCH FIRST 20 ROWS ONLY;
This should deliver some results from the QUERY_HISTORY_TABLE2.
Step 5 — Create the Monitoring AoT Proxy Table (if not already created)
Customize your environment per IBM documentation, then create the proxy table on Db2/z and validate data:
-- Create the proxy table mapped to the accelerator AoT activity snapshot
CREATE TABLE <creator>.ACCEL_ACTIVITY_TABLE (
ACTIVITY_TIMESTAMP TIMESTAMP NOT NULL,
APPLICATION_HANDLE BIGINT NOT NULL,
STATEMENT_HASH BIGINT NOT NULL,
EVENT_STATE VARCHAR(32),
TEMPSPACE_MB BIGINT,
TOTAL_IO BIGINT,
TOTAL_CPU_TIME BIGINT,
MEMORY_POOL_USED_BYTES BIGINT,
QUERY_ACTUAL_DEGREE INTEGER,
UOW_START_TIME_SEC DOUBLE)
IN DATABASE "<dbname>"
CCSID UNICODE IN ACCELERATOR <accel>;
-- Verify AoT table content
SET CURRENT ACCELERATOR = 'ACCEL1';
SET CURRENT QUERY ACCELERATION ALL;
SELECT *
FROM SYSACCEL.SYSMONITOR_ACCEL_ACTIVITY_TABLE
FETCH FIRST 20 ROWS ONLY;
In IDAA V8, the SYSMONITOR_ACCEL_ACTIVITY_TABLE pre-exists on IDAA and data is automatically populated. User needs to create only the proxy table on Db2/z.
Step 6 — Join Activity Table with Query History
Add ACCEL.QUERY_HISTORY_TABLE2 to the accelerator. Load the table.
Now Join SYSACCEL.SYSMONITOR_ACCEL_ACTIVITY_TABLE with ACCEL.QUERY_HISTORY_TABLE2.
SELECT *
FROM SYSACCEL.SYSMONITOR_ACCEL_ACTIVITY_TABLE A
JOIN ACCEL.QUERY_HISTORY_TABLE2 H
ON A.APPLICATION_HANDLE = H.SESSIONID
WHERE H.STATE IN ('RUNNING','QUEUED','FETCHING')
ORDER BY A.TIMESTAMP DESC;
Step 7 — Create a Reusable Monitoring View (Optional)
CREATE VIEW ACCEL.V_ACCEL_QUERY_RESOURCE_USAGE AS
SELECT
A.TIMESTAMP,
A.APPLICATION_HANDLE AS SESSION_ID,
A.QUERY_ACTUAL_DEGREE,
H.USER,
H.STATE,
H.CPUTIMESEC,
H.ELAPSEDTIMESEC,
A.TEMPSPACE_MB,
H.RESULTROWS,
H.SQLTEXT
FROM SYSACCEL.SYSMONITOR_ACCEL_ACTIVITY_TABLE A
JOIN ACCEL.QUERY_HISTORY_TABLE2 H
ON A.APPLICATION_HANDLE = H.SESSIONID
WHERE H.STATE IN ('RUNNING','QUEUED','FETCHING');
-- Usage
SELECT *
FROM ACCEL.V_ACCEL_QUERY_RESOURCE_USAGE
ORDER BY TIMESTAMP DESC;
Step 8: Get Insight — Example Code
Top 10 active queries by memory consumption:
SELECT
A.TIMESTAMP,
A.APPLICATION_HANDLE,
H.SESSIONID,
H.USER,
H.STATE,
H.CPUTIMESEC,
H.ELAPSEDTIMESEC,
A.MEMORY_POOL_USED_BYTES,
DECIMAL(A.MEMORY_POOL_USED_BYTES/1024/1024,15,2) AS MEMORY_MB,
A.TEMPSPACE_MB,
H.RESULTROWS,
H.SQLTEXT
FROM SYSACCEL.SYSMONITOR_ACCEL_ACTIVITY_TABLE A
JOIN ACCEL.QUERY_HISTORY_TABLE2 H
ON A.APPLICATION_HANDLE = H.SESSIONID
WHERE H.STATE IN ('RUNNING','QUEUED','FETCHING')
ORDER BY A.MEMORY_POOL_USED_BYTES DESC
FETCH FIRST 10 ROWS ONLY;
-- Variants:
-- Top temp space consumption: ORDER BY TEMPSPACE_MB DESC
-- Highest CPU consumption (active): ORDER BY CPUTIMESEC DESC
Operational Recommendations
- Run history extraction every 10 - 20 minutes.
- The activity table refresh interval is approximately 1 minute.
- Always filter states to RUNNING, QUEUED, FETCHING; session IDs are reused after completion and can lead to incorrect joins if not filtered.
- Make sure activity_start_time is between backend_execution_start_time (or prepare_start_time) and backend_execution_finish_time after the join.
Summary
This monitoring pattern correlates live resource consumption from the activity snapshot with historical execution metrics to provide insight of CPU, elapsed time, temp space, and memory usage for active accelerator queries.
References
More v8 monitoring options via AoT tables: https://www.ibm.com/docs/en/daafz/8.1?topic=tasks-displaying-monitoring-tables
Creating and using query history table: https://www.ibm.com/support/pages/how-store-query-history-db2-zos-table
Contribution
This blog is contributed by
Huiyan Roy, IBM Db2 Analytics Accelerator L3 Technical Lead, IBM Ehningen Lab, Germany
CJ Chang, IBM Db2 Analytics Accelerator Development Team Lead, IBM SVL Lab, US