Query Patroller

Query class behavior update

A warning message is returned when one of the following tasks is performed through the Query Patroller Center or Query Patroller command line:

Adding a query class
Removing a query class
Updating the maximum cost of a query for a query class

The warning message is:

DQP1024W  Creation, change, or removal of a query class will not 
          take effect until the Query Patroller server is restarted.

Similarly, the DB2 Query Patroller^(TM) Guide: Installation, Administration, and Usage, Version 8.2, states that you must restart the Query Patroller server after creating, changing, or removing query classes for your changes to take effect.

The message and the statement in the guide are no longer accurate. The three query class tasks listed previously will take effect immediately unless there are queued or running queries. If there are queued or running queries, including newly submitted queries, the query class changes will take effect when the queued or running queries complete. If you do not want to wait for all queued and running queries to complete, you must restart the Query Patroller server.

Note:

As with earlier versions of Query Patroller, updating the maximum number of queries for a query class always takes effect immediately.

Definition updates for managed query states

The Canceled and Done query status meanings are updated as follows:

Canceled: The query was canceled, through either the Query Patroller Center or the Query Patroller command line, by the administrator, submitter, or an operator whose profile has the MONITORING privilege with edit authority. Only running, held, released, and queued queries can be canceled.
Done: The query completed successfully.
Note:

Although the query itself completed without error, the application may receive an error if the completion was caused by an external event, such as a DB2 force application.

Create Explain tables before running Query Patroller historical data generator

When running the historical data generator for Query Patroller, if the Explain tables do not already exist, the generator will create them for you. However, it is strongly recommended that you create the Explain tables before running the historical data generator. When you create the Explain tables be sure you create them on the same partition. Actively creating the Explain tables on the same partition improves the performance of the Explain facility. This improvement increases the performance of the historical data generator.

Checking Query Patroller log files for historical analysis

If the Explain Run column of the Query Activity over Time (Historical Analysis) report shows a status of Ran unsuccessfully for a query, historical data has not been generated for that query. Therefore, the query will not appear in any historical analysis reports or graphs. As documented in Version 8, to determine why the query was unsuccessful, you can examine the qpuser.log file.

In addition to examining the qpuser.log file, you should examine the qpdiag.log file.

Abnormal shutdown of the historical data generator

If you run the historical data generator and shut it down in an abnormal way, you will receive an error the next time you attempt to run the historical data generator. Examples of abnormal shutdown include:

DB2 Universal Database stops unexpectedly
Issuing a db2stop force command
Issuing a killdb2 command

When the historical data generator shuts down abnormally, you must issue the following command before attempting to rerun the historical data generator:

    qp -d database generate historical_data stop

where database identifies the database that the command is being run against.

Dynamic query class updates

Certain query class operations no longer require Query Patroller to be stopped and restarted to take effect.

In the table that follows, an active query is a query whose status is Running or Queued.

Table 38. Conditions for query class changes to take effect
Nature of change	Conditions for change to take effect
Addition, removal, or update of a query class.	If there are no active queries, changes take effect immediately.
An update to a query class that involves only a change to the Maximum number of queries.	Takes effect immediately, even if there are active queries.
An update to a query class that involves only a change to the Maximum cost of a query.	If there are active queries, the update takes effect when either: Query Patroller is stopped and restarted. There are no more active queries. Note: When there is a change pending against Maximum cost of a query, subsequent query class updates of any kind will not take effect until one of the two previous conditions is met.
Addition or removal of a query class.	If there are active queries, the addition or removal takes effect when either: Query Patroller is stopped and restarted. There are no more active queries.

Nested query behavior

Nested queries cannot be queued. Instead, a nested query will run immediately if it exceeds a threshold that would normally cause it to be queued.

Limitations by SQL statement type

Contrary to previous documentation, the queries with the following statements can be queued:

Queries containing static SQL with host variables
Queries containing an identity value function (IDENTITY_VAL_LOCAL) or a sequence value function, such as NEXT VALUE FOR or PREVIOUS VALUE FOR

Resolution limitation when using the Terminal Services Client

