How to store the query history in a Db2 for z/OS table

------------------------
-- DDL - run only once
-------------------------
DROP DATABASE SAMPDB@
CREATE DATABASE SAMPDB@
DROP TABLE ACCEL.QUERY_HISTORY_TABLE2@

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 BIGINT,
fetchTimeSec DOUBLE,
cpuTimeSec BIGINT,
elapsedTimeSec DOUBLE,
priority CHAR(20),
resultRows BIGINT,
resultBytes BIGINT,
errorDescription VARCHAR(256),
task BIGINT,
sqltext CLOB(32M) INLINE LENGTH 1024) CCSID UNICODE IN DATABASE SAMPDB@

CREATE INDEX ACCEL.XSUBMIT2
ON ACCEL.QUERY_HISTORY_TABLE2(SUBMITTIMESTAMP, ACCELERATOR ASC)@


DROP PROCEDURE ACCEL.GET_QUERY_HISTORY2@

CREATE PROCEDURE ACCEL.GET_QUERY_HISTORY2(IN ACCEL VARCHAR(128),
OUT RETURN_MESSAGE CLOB(64K))
VERSION V1
ISOLATION LEVEL CS
DATE FORMAT ISO
TIME FORMAT ISO
LANGUAGE SQL
DISABLE DEBUG MODE
SP: BEGIN

-- VARIABLE DECLARATION
DECLARE RETURN_TEXT VARCHAR(70); -- CONTAINS THE MESSAGE TEXT OF THE ERROR ENCOUNTERED RETURNED TO THE CALLER
DECLARE ALREADY_EXIST INTEGER; -- CONTAINS THE RESULTS OF CHECK STATEMENTS

DECLARE FILTER_XML CLOB(1K) CCSID UNICODE DEFAULT '<?xml version="1.0" encoding="UTF-8"?> <dwa:querySelection xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">'; -- INPUT PARAMETER for ACCEL_GET_QUERIES2 SP

DECLARE FILTER_INTERVAL CLOB(200) CCSID UNICODE;
DECLARE RESULT_XML CLOB(128M) CCSID UNICODE; -- OUTPUT PARAMETER for ACCEL_GET_QUERIES2 SP


-- internal variable for resultsets locator
DECLARE QUERY_INFO_LOCATOR RESULT_SET_LOCATOR VARYING;
-- internal variables for QUERY_INFO_CURSOR
DECLARE QUERY_INFO_SEQID INTEGER;
DECLARE QUERY_INFO_XML CLOB(32K) CCSID UNICODE;


-- INOUT PARAMETER PASSED TO ALL IBM DB2 ANALYTICS ACCELERATOR STORED PROCEDURES CALLED
DECLARE MESSAGE_XML CLOB(64K) DEFAULT '<?xml version="1.0" encoding="UTF-8" ?><dwa:messageControl xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.2"><compatibilityLevel>77</compatibilityLevel><language>en_US</language></dwa:messageControl>';

DECLARE FILTER_FROM TIMESTAMP WITH TIMEZONE;
DECLARE FILTER_TO TIMESTAMP WITH TIMEZONE;

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE RECEIVED_SOMETHING INTEGER DEFAULT 0;

-- HANDLER DECLARATION AND MANAGEMENT FOR IBM DB2 ANALYTICS ACCELERATOR STORED PROCEDURES CALL
-- DEFINING WARNING CONDITION THROWABLE BY IBM DB2 ANALYTICS ACCELERATOR STORED PROCEDURE
DECLARE AQTERR VARCHAR(10) DEFAULT 'UNKNOWN'; -- EVENTUALLY CONTAINS IBM DB2 ANALYTICS ACCELERATOR STORED PROCEDURE REASON CODE ERROR
DECLARE IDAAERRCODE VARCHAR(500);
DECLARE IDAAWARN1 CONDITION FOR SQLSTATE VALUE '01H08';
DECLARE IDAAWARN2 CONDITION FOR SQLSTATE VALUE '01H04';
DECLARE IDAAOK SMALLINT DEFAULT 0; -- CONTAINS THE SUCCESSFUL/UNSUCCESSFUL EXECUTION OF IDAAPART COMPUND STATEMENTS

DECLARE c1 CURSOR FOR SELECT ACCELERATORNAME FROM SYSACCEL.SYSACCELERATORS WHERE ACCELERATORNAME LIKE ACCEL FOR FETCH ONLY WITH UR;

