SYSPROC.ACCEL_GET_QUERIES2

Details

The input XML string allows you to specify filtering conditions, determine the result order, and limit the number of results. Multiple filter conditions can be specified; a result is returned only if all conditions match.

In contrast to its predecessor, SYSPROC.ACCEL_GET_QUERIES2 returns all SQL statements in full length.

The SYSPROC.ACCEL_GET_QUERIES2 stored procedure returns only the queries that were run on the specified accelerator.

Syntax

CALL SYSPROC.ACCEL_GET_QUERIES2
(accelerator_name,
query_selection,
message);

Options description

accelerator_name

The unique name of the accelerator. This accelerator must have been defined by the SYSPROC.ACCEL_ADD_ACCELERATOR2 stored procedure.

query_selection

An XML input string containing the filter conditions to be used. The string must conform to the structure of the querySelection element in the SAQTSAMP(AQTSXSD1) data set.

Example:

<?xml version="1.0" encoding="UTF-8" ?>
<dwa:querySelection 
 xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.0">
	<!-- filter conditions are combined with AND -->
	<filter scope="completed"
		fromTimestamp="2010-01-01T10:45:00Z" 
    toTimestamp="2010-01-01T12:45:00Z"
    minElapsedTimeSec="120" 
    minExecutionTimeSec="60"
    user="BJONES" />
	<result order="elapsedTime" maxRows="100"/>
</dwa:querySelection>

message

For the description, follow the appropriate link under Related reference at the end of this topic.

Result sets

The stored procedure returns three result sets.

First result set (QUERY_INFO_CURSOR)

This result set includes an XML string that contains the query information that was extracted from the accelerator. The structure of this string conforms to that of the queryList element in the SAQTSAMP(AQTSXSD1) data set.

The result set is identified by a cursor named QUERY_INFO_CURSOR. The result set rows consist of the following columns:

1. SEQID INTEGER
2. QUERYINFO VARCHAR(32698)

The information in the QUERYINFO column is concatenated in ascending order of the values in the SEQID column. The column information is encoded in UTF-8.

Second result set (SP_TRACE_CURSOR)

Depending on the trace configuration in the message input parameter, this result set is empty or contains trace information about the stored procedure execution. It is identified by the cursor SP_TRACE_CURSOR and contains the following columns:

• SEQID of type INTEGER
• TRACEDATA of type VARBINARY, with a maximum length of 32698 characters

The information in the TRACEDATA column is encoded in UTF-8. It is intended for analysis by IBM support. If the length of a value exceeds 32698 characters, it is split into multiple result set rows. The rows are concatenated in ascending order of SEQID.

Third result set (MESSAGES_CURSOR)

This result set contains an XML string that contains the same messages as the MESSAGE output parameter. In contrast to the MESSAGE output parameter, the result set does not have a 64 KB size limitation. Therefore, it always contains the whole set of <message> elements (no truncation). The structure of the XML string conforms to that of the messageOutput element in the SAQTSAMP(AQTSXSD1) data set. The result set is identified by a cursor named MESSAGES_CURSOR and contains the following columns:

• SEQID of type INTEGER
• MESSAGES of type VARBINARY, with a maximum length of 32698 characters

The rows in the result set are concatenated in ascending order of the values in the SEQID column. The information in the MESSAGES column is encoded in UTF-8.

Fourth result set

Each cell in the QUERY column contains query information in XML format. The information is provided in the form of XML attributes. These belong to the <query> element.

The <query> element has the following attributes:

applicationStallTime

This is the total time a query had to wait for its execution. The end of this period is marked by the return of the result set that was generated from the open query. This is when the query-submitting application became inactive, which is known as the application stall time.

prepareTimeSec

The time in seconds that was needed to prepare a SQL statement before the corresponding query could be processed.

sessionID

A number that identifies the session with the accelerator during which a query was processed.

timeoutsHTAP

The number of times queries had to be delayed because the WAITFORDATA delay period expired. This value is provided only if the accelerator was already configured to collect delay information related to WAITFORDATA settings.

waitTimeHTAPSec

The time a WAITFORDATA query had to wait for the completion of the next data replication process until it could be routed to the backend database on the accelerator. This value is provided only if the accelerator was already configured to collect delay information related to WAITFORDATA settings.

Prerequisites

• For the user ID that calls the stored procedures on z/OS, you must define an OMVS segment in the Resource Access Control Facility (RACF®).
• The accelerator_name parameter must specify an accelerator name that is listed in the catalog tables of the product.

Authorizations for z/OS

On z/OS, the user ID under which this stored procedure is run must have the following privileges:

• EXECUTE on the stored procedure
• MONITOR1 authorization
• Read/write and execute access to the /tmp directory for the user who calls the stored procedure
• RACF ACCESS(READ) on the data set that contains the AQTENV file in the started task procedure of the Workload Manager (WLM) environment.
• RACF ACCESS(READ) on the data set that contains the AQTDEF6 file in the started task procedure of the Workload Manager (WLM) environment.
• EXECUTE on the DSNADM.DSNADMIZ package to allow access to system parameters when the SYSPROC.ADMIN_INFO_SYSPARM stored procedure is called.