This part of the book provides information and procedures for running and maintaining the DB2 Query Patroller system. The following chapter provides an overview of the system and its components, and gives you a step by step explanation of how DB2 Query Patroller handles and processes jobs.
This book uses syntax diagrams as well as the syntax parameters described in
the table below. Consider parameters in the table to be case sensitive
unless the description of the parameter states otherwise.
| Convention | Description |
|---|---|
| { } | Brace brackets contain multiple choices separated by the pipe (|) symbol. You can only select one of the values within the brace brackets. For example, the syntax {yes | no} indicates that you can select either the yes or no value, but not both. |
| | | The pipe symbol separates multiple choices found within brace brackets. |
| [ ] | Square brackets contain optional parameters. |
| Italics | Italics are used to note a variable parameter that is acting as a place holder. You must replace it with a specific value when you issue the command. For example, node_id represents a variable for the node ID; you must supply a specific node ID when you enter the command. |
| ... | An ellipsis indicates that the previous value may be repeated. |
The DB2 Query Patroller system consists of the DB2 Query Patroller server
system, the system administrator's workstation, and client PCs.
The diagram below illustrates the architecture of the DB2 Query Patroller
system and the product components on each platform:
DB2 Query Patroller consists of the following components:
The 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:
The request server provides services for requests received from the various DB2 Query Patroller components.
The cost analyzer component determines the relative cost to execute a query based upon explain plan output. The cost analyzer executes one time per query. The job scheduler uses the calculated cost at a later time to determine when the query should be run.
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. DB2 Query Patroller assigns a hold status to a query if the computed cost exceeds the user's cost threshold as defined in the User Profile table. (For information about the User Profile table, see DB2 Query Patroller Tables.) The notifier component of DB2 Query Patroller notifies the user in the event that a query has been placed on hold.
The job scheduler schedules the query to a node for execution. DB2 Query Patroller uses a proprietary scheduling technique that takes into account:
The notifier scans the DB2 Query Patroller tables for newly completed queries at a user-specified time interval. It notifies the user through system mail when the query completes. This component also adds a row to the Job Accounting table for each job.
The following list outlines the query completion status returned to the user through the mail system:
The server and agent components write error information to a log file. 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 interested parties through e-mail.
The 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 agent(s) may run on several nodes, including the same node as the server component.
The DB2 Query Patroller agent consists of the following software components:
The node manager executes any work scheduled to its node by the job scheduler.
The executor component runs SQL statements and system commands.
The command-line interface enables the system administrator to monitor and control DB2 Query Patroller from a command-line prompt. This feature also enables you to submit and monitor jobs from the command line. 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.
QueryAdministrator provides an interface for administering the DB2 Query Patroller system. QueryAdministrator is a Java application that runs on Windows 32-bit operating systems.
QueryAdministrator enables the system administrator to manage the DB2 Query Patroller system parameters, create or delete profiles for DB2 Query Patroller users, manage nodes, result destinations, data sources and job queues.
The Tracker utility provides reports that display a database usage history for queries that have been managed by the DB2 Query Patroller system. For example, Tracker 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.
QueryEnabler places queries submitted through an ODBC-compliant query tool under the management of the DB2 Query Patroller system. With QueryEnabler, you can wait for the results to return, or you can have the results returned at a later time. This frees your workstation to submit other queries or perform other work. QueryEnabler runs on Windows 32-bit operating systems.
QueryMonitor provides an interface for monitoring queries that have been submitted to the DB2 Query Patroller system. QueryMonitor is a Java application that runs on Windows 32-bit operating systems.
QueryMonitor enables the user to monitor query status, view job details, cancel jobs, submit new jobs, drop result tables, and resubmit completed jobs.
The following list explains the tables used by DB2 Query Patroller. The DB2 Query Patroller installation creates these tables in the database.
Various components of DB2 Query Patroller use the Job table to monitor and track each job under the control of DB2 Query Patroller. The various DB2 Query Patroller components maintain this table. Users can monitor their jobs, cancel jobs, and drop temporary result tables through the QueryMonitor component. System administrators can monitor any job, change a job's status, manage system and node parameters, and maintain user profiles using QueryAdministrator. The notifier component of DB2 Query Patroller can automatically purge obsolete job information after a period of time specified by the system administrator.
The User Profile table stores the user profile information for the DB2 Query Patroller users. A User Profile record must exist in this table before the corresponding user can log into the DB2 Query Patroller system. The system administrator maintains this table using QueryAdministrator.
The Node Information table contains information concerning the status of each node in the system. The server and agent components maintain this table. The system administrator can alter the status of any node using the command-line interface or QueryAdministrator.
The System Parameters table contains system-wide information that DB2 Query Patroller uses to control processing. The system administrator maintains this table using the command-line interface or QueryAdministrator.
Normally, the database stores query results as tables. The Result Destinations table contains the descriptions of alternate destinations for query results. See Result Destinations for additional information about alternate destinations. The system administrator maintains this table using the command-line interface or QueryAdministrator.
The Data Sources table contains information that DB2 Query Patroller uses to control processing for each database instance. Presently, DB2 Query Patroller controls only one database. The system administrator maintains this table using the command-line interface or QueryAdministrator.
The Job Queues table defines the job queues for each data source (database instance). Queues receive job assignments based on the job's cost. Each queue can have different limits on the number of jobs run. The system administrator maintains this table using the command-line interface.
When job accounting status is active, the notifier component of DB2 Query Patroller adds one row to the job accounting table each time a job completes, either successfully or unsuccessfully. The information in this table is used by the Tracker utility.
Each time DB2 Query Patroller executes a job containing a SQL SELECT statement, it normally creates a temporary table containing the results of the query. QueryEnabler retrieves results from these tables and returns the results to the originating query application. QueryEnabler and QueryMonitor can be used to drop result tables. If result tables are not dropped by the user after retrieval, the notifier component of DB2 Query Patroller can automatically purge result tables after a period of time specified by the system administrator.
The various DB2 Query Patroller components communicate with each other through either the DB2 Query Patroller tables or socket connections. All of the Windows-based components communicate with DB2 Query Patroller through the DB2 Query Patroller tables. The command-line interface, agent component, and some parts of the server component communicate with the DB2 Query Patroller request server through sockets. To reduce the number of TCP socket connections required, the iwm_net process runs on the server node and on the agent nodes. The iwm_net processes enable the processes on one node to communicate with each other using TCP/IP sockets and concentrate all communication between nodes to a single outgoing socket connection.
This section describes the flow of a job from the time a query is submitted to DB2 Query Patroller until the time that notification of job completion is received.
Queries are typically submitted with 32-bit ODBC query applications. QueryEnabler intercepts these queries and, with user intervention, submits the query to DB2 Query Patroller as a job. Jobs can also be submitted through QueryMonitor and the command-line interface. When you submit a job, the DB2 Query Patroller component used to submit the job saves the SQL statement, along with the user ID, date and time the job is scheduled to be run, and the maximum cost permitted. The User Profile information provides the maximum cost. The component used to submit the job also adds this information to the Job table and assigns a job number.
After a user submits a job to DB2 Query Patroller, the server component estimates the cost to execute the query. The server component uses the cost estimate from DB2.
If the computed cost is greater than the maximum cost (obtained from the User Profile when the user submitted the job), the job is put on hold. Otherwise, the job runs as soon as the scheduled date and time passes and sufficient system resources are available. If the job is on hold, the DB2 Query Patroller system administrator must release or cancel the job. For jobs submitted through the command-line interface, cost analysis is performed as part of job submission, not as a separate step.
The server component's job scheduler activates periodically to scan for new jobs to be run. It processes jobs that are ready to run in order of priority. Jobs of equal priority are processed in the order that they were submitted. To determine if a job can run, the scheduler looks to see if the number of queries already running has reached the limit set in the User Profile. The scheduler also looks to see if the total number of queries running or the cost of the new job plus the total cost of any running jobs has reached the limits set for the data source and the system. Next, based on the job's cost, the scheduler checks for an associated job queue and determines the limit on the number of jobs for the queue. If a job cannot be scheduled due to system limits, the scheduler bypasses the job and processes the next ready job. The scheduler processes any jobs skipped during a scheduling cycle the next time it activates.
If a job can be scheduled, it must be assigned to a node. The job scheduler selects a node based on the limits set for the data source and on the CPU and disk-utilization statistics being gathered by the node managers. The node selected is the one with the fewest number of assigned jobs. If two or more nodes are running the same number of jobs, the job scheduler selects the node with the lowest CPU utilization.
The node manager of each agent activates periodically to scan for new jobs scheduled to be run on that node. (For more information, see DB2 Query Patroller Components.) The node manager creates a new process for each job to run the executor. Normally, for a job containing a SQL SELECT statement, the executor component opens a cursor to fetch the result rows. When the database returns the first row, DB2 Query Patroller creates the result table. The executor component uses an insert cursor to efficiently create the result table. When each job completes, the executor component sends the completion status and execution statistics back to the server component to be recorded in the job table.
In addition to running jobs newly scheduled for the node, each time the node manager activates it also collects CPU and disk utilization statistics and checks for jobs to be canceled or that have aborted. Jobs may be canceled any time from submission until they have been scheduled. Database connections for running jobs are removed when a job is canceled.
The server component's notifier activates periodically to scan for jobs that complete successfully or unsuccessfully, or for any jobs with a held status. This component notifies users of these events through e-mail and adds a row to the Job Accounting table for each completed job when the job accounting status is active. The notifier component also purges expired job table entries and result tables.