DECLARE EXIT HANDLER FOR IDAAWARN1, IDAAWARN2

BEGIN
GET DIAGNOSTICS CONDITION 1 IDAAERRCODE = MESSAGE_TEXT;
SET RETURN_MESSAGE = MESSAGE_XML;
SET ALREADY_EXIST = LOCATE('AQT',IDAAERRCODE);
IF ALREADY_EXIST IS NULL OR ALREADY_EXIST = 0 THEN
SET AQTERR = 'UNKNOWN';
ELSE
SET AQTERR = SUBSTR(IDAAERRCODE,ALREADY_EXIST,9);
END IF;
SET IDAAOK = -1;
END;

SET QUERY_INFO_XML = NULL; -- return all tables
SET RESULT_XML = '';

----------------------------
-- CHECK INPUT PARAMETERS
----------------------------

-- CHECK IF THE INPUT PARAMETER ACCEL REALLY EXISTS AS ACCELERATOR
SELECT COUNT(*) INTO ALREADY_EXIST
FROM SYSACCEL.SYSACCELERATORS
WHERE ACCELERATORNAME=ACCEL WITH UR;
IF ALREADY_EXIST = 0 THEN
IF ACCEL IS NOT NULL THEN
SET RETURN_TEXT = 'ERRC 001 - ACCELERATOR "'|| ACCEL ||'" IS AN UNDEFINED NAME';
ELSE
SET RETURN_TEXT = 'ERRC 001 - ACCELERATOR "NULL" IS AN UNDEFINED NAME';
END IF;
SIGNAL SQLSTATE VALUE '7UA00' SET MESSAGE_TEXT=RETURN_TEXT;
END IF;

-- set filter timestamps
SET FILTER_FROM = (SELECT CHAR(DATE(MAX(SUBMITTIMESTAMP))) CONCAT 'T' CONCAT CHAR(TIME(MAX(SUBMITTIMESTAMP + 1 SECOND)),JIS) CONCAT 'Z' FROM ACCEL.QUERY_HISTORY_TABLE2 WHERE ACCELERATOR=ACCEL);
SET FILTER_TO = (SELECT CHAR(DATE(CURRENT TIMESTAMP + 2 DAY)) CONCAT 'T00:00:00Z' from SYSIBM.SYSDUMMY1);
IF FILTER_FROM IS NULL THEN
  SET FILTER_FROM = '2000-01-01T00:00:00Z';
END IF;


----------------------------
-- GET QUERIES FROM HISTORY
----------------------------
-- -- fromTimestamp="2010-01-01T10:45:00Z"
-- toTimestamp="2010-01-01T12:45:00Z"
SET FILTER_INTERVAL = xmlserialize(
xmlelement(NAME "filter",
xmlattributes(
'all' as "scope",
FILTER_FROM as "fromTimestamp",
FILTER_TO as "toTimestamp")) AS CLOB(200));
SET FILTER_XML = FILTER_XML ||FILTER_INTERVAL||'</dwa:querySelection>'; 

--Prepare and CALL SYSPROC.ACCEL_GET_QUERIES2(ACCEL,FILTER_XML,MESSAGE_XML);
CALL SYSPROC.ACCEL_GET_QUERIES2(ACCEL,FILTER_XML,MESSAGE_XML);

IF IDAAOK = 0 THEN
SET RETURN_MESSAGE = 'ERRC 000 - SUCCESSFUL EXECUTION';
ELSE
SIGNAL SQLSTATE VALUE '01UA0' SET MESSAGE_TEXT= 'ERRC 100 - ACCEL_GET_QUERIES2 ERROR: '|| AQTERR ||'. MORE DETAILS IN OUTPUT PARAMETER';
END IF;

--------------------------------------------------------------
-- FETCH QUERY_INFO_CURSOR RESULTSET of ACCEL_GET_QUERIES2 SP
-- PARSE OUTPUT XML and WRITE INTO TABLE
--------------------------------------------------------------
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
-- do not append the content again!
LEAVE fetch_loop;
-- it may be necessary to check for other SQL codes
ELSE
-- append fetched content to temp variable
SET RECEIVED_SOMETHING = 1;
set RESULT_XML = CONCAT(RESULT_XML, QUERY_INFO_XML);
END IF;

END LOOP fetch_loop;

CLOSE QUERY_INFO_CURSOR;

