Collecting host variables

Db2 Query Monitor allows you to collect, by sampling, the relative frequency of host variable combinations for SQL statements in workloads that you define. Follow these steps to collect host variables for SQL text of interest.

Before you begin

Before you collect host variables, verify the following:

  • The Db2 subsystems for which you want to collect of host variables are running Db2 Version 11 or 12.
  • You have the proper authority to use this feature.
  • A Host Variable Agent has been set up. A Host Variable Agent is a CAE Agent that coordinates the host variable collection process for one or more Query Monitor Subsystems on an LPAR.
  • You have created a connection to the staging table that is to hold the collected host variables. For more information, see Manage staging tables.

Other considerations:

  • Host variable samples are stored in staging tables for potentially long periods of time and are not deleted automatically.
  • To prevent wasting disk space, you must delete host variable information from staging tables manually, when it is no longer needed.
  • You can access collected host variable samples through the Manage Workloads dialog, where you can review what has been collected so far and remove any samples you no longer need. For more information, see Managing workloads.

About this task

When you use this feature, Db2 Query Monitor collects a fixed-size representative sample of all host variable values seen during a sampling interval. This allows you to find the most commonly used host variable values and better understand your application's performance. You can also use host variable information to tune your SQL queries. In the case of stripped SQL text, the staging table contains the first unstripped SQL text, even though sampling is performed on all unstripped SQL texts.

Procedure

  1. From any window within the CAE Browser Client click Activity Browser.
  2. Use the data table to navigate to the SQL text for which you want to collect host variables and click Collect HostVars.
  3. Specify the following:
    Scope
    Select the appropriate option for the Scope of the host variable collection. Click Selected Rows to capture host variables for the selected rows. Click Current Page to capture host variables for all rows shown on the current page in the data table. Click All Rows to capture host variables for all rows on all pages of the data table.
    Staging Tables Connection
    From the Staging Tables Connection list, select the staging table connection you want to use to store collected host variable information. Click Create New to create a new staging table connection, if the list does not contain a staging table connection you want to use.
    Ignore SQL Text Retrieval Errors
    Select this check box if you want to ignore errors encountered while retrieving SQL text.
  4. Click Next.
  5. Specify the following:
    Collection Period
    Specify the period of time during which you want to collect host variable information. Select the Start Time and End Time using the date and time drop down lists.
    Sample Size
    Type an integer in the Sample Size box to define the sample size to use when collecting host variable information. For every SQL query from the workload, the CAE Agent collects a fixed size representative sample (the Sample Size) of all host variable values that occurred during the sampling interval.
  6. Click Finish.
  7. You can view collected host variable information by navigating to an SQL drill down that contains the statements from the workload and selecting the Host Variables tab in the details panel. Alternatively, you can select Application Menu > Query Tuning > Manage Staging Tables and clicking Manage Workloads. For more information, see Managing workloads.

Example

A user tries to tune SQL statements that use host variables (static text) or parameter markers (dynamic text). To help guide their tuning decisions, the user wants to identify the values of the host variables that are used most often. The user can select a set of SQL statements (from the SQL drill down in Operational Summaries). After selecting the SQL statements for which they want to collect host variable information, they can click Collect Host Variables, which presents a dialogue that asks them to specify the staging tables in which to store the workload. In this same dialogue, the user specifies the time period for which to collect the host variables.

When that time period is over, the user can:

  • View the relative frequency of host variables used for individual SQL statements by a going to an SQL drill down that includes the statements of interest
  • Select one of those statements, and
  • In the Details panel, select the Host Variables tab

The resulting display shows a table where there is a column for each host variable, plus an additional column to show the percentage (the percentage column will actually be the first column). Each row shows the following data:

  • The percentage of samples in which the specific combination of host variable values occurred
  • The individual host variable values