IBM DB2 Query Patroller Administration Guide

DB2 Query Patroller Components

The DB2 Query Patroller system consists of the DB2 Query Patroller server and agents, the DB2 Query Patroller system administration workstation, and client workstations. DB2 Query Patroller can be deployed on a system running DB2 Enterprise Edition or DB2 Enterprise-Extended Edition. The diagram below illustrates the architecture of DB2 Query Patroller deployed on a system running DB2 Enterprise Edition. Refer to the DB2 Query Patroller Installation Guide for a diagram of DB2 Query Patroller deployed on a system running DB2 Enterprise-Extended Edition.

DB2 Query Patroller consists of the following components.

DB2 Query Patroller Server

The DB2 Query Patroller server executes on a single database node. The server accepts, analyzes, prioritizes, and schedules database requests and optionally notifies users when their requests have been processed.

The server consists of the following software components:

request server

The request server provides management and coordination for requests received from the various DB2 Query Patroller components.

cost analyzer

The cost analyzer component determines the relative cost to execute a query using the cost estimate provided by DB2. For more information about DB2 cost estimates, refer to the DB2 Administration Guide. The cost analyzer executes once per query. The job scheduler uses the calculated cost to determine when the query should be run. Because the cost analyzer uses the database catalog to determine the relative cost of a full table scan, RUNSTATS should periodically be run to keep the catalog entries current. The catalog reflects the status of a table as of the most recent RUNSTATS. If it has never been run, the catalog will be empty.

The processing described in the previous paragraph determines if DB2 Query Patroller should proceed with query execution or put the query on hold for later manual intervention by the system administrator. DB2 Query Patroller assigns a hold status to a query if the computed cost exceeds a user's cost threshold as defined in the user's profile. The notifier component of DB2 Query Patroller notifies the user in the event that a query has been placed on hold. For information about the User Profile table, see DB2 Query Patroller Control Tables. See User Administration for information on setting the cost threshold for a user.

job scheduler

The job scheduler schedules the query to a DB2 Query Patroller agent for execution. For the scheduling of queries, DB2 Query Patroller uses a scheduling technique that takes into account:

Current number of queries executing on the system
Cost of all queries currently executing
Number of nodes participating in the DB2 Query Patroller system
Individual user priorities
Number of queries executing for each user

notifier

The notifier scans the DB2 Query Patroller tables for newly completed queries at a user-specified time interval. This time interval is specified by the DQP_INTERVAL profile variable. For more information, see DB2 Profile Variables. The notifier component notifies the user through system mail when the query completes. If job accounting status is active, the notifier adds a row to the Job Accounting table for each query completed. Information in the Job Accounting table is used by the Tracker tool to provide reports and display database usage history.

The following list outlines the query completion status returned to the user through system mail:

Done: The query completed successfully.
Aborted: An abnormal termination has occurred.
Hold: The query has been placed on hold.
Cancelled: The query was canceled.

log monitor

The DB2 Query Patroller server and agent components write error information to a log file. The log monitor program periodically activates and examines the log file for new entries. You may want to consider using a product that consolidates system status information, including the DB2 Query Patroller log file. If you do not have such a product, use the DB2 Query Patroller log monitor to send new log entries to users through system mail. See Log Monitoring for more information.

DB2 Query Patroller Agent

The DB2 Query Patroller agent executes on one or more database nodes and processes database requests. On a single-processor or non-clustered SMP machine, the agent and server components run on the same machine. On an MPP machine, or clustered SMP machines, the server component runs on one node and the agents may run on several nodes, including the same node as the server component.

The DB2 Query Patroller agent consists of the following software components:

node manager: The node manager executes any work scheduled to its node by the job scheduler.
executor: The executor component runs SQL statements and system commands.

Command Line Interface

The command line interface enables the system administrator to monitor and control DB2 Query Patroller from a command line prompt. The system administrator can also submit and monitor jobs using the command line interface. When combined with shell scripts or languages such as Perl, awk, and REXX, you can use the command line interface as an application programming interface (API) to DB2 Query Patroller. See Using the Command Line Interface to Administer DB2 Query Patroller for more information.

QueryAdministrator

QueryAdministrator tool is a Java application that provides a GUI interface for administering the DB2 Query Patroller system.

QueryAdministrator enables the system administrator to manage the DB2 Query Patroller system parameters, create or delete profiles for DB2 Query Patroller users, and manage nodes, result destinations, data sources and job queues. See Using QueryAdministrator for more information.

Tracker

The Tracker is a system administration GUI tool that provides reports displaying database usage history for queries that have been managed by the DB2 Query Patroller system. For example, the Tracker tool allows you to determine which tables and columns have been accessed most frequently, which tables have returned the most result rows, and which jobs have been completed within a specific time period. See The Tracker Tool for more information. The Tracker tool is a Java application.

QueryEnabler

QueryEnabler places submitted queries, including dynamic queries submitted through any DB2 interface, under the management of the DB2 Query Patroller system. With QueryEnabler, you can wait for the query results to return, or you can have the query results returned at a later time to free your workstation to submit other queries or perform other work. Refer to the DB2 Query Patroller User's Guide for more information. The QueryEnabler tool is a Java application.

QueryMonitor

QueryMonitor is a GUI tool used for monitoring and managing queries that have been submitted through the DB2 Query Patroller system. With QueryMonitor, you can monitor query status, view job details, cancel jobs, submit new jobs, drop result tables, and resubmit completed jobs. Refer to the DB2 Query Patroller User's Guide for more information about QueryMonitor. The QueryMonitor tool is a Java application.

[ Top of Page | Previous Page | Next Page ]