When using the Terminal Services Client at resolution 640x480 to connect to a remote desktop that is running the Query Patroller Center, the Submission Preferences window might appear blank. For the Submission Preferences window to display properly, you must use a resolution higher than 640x480.

New group support for query submissions

Starting in Version 8.2, DB2 Universal Database (UDB) supports user groups beyond operating system groups. Therefore, there is a slight change in the Submitter Profile to Use drop-down list in the Query Submission Preferences window of the Query Patroller Center.

If you are logged in, but do not have either DBADM authority or Edit privilege for Query Patroller user administration, you can only add or update a submission preference for yourself. In this case, the Submitter Profile to Use drop-down list contains existing submitter profiles of the DB2 UDB groups that you belong to, instead of just the operating system groups that you belong to.

If you are logged in, and have either DBADM authority or Edit privilege for Query Patroller user administration, you can add or update submission preferences for other users. In this case, the Submitter Profile to Use drop-down list contains all existing group submitter profiles.

Query Patroller schedule limitations

When working with schedules in the Query Patroller Center, you can use the Schedule window to save schedules to a file and import them later. If you have a schedule that you saved using FixPak 6 or earlier, you cannot import the schedule using Version 8.2 or later. This limitation is due to the change in serialization between JDK levels introduced with DB2 UDB Version 8.2.

Authorization required to use RUN IN BACKGROUND QUERY command

To run the RUN IN BACKGROUND QUERY command, you must be the submitter who submitted the query originally.

Creating an alias for a result table

As of Query Patroller Version 8.1 FixPak 5, Query Patroller stopped creating result tables in the schema that matched the authorization ID of the submitter of the query. Instead, Query Patroller started creating result tables in a common DB2QPRT schema. To allow result tables to be referenced using the schema of the submitter, Query Patroller Version 8.2 introduces an option to automatically create an alias for each new result table that Query Patroller creates. The result table is created in the DB2QPRT schema and the alias is created in a schema that matches the submitter's authorization ID.

To turn this option on or off, issue the UPDATE QP_SYSTEM command with the CREATE_RESULT_TABLE_ALIASES option:

>>-UPDATE QP_SYSTEM USING--------------------------------------->
 
>--+-DEFAULT------------------------------+--------------------><
   '-CREATE_RESULT_TABLE_ALIASES--+-'Y'-+-'
                                  '-'N'-'

Removing orphaned result table aliases

Aliases created with CREATE_RESULT_TABLE_ALIASES option are automatically dropped when a result table is dropped. However, there are two situations in which a result table may be dropped without the corresponding alias being dropped.

When the result table is dropped manually without using the qp command line or Query Patroller Center.
When the result table is dropped using the qp command line or Query Patroller Center under the authority of an operator who is not the submitter of the query and does not have DBADM authority.

To clean up aliases that have no corresponding result tables, a new command, REMOVE RESULT_TABLE_ALIASES, has been created. This command is automatically executed whenever result tables are purged as part of the Query Patroller scheduled result table purging process. The REMOVE RESULT_TABLE_ALIASES command obtains the list of aliases to purge using the following query:

with a as (select tabschema, tabname from syscat.tables 
           where type = 'A' and tabname like 'QUERY%_RESULTS'), 
     t as (select tabname from syscat.tables 
           where type = 'T' and tabname like 'QUERY%_RESULTS')
  select all tabschema, tabname from a 
  where not exists (select * from t where t.tabname=a.tabname)

Prerequisites

You must have DBADM authority.

Procedure

Issue the REMOVE RESULT_TABLE_ALIASES command

This command removes all aliases that exist after having their corresponding result tables dropped. The aliases were originally created by Query Patroller for result tables.

Command syntax

>>-REMOVE RESULT_TABLE_ALIASES---------------------------------><

Note:

For information about entering Query Patroller commands using the command line interface, and general syntax for Query Patroller commands, see the Query Patroller command line interface.

Fenced user ID requires write access qpdiag.log file and path

Query Patroller uses some fenced stored procedures which may log entries to the qpdiag.log file. Therefore, the fenced user ID must have access to write to the qpdiag.log file and the path where the qpdiag.log file resides.

[ Top of page |Previous page | Next page | Contents ]