----------------------------------------
-- PARSE OUTPUT XML and WRITE INTO TABLE
----------------------------------------
IF RECEIVED_SOMETHING != 0 THEN

--format the return
INSERT INTO ACCEL.QUERY_HISTORY_TABLE2 (ACCELERATOR,
planID,
user,
sessionID,
productID,
clientUser,
workstation,
application,
locationName,
connName,
connType,
corrID,
authID,
planName,
accounting,
subSystemID,
state,
submitTimestamp,
waitTimeSec,
fetchTimeSec,
cpuTimeSec,
elapsedTimeSec,
priority,
resultRows,
resultBytes,
errorDescription,
task,
sqltext)
select ACCEL AS ACCELERATOR, X.* from
(select xmlparse(document result_xml) as history_xml from sysibm.sysdummy1) P,
XMLTABLE(
XMLNAMESPACES('http://www.ibm.com/xmlns/prod/dwa/2011' AS "dwa"),
'$d/dwa:queryList/query' passing p.history_xml as "d"
COLUMNS
planID BIGINT PATH '@planID',
user CHAR(8) PATH '@user',
sessionID BIGINT PATH '@sessionID',
productID CHAR(8) PATH 'clientInfo/@productID',
clientUser CHAR(8) PATH 'clientInfo/@user',
workstation CHAR(18) PATH 'clientInfo/@workstation',
application CHAR(20) PATH 'clientInfo/@application',
locationName CHAR(16) PATH 'clientInfo/@locationName',
connName CHAR(8) PATH 'clientInfo/@connName',
connType CHAR(8) PATH 'clientInfo/@connType',
corrID CHAR(12) PATH 'clientInfo/@corrID',
authID CHAR(8) PATH 'clientInfo/@authID',
planName CHAR(8) PATH 'clientInfo/@planName',
accounting VARCHAR(201) PATH 'clientInfo/@accounting',
subSystemID CHAR(8) PATH 'clientInfo/@subSystemID',
state CHAR(20) PATH 'execution/@state',
submitTimestamp timestamp with timezone PATH 'execution/@submitTimestamp',
waitTimeSec BIGINT PATH 'execution/@waitTimeSec',
fetchTimeSec DOUBLE PATH 'execution/@fetchTimeSec',
cpuTimeSec BIGINT PATH 'execution/@cpuTimeSec',
elapsedTimeSec DOUBLE PATH 'execution/@elapsedTimeSec',
priority CHAR(20) PATH 'execution/@priority',
resultRows BIGINT PATH 'execution/@resultRows',
resultBytes BIGINT PATH 'execution/@resultBytes',
errorDescription VARCHAR(256) PATH 'execution/@errorDescription',
task BIGINT PATH 'task/@id',
sqltext CLOB(32M) PATH 'sql'
) AS X;

commit;

END IF;


END SP
@

CALL ACCEL.GET_QUERY_HISTORY2('ACCEL1', cast(? as CLOB(64K)))@

select * from ACCEL.QUERY_HISTORY_TABLE2@

