Queries are typically submitted through a query application or dynamically through a DB2 interface. Jobs can also be submitted through QueryMonitor and the command line interface. When a job is submitted, DB2 Query Patroller saves the SQL statement, the user ID, the date and time the job is scheduled to be run, the maximum cost permitted, and the cost management threshold. The Cost Management Threshold parameter determines whether or not the query will be intercepted by the QueryEnabler tool for user intervention. The Maximum Cost and the Cost Management Threshold parameters are defined for each user or group profile using the User Administration page of the QueryAdministrator tool.
After a user submits a job to DB2 Query Patroller, the server component
estimates the cost to execute the query using the cost estimate from
DB2. For more information about DB2 cost estimates, refer to the
DB2 Administration Guide.
 | DB2 Query Patroller can invoke a customer-written exit program just prior to performing cost analysis for a query. The exit program can examine the SQL statement and prevent DB2 Query Patroller from executing the statement. For more information, see Exit Analysis. |
The cost of the job is measured against the Maximum Cost and Cost Management Threshold parameters obtained from the profile of the submitting user. If the computed cost is greater than the maximum cost, the job is placed on hold. The cost management threshold parameter determines whether or not the query will be intercepted for user intervention by the QueryEnabler tool.
If the job does not exceed the cost management threshold, the query will not be intercepted and will execute immediately. If the job exceeds the cost management threshold, and does not exceed the maximum cost, the query will be intercepted by the QueryEnabler tool for user intervention, and the job will run as soon as the scheduled date and time pass and sufficient system resources become available.
If a job is placed on hold, the DB2 Query Patroller system administrator or operator 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 DB2 Query Patroller node to be executed. 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. The node manager creates a new process to run the executor for each new job. Normally, for a job containing an SQL statement, the executor component opens a cursor to fetch the result rows. 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 hold 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.