IBM DB2 Query Patroller Administration Guide

Configuring a DB2 Query Patroller Client

A typical DB2 Query Patroller client consists of two utilities. The QueryEnabler and the QueryMonitor. The QueryMonitor tool allows the user to mange queries, and the QueryEnabler tool is the component that intercepts submitted queries and places them under the control of the DB2 Query Patroller system. This chapter provides steps to help configure a DB2 Query Patroller client.

1. Before you can submit a query from a DB2 Query Patroller client workstation, you must have a user profile on the DB2 Query Patroller system.
2. The DYN_QUERY_MGMT database configuration parameter must be set to ENABLE on the DB2 Query Patroller server. Setting this parameter will allow the QueryEnabler component to intercept dynamically submitted queries.
3. There are several important parameters you must set when adding a user or group profile to the DB2 Query Patroller system. All of the parameters listed below will affect performance of the DB2 Query Patroller client:

   User Threshold

   Provides the threshold, which if exceeded by a job, causes the job to be placed on hold. If a job is placed on hold, only a user with administrator or operator authority can release it. Be sure to set this parameter at a value high enough to accommodate the queries that a user will be submitting. User threshold is measured in timeron units. A timeron is a unit of measurement used to give a rough relative estimate of the resources, or cost, required by the database server to execute two plans for the same query. The resources calculated in the estimate include weighted CPU and I/O costs.

   Management Threshold

   This value determines whether or not a dynamic SQL query will be intercepted by the QueryEnabler component. If the cost of the query does not exceed the set value, the query will not be intercepted by the QueryEnabler and will execute without user intervention. For example, if you want all queries with a cost greater than 10 to be intercepted by the QueryEnabler component for user intervention, set this parameter to a value of 10.

   Query cost is measured in timeron units. A timeron is a unit of measurement used to give a rough relative estimate of the resources, or cost, required by the database server to execute two plans for the same query. The resources calculated in the estimate include weighted CPU and I/O costs.

   Maximum Elapsed Time

   This option specifies the maximum number of seconds the query will be permitted to run against the DB2 Query Patroller server. If this value is set to 0 or -1 the query will always run to completion. If the value for this parameter is too small, queries will not be able to complete executing.

   Maximum Result Rows

   This option specifies the maximum number of rows that will be returned in the answer set. If this value is set to 0, the complete answer set will be returned. Ensure that this parameter is set to an appropriate value.

   Other parameters you can configure for the DB2 Query Patroller client include Low, Normal, and High Priority, and a users e-mail address for notification. For a complete listing of User Profile Parameters, and for instructions on how to add a user to the DB2 Query Patroller system, see User Administration.

4. Update the Database manager configuration file for the client workstation. You must set values for the following parameters:
   • The JAVA_HEAP_SZ specifies the maximum size of the heap that is used by the Java interpreter. For non-partitioned database systems, one heap is allocated for the instance; for partitioned database systems, one heap is allocated for each database partition server. The DB2 Query Patroller GUI tools are Java applications.
   • The JDK11_PATH parameter specifies the directory under which the Java Development Kit 1.1 is installed. The CLASSPATH and other environment variables used by the Java interpreter are computed from the value of this parameter. The DB2 Query Patroller GUI tools are Java applications.
5. DB2 Query Patroller profile variables provide a means to further configure a DB2 Query Patroller client. For instructions on how to set the following profile variables, see DB2 Profile Variables.

   DQP_ABORTRESULT

   If set to Y, result sets that exceed the value provided for the Maximum Result Rows parameter as defined in the User profile table will be aborted. No result set will be returned. To define the Maximum Result Rows parameter for a user or group, see User Administration. If DQP_ABORTRESULT is not set, the result set will be truncated once the row count reaches the defined number of rows specified by the Maximum Result Rows parameter.

   DQP_LAST_RESULT_DEST

   If DQP_NTIER variable is set to RUN or CHECK, set DQP_LAST_RESULT_DEST to the name of a valid result destination. Result destinations are defined using the Result Set Administration page of the QueryAdministrator tool. If the DQP_NTIER variable is set, and DQP_LAST_RESULT_DEST is not set, the result destination will be a table determined by DB2.

   DQP_NTIER

   This option is provided to allow queries to be posted to the DB2 Query Patroller server without requiring user intervention. It can be set to one of the following values:

   • RUN

     When the n-tier option is set to RUN, queries will be submitted to the DB2 Query Patroller server in a Submit and Wait mode. User intervention through the QueryEnabler tool is not required. The RUN value has a time out option: RUN: timeout. This option is in units of seconds. If this option is not set, QueryEnabler will wait until the query has been processed before returning control to the query application.

   • CHECK

     When the DQP_NTIER profile variable is set to CHECK, it will behave similarly to RUN, but in this case, DB2 Query Patroller will check to determine if an answer set already exists for the query being submitted. If an answer set does exist, that result set will be returned, as opposed to the same query being executed again. The CHECK value has a time out option: CHECK: timeout. This option is in units of seconds. If this option is not set, QueryEnabler will wait until the query has been processed before returning control to the query application.

     Note:

     The default value for the DQP_NTIER variable is OFF.

   DQP_SHARE

   The DQP_SHARE profile variable only requires setting if the DQP_NTIER profile variable is set. Otherwise, the option to share results sets is specified using the QueryEnabler interface. Set the value of this profile variable to Y to have the user's result tables granted public access. If the DQP_NTIER profile variable is set, and DQP_SHARE is not, only the submitting user and system administrator will have access to the user's result sets.

   DQP_TRACEFILE

   This profile variable specifies a file name for the QueryEnabler Java Trace file. Set this profile variable on the DB2 Query Patroller client.

6. A server side DB2 Query Patroller profile variable that will have a direct affect on the performance of a DB2 Query Patroller client is the DQP_INTERVAL profile variable. This profile variable specifies the number of seconds that each server and agent process sleeps between searches for work. For example, if the value for this variable is set to 20, a user may have to wait up to twenty 20 seconds plus the time it takes for a query to execute and return the result. Adjust this value according to your system and user requirements.