<jobcard>
//*********************************************************************
//*    Sample REXX Job to call ACCEL.GET_QUERY_HISTORY2
//*
//*    LICENSED MATERIALS - PROPERTY OF IBM
//*    5697-DA7
//*    (C) COPYRIGHT IBM Corp. 2023.
//*                                  
//* US Government Users Restricted Rights
//* Use, duplication or disclosure restricted by GSA ADP Schedule
//* Contract with IBM Corporation
//*
//* DISCLAIMER OF WARRANTIES :
//* Permission is granted to copy and modify this  Sample code provided
//* that both the copyright  notice,- and this permission notice and
//* warranty disclaimer  appear in all copies and modified versions.
//*
//* THIS SAMPLE CODE IS LICENSED TO YOU AS-IS.
//* IBM  AND ITS SUPPLIERS AND LICENSORS  DISCLAIM ALL WARRANTIES,
//* EITHER EXPRESS OR IMPLIED, IN SUCH SAMPLE CODE, INCLUDING THE
//* WARRANTY OF NON-INFRINGEMENT AND THE IMPLIED WARRANTIES OF
//* MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT
//* WILL IBM OR ITS LICENSORS OR SUPPLIERS BE LIABLE FOR ANY DAMAGES
//* ARISING OUT OF THE USE OF OR INABILITY TO USE THE SAMPLE CODE OR
//* COMBINATION OF THE SAMPLE CODE WITH ANY OTHER CODE. IN NO EVENT
//* SHALL IBM OR ITS LICENSORS AND SUPPLIERS BE LIABLE FOR ANY LOST
//* REVENUE, LOST PROFITS OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL,
//* CONSEQUENTIAL,INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND
//* REGARDLESS OF THE THEORY OF LIABILITY,-, EVEN IF IBM OR ITS
//* LICENSORS OR SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH
//* DAMAGES.
//*
//*  Function =
//*   SAMPLE REXX JOB TO CALL GET_QUERY_HISTORY2. STORED PROCEDURE INFO:
//*   (https://www.ibm.com/support/pages/how-store-query-
//*   history-db2-zos-table)
//*    
//*    CAUTION: ONLY EXPERIENCED USERS SHOULD USE THIS PROCEDURE.
//*             READ THE DESCRIPTION OF EACH STEP CAREFULLY!
//*             IF NOT USED PROPERLY, A DATA LOSS MIGHT OCCUR!
//*
//*  Notes =
//*    PRIOR TO RUNNING THIS JOB, customize it for your system:
//*    (1) Add a valid job card.
//*    (2) Locate and change all occurrences of the following strings
//*        as indicated:
//*        (A) !REXXDSN!        TO THE DATASET FOR THE REXX FILE
//*        (B) !REXNAME!        TO THE NAME OF THE REXX FILE
//*        (C) !SSID!           TO THE DB2 SUBSYSTEM NAME
//*        (D) !ACCEL!          TO THE ACCELERATOR NAME
//*        (E) !STEPLIB!        TO THE STEPLIB DATASET FOR DSNREXX
//*
//*  Change Activity =
//********************************************************************         
//*  STEP 1:  Copy this REXX pgm to a temp PDS                                  
//********************************************************************          
//COPYREXX  EXEC PGM=IEBGENER,COND=(4,LT)                                        
//SYSPRINT DD  SYSOUT=*                                                         
//SYSUDUMP DD  SYSOUT=*                                                         
//SYSIN    DD  DUMMY                                                            
//SYSUT2   DD  DSNAME=!REXXDSN!(!REXNAME!),DISP=SHR                       
//SYSUT1  DD   *                                                                
 /* REXX */
 /* ACCEL.GET_QUERY_HISTORY2 into a table */
 TRACE OFF                                                                      
 PARSE ARG DBSSID ACCELNAME                                                           
 
 ADDRESS TSO 'SUBCOM DSNREXX'
 IF RC <> 0 THEN   SUBCC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
 
 /* Connect to Db2z subsystem*/
 ADDRESS DSNREXX 'CONNECT' DBSSID                                           
 
 SAY 'Get queries for: ' ACCELNAME
 
 /* Get input for GET_QUERY_HISTORY2 stored procedure */
 AcceleratorName = ACCELNAME
 
 MsgInd = 1
 
 /* Message from any errors from the stored procedure */
 MessageString = left(' ',1000000)

 /* CALL GET_QUERY_HISTORY2 stored procedure */
 Address DSNREXX 'EXECSQL CALL ACCEL.GET_QUERY_HISTORY2 (',
                ':AcceleratorName,',
                ':MessageString INDICATOR :MsgInd ) '

 /* Check GET_QUERY_HISTORY2 for messages */
 if SQLCODE <> 0 then
      do
        SQLERRORPOSITION = 'Call GET_QUERY_HISTORY2 Stored Procedure';
        if MsgInd >= 0 then
          say 'Message:' MessageString;
        call SQLERRORROUTINE
      end
      else
      do
        say 'Successful Call of GET_QUERY_HISTORY2 Stored Procedure.', 
            'Check Message'
        if MsgInd >= 0 then 
            say 'Message:' MessageString;
      end     
 
 /* Disconnect from Db2z subsystem */
 ADDRESS DSNREXX 'DISCONNECT'
 
 EXIT                                 
 
 /* Error message processing */
 SQLERRORROUTINE:
  SAY 'POSITION   = ' SQLERRORPOSITION
  SAY 'SQLCODE    = ' SQLCODE
  SAY 'SQLSTATE   = ' SQLSTATE
  SAY 'SQLERRP    = ' SQLERRP
  SAY 'TOKENS     = ' TRANSLATE(SQLERRMC,',','FF'X)
  SAY 'SQLERRD.1  = ' SQLERRD.1
  SAY 'SQLERRD.2  = ' SQLERRD.2
  SAY 'SQLERRD.3  = ' SQLERRD.3
  SAY 'SQLERRD.4  = ' SQLERRD.4
  SAY 'SQLERRD.5  = ' SQLERRD.5
  SAY 'SQLERRD.6  = ' SQLERRD.6
  SAY 'SQLWARN.0  = ' SQLWARN.0
  SAY 'SQLWARN.1  = ' SQLWARN.1
  SAY 'SQLWARN.2  = ' SQLWARN.2
  SAY 'SQLWARN.3  = ' SQLWARN.3
  SAY 'SQLWARN.4  = ' SQLWARN.4
  SAY 'SQLWARN.5  = ' SQLWARN.5
  SAY 'SQLWARN.6  = ' SQLWARN.6
  SAY 'SQLWARN.7  = ' SQLWARN.7
  SAY 'SQLWARN.8  = ' SQLWARN.8
  SAY 'SQLWARN.9  = ' SQLWARN.9
  SAY 'SQLWARN.10 = ' SQLWARN.10

  ADDRESS DSNREXX 'EXECSQL ROLLBACK'

  IF SQLCODE <> 0 THEN
    DO
      SAY 'ROLLBACK SQLCODE : ' SQLCODE
    END
  
  EXIT  8                                                                         
