IBM Support

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

Education


Abstract

IBM Db2 Analytics Accelerator for z/OS maintains a history of the latest accelerated queries. This document
- explains how you can extract (copy) the query history from the accelerator and store it in Db2 for z/OS table. This is useful for performance analyses and cost distributions on the basis of usage percentages.
- describes how to create the ACCEL.GET_QUERY_HISTORY2 stored procedure which exploites the SYSPROC.ACCEL_GET_QUERIES2 stored procedure to extract the query history data and which stores those data in a Db2 for z/OS table.
Note:
The SYSPROC.ACCEL_GET_QUERIES2 stored procedure delivered with Accelerator V7 replaces the SYSPROC.ACCEL_GET_QUERIES procedure that was delivered with older product versions.
This is the updated document for product version 7 and higher. which replaces the SYSPROC.ACCEL_GET_QUERIES procedure that was delivered with older product versions.

Content

For more information about the newer stored procedure, see:
Your administration client can display the latest accelerated queries.
In IBM Db2 Analytics Accelerator Studio, you find this list of queries in the Query Monitoring section of the Accelerator view.
In IBM Data Server Manager, you access this list by following these steps:
  1. Select the relevant accelerator in the Accelerator view.
  2. Click Manage.
  3. Click the Query tab.
The following screen capture is taken from IBM Db2 Analytics Accelerator Studio:


The result set of the stored procedure is an XML document that contains a list of queries. If the XML document is larger than 32 KB, it is split into chunks of 32K and the result set contains multiple rows.

Some users prefer a table or a spreadsheet over an XML document. To save the XML output from SYSPROC.ACCEL_GET_QUERIES2 to a history table in Db2, you can use the sample SQL stored procedure ACCEL.GET_QUERY_HISTORY2 (which is defined in the sample at the end of this technote).

Using ACCEL.GET_QUERY_HISTORY2 you can export the query history to a Db2 table and then use this table for further analysis (for example, you can filter the table by user or export it to a spreadsheet).
Note: We use the suffix "2" in the procedure and table names to avoid a name conflict for users who have used an earlier version of this document.

The sample ACCEL.GET_QUERY_HISTORY2 stored procedure always copies the latest queries. When you invoke the stored procedure for the first time, you get all the queries that are kept in the query history. Each subsequent invocation outputs the queries that were added to the query history after the previous invocation. The queries are copied to the ACCEL.QUERY_HISTORY_TABLE2 table.

For example, if you invoke the sample stored procedure every 20 minutes, you can be reasonably sure that the ACCEL.QUERY_HISTORY_TABLE2 table contains the most recent queries.

If you prefer another name for the output table or the stored procedure, you can modify the sample and replace all occurrences of ACCEL.QUERY_HISTORY_TABLE2 and ACCEL.GET_QUERY_HISTORY2 with a name of your choice.

Note:
1. The following SQL uses the at-sign (@) as a statement termination character instead of the semicolon (;) because the CREATE PROCEDURE DDL statement contains multiple nested SQL statements that are terminated by a semicolon in the SQL body of the stored procedure.

If you want to run the SQL script in the SQL Script Editor of your administration client (IBM Data Studio or IBM Data Server Manager), make sure to use the at-sign as a statement termination character rather than a semicolon.
2. Some wider lines of the code presented in the following window seem to have been cut. However, this is not the case; there is a horizontal scroll-bar at the bottom of the window showing the code snippet supporting to show the content beyond the right border of the window.
3. The DDL shown below requires the accelerator and the stored procedures  to run at maintenance level 7.5.8 or later.
------------------------
-- 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@
Sample REXX procedure to call 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!                                                                  
/*
Troubleshooting:
The size of your SQL statement text in the monitored queries may vary. If the total size of all queries exceeds 128 MB, you may exceed the size of the variable result_xml, which is defined as a CLOB(128M). As a consequence you may see the following error message:
THE XML VALUE IS NOT A WELL-FORMED DOCUMENT WITH A SINGLE ROOT ELEMENT. SQLCODE=-20345, SQLSTATE=2200L
In this case you can increase the size of the result_xml variable or run the ACCEL_GET_QUERY_HISTORY2 stored procedure more frequently to reduce the number of queries that is returned with each invocation.
History of changes:
28 February 2024:
Provide a sample REXX procedure showing how to call ACCEL.QUERY_HISTORY_TABLE2
29 January 2024:
Amend DATE FORMAT ISO and TIME FORMAT ISO to CREATE PROCEDURE-statement to avoid SQLCODE -180.
30 November, 7 December 2023:

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

24 June 2022:
  • 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',
1 June 2021:
  • Correct settings:
    • DECLARE FILTER_XML, DECLARE QUERY_INFO_XML,
    • SET FILTER_FROM, SET FILTER_TO, IF FILTER_FROM IS NULL..., SET FILTER_XML
19 May 2021:
  • Superfluous semicolon removed from XMLNAMESPACES definition
  • Link to Knowledge-Center  replaced with link to IBM Documentation

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS4LQ8","label":"Db2 Analytics Accelerator for z\/OS"},"ARM Category":[{"code":"a8m0z0000000775AAA","label":"Db2 related products and functions-\u003EDb2 Analytics Accelerator for z\/OS"}],"ARM Case Number":"","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"7.5.x","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Product Synonym

IDAA

Document Information

Modified date:
28 February 2024

UID

swg27039739