DB2 Version 9.7 for Linux, UNIX, and Windows

Query Patroller components

DB2® Query Patroller is a client and server solution consisting of the following components: DB2 Query Patroller can be deployed on a system running DB2 Enterprise Server Edition.
Important: With the new workload management features introduced in DB2 Version 9.5, Query Patroller and its components have been deprecated in Version 9.7 and might be removed in a future release. For more information, see "DB2 Governor and Query Patroller have been deprecated".

Query Patroller server

When you install Query Patroller server, the following software elements are deployed to the target computer:
Query Patroller stored procedures
Query Patroller stored procedures are called by other Query Patroller components to perform the necessary database tasks.
Control tables
When Query Patroller is set up to manage queries issued against a database, the DB2QP schema, control tables, triggers, functions, and procedures are created within that database. The control tables store all of the information that Query Patroller requires to manage queries. This information includes the following:
  • Query Patroller system properties settings
  • Query class information
  • Submitter information, including query submission preferences
  • Operator information
  • Managed query properties information
  • Historical query properties information
  • Query result information
  • Historical analysis data
  • Scheduled purge job details
For example, the SUBMITTER_PROFILE table contains information such as the submitter's ID, authority level, and the maximum number of queries that the user can have running simultaneously. When the user submits a query, Query Patroller references the SUBMITTER_PROFILE table for these parameters.
Log files
Diagnostic information about errors is recorded in these Query Patroller log files:
qpsetup.log
Query Patroller writes to qpsetup.log during installation and when the qpsetup command is issued. On UNIX operating systems, qpsetup.log resides in the INSTANCE/db2dump directory, where INSTANCE is the directory where you installed DB2. On Windows, qpsetup.log resides in the directory specified in the diagpath database configuration parameter. The qpsetup.log file is intended for use by Query Patroller administrators.
qpuser.log
Query Patroller starts writing to the qpuser.log file when the system becomes active. The information written to the qpuser.log file is used for problem determination and is intended for use by Query Patroller administrators. On UNIX operating systems, qpuser.log resides in the INSTANCE/db2dump directory, where INSTANCE is the directory where you installed DB2. On Windows, qpuser.log resides in the directory specified in the diagpath database configuration parameter. If you choose to locate qpuser.log in a place other than the default log path, ensure that the permissions in the new directory allow write access for the fenced user ID. Query Patroller commands call a fenced stored procedure which must have write access to this file for diagnostic information to be logged.
qpdiag.log
Query Patroller starts writing to the qpdiag.log file when the system becomes active. The information written to the qpdiag.log file is used for problem determination and is intended for use by DB2 technical support. On UNIX operating systems, qpdiag.log resides in the INSTANCE/db2dump directory, where INSTANCE is the directory where you installed DB2. On Windows, qpdiag.log resides in the directory specified in the diagpath database configuration parameter. If you choose to locate qpdiag.log in a place other than the default log path, ensure that the permissions in the new directory allow write access for the fenced user ID. Query Patroller commands call a fenced stored procedure which must have write access to this file for diagnostic information to be logged.
Use a text editor to view the log files on the server where Query Patroller is installed. The most recent events are recorded at the end of the file. Generally, each entry contains the following parts:
  • A time stamp
  • Instance, database, and database partition name details
  • Process ID (PID) and name, or Thread ID (TID) and name
  • The component reporting the error
  • A diagnostic message (usually beginning with "DQP") explaining the error
The log files grow continuously. Occasionally, they should be backed up then erased. A new log file is generated automatically the next time it is required by Query Patroller.

Query Patroller directory access

All userids that require access to Query Patroller must have read access to the directory <instance directory>\ctrl\qp on Windows and INSTHOME/sqllib/ctrl/qp on Linux and UNIX. If any userids that are running the Query Patroller tools do not have access to this directory, they might be unable to connect to the Query Patroller server, resulting in error SQL29007. A file located in this directory contains configuration information that allows DB2 and the Query Patroller tools to communicate with Query Patroller server. You must not modify or delete the files in this directory while the Query Patroller server is running.

Query Patroller Center

The Query Patroller Center is a graphical user interface that allows administrators to manage Query Patroller system properties, users, and queries, and to view historical analysis reports. The Query Patroller Center also allows query submitters to manage their queries, save query results, and customize their query submission preferences.

The look and functionality of the Query Patroller Center varies depending on different factors, such as the authority of the user and whether the DB2 administration tools are also installed.

An administrator has access to the Query Patroller Center's full functionality. The following list shows some of the tasks that administrators can do with Query Patroller Center:
  • Manage the Query Patroller system parameters
  • Create, update, or delete profiles for Query Patroller submitters and operators
  • Create, update, or delete submission preferences for Query Patroller submitters
  • Create, update, or delete query classes
  • Monitor and manage queries that have been intercepted by the Query Patroller system
  • Generate and analyze reports that display database usage history
A submitter has access to a subset of the Query Patroller Center's functionality. The following list shows some of the tasks that submitters can do with Query Patroller Center:
  • Monitor and manage queries that they have submitted through the Query Patroller system
  • Store results of the queries that they have submitted for future retrieval
  • Show or file results of the queries that they have submitted
  • Create, update, or delete their own query submission preferences

Query Patroller command line support

Command line support enables Query Patroller administrators and submitters to perform most Query Patroller tasks from the DB2 CLP or from the operating system's command line prompt. Query Patroller commands can also be combined with shell scripts or languages such as Perl, awk, and REXX.

Parent topic: DB2 Query Patroller overview