/*                                                                              
//********************************************************************          
//*  STEP 2:  Execute the rexx program                                          
//********************************************************************          
//RUNREXX  EXEC PGM=IKJEFT01,REGION=0M,DYNAMNBR=20                              
//STEPLIB  DD DSN=!STEPLIB!,DISP=SHR                                                                      
//SYSTSPRT DD SYSOUT=*                                                          
//SYSPRINT DD SYSOUT=*                                                          
//SYSEXEC  DD DISP=SHR,DSN=!REXXDSN!                                     
//SYSTSIN  DD *                                                                 
%!REXNAME! !SSID! !ACCEL!                                                                  
/*

DDL change, sessionID column was added - this requires accelerator and stored  procedures to be at maintenance level 7.5.8 or later.

• Executing stored proccedure SYSPROC.ACCEL_GET_QUERIES2  results in
  DSNT408I SQLCODE = -413, ERROR:  OVERFLOW OR UNDERFLOW OCCURRED DURING NUMERIC
           DATA TYPE CONVERSION
  The message "SQL0413N Overflow occurred during numeric data type conversion" is due to one of the columns in the QUERY_HISTORY_TABLE2. Several columns have INTEGER when the corresponding values returned from the
  accelerator server are uint64/BIGINT. There is also an INTEGER column where the server value is DOUBLE.

  The DDL of ACCEL.QUERY_HISTORY_TABLE2 has been adapted.
  The following columns have been changed:

  planID INTEGER NOT NULL, --> planID BIGINT NOT NULL,
  waitTimeSec INTEGER, --> waitTimeSec BIGINT,
  fetchTimeSec INTEGER, --> fetchTimeSec DOUBLE,
  cpuTimeSec INTEGER, --> cpuTimeSec BIGINT,
  elapsedTimeSec INTEGER, --> elapsedTimeSec DOUBLE,
  task INTEGER, --> task BIGINT,

  The INSERT statement has been adjusted accordingly:

  INSERT INTO ACCEL.QUERY_HISTORY_TABLE2 (ACCELERATOR,...
  ...
  planID INTEGER PATH '@planID' --> planID BIGINT PATH '@planID'
  waitTimeSec INTEGER PATH 'execution/@waitTimeSec', --> waitTimeSec BIGINT PATH 'execution/@waitTimeSec',
  fetchTimeSec INTEGER PATH 'execution/@fetchTimeSec', --> fetchTimeSec DOUBLE PATH 'execution/@fetchTimeSec',
  cpuTimeSec INTEGER PATH 'execution/@cpuTimeSec', --> cpuTimeSec BIGINT PATH 'execution/@cpuTimeSec',
  elapsedTimeSec INTEGER PATH 'execution/@elapsedTimeSec', --> elapsedTimeSec DOUBLE PATH 'execution/@elapsedTimeSec',
  task INTEGER PATH 'task/@id', --> task BIGINT PATH 'task/@id',

• Correct settings:
  • DECLARE FILTER_XML, DECLARE QUERY_INFO_XML,
  • SET FILTER_FROM, SET FILTER_TO, IF FILTER_FROM IS NULL..., SET FILTER_XML