Capture Query Environment and Performance data
In this blog we will discuss about the Capture Query environment and performance data functionality that is supported by IBM Db2 Analytics Accelerator for z/OS (IDAA) to gather query environment information to help understand and address query performance issues. With query acceleration being the primary business value proposition for IDAA it is always important to understand the environment in which the query is executing to help achieve optimal query performance .  The need for a robust means to gather information about the environment in which queries execute is amplified in the case where complex, resource intensive, analytical queries are being executed on the IDAA. 
 
Since versions 7.5.1 and 7.5.3 of the IDAA server, this new capability to allow Capturing query environment and performance data has been supported. As you may be aware, IDAA uses a Db2 Warehouse columnar engine as the datastore. When eligible for acceleration, Db2 for z/OS sends the queries over to IDAA for execution. In case of performance issues, collection of query environment and performance data can be enabled. When enabled, the queries need to be re-executed under the covers to gather the necessary information.  
 
Initiating the data capture can be done by invoking the ACCEL_CONTROL_ACCELERATOR with the following inputs or using the existing Data Studio interface to administering IDAA. 
 
202d8500-799a-11ea-9335-4a9eeca002cf
 
Since the query is executed under the covers and may be long-running, the timeout value here allow users to control how much longer the query will be allowed to execute to capture environment data.  This execution of the query along with performance tracing enabled will utilize Db2 Warehouse resources and may potentially impact performance of other IDAA operations, depending on the nature of the query.

52d77d80-799a-11ea-933b-06b5be6c0564
 
 
Once the capture has been initiated, saving the trace with the option to also gather the Manually collected trace information will combine the query and performance trace data as part of the downloaded traces to allow users to analyze and share with the support teams for further analysis. 
 
This doc along with the default IDAA trace can help provide actionable steps to resolve the query performance problem. Some examples include, but are not limited to the following:
 
This can also be automated in a batch process using the sample stored procedure ACCEL.GET_QUERY_HISTORY2 How to store Db2 query history in a Db2 for zOS table . With GET_QUERY_HISTORY2 running at a set interval, i.e. every 5 minutes, a batch application can set a threshold for total elapsed time. For queries that exceed this threshold call ACCEL_CONTROL_ACCELERATOR automatically to collect the query environment data and performance docs. 
 
<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011"
 version="1.0">
    <getAdditionalSupport planID="0" traceCommand="CDE_PERFORMANCE_TRACE"
     timeoutInMinutes="0"/>
</aqt:controlCommand >


Then at another interval (every 24 hours), call ACCEL_CONTROL_ACCELERATOR to save the trace . 
 
<?xml version="1.0" encoding="UTF-8" ?>
<aqt:controlCommand xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
 version="1.0">
    <getTraceData keepConfiguration="true">
        <content>ACCELERATOR</content>
        <content>ADDITIONALSUPPORTINFO </content>
    </getTraceData>
</aqt:controlCommand>
 
NoteDocuments saved from capture query environment and performance data are automatically deleted on the accelerator after 7 days.
E-mail me when people leave their comments –

Sowmya Kameswaran works at the IBM Silicon Valley Labs in San Jose California and leads the User Experience modernization for Db2z platform and ecosystem. Understanding the way users interact with the system and building meaningful interfaces to provide the best user experience is what she enjoys to do. Being hands-on into software development helps her go end to end from understanding the kind of experience customers need to getting it built.

You need to be a member of WorldofDb2 to add comments!

Join WorldofDb2