IBM DB2 Query Patroller Administration Guide

DB2 Query Patroller Control Tables

DB2 Query Patroller control tables belong to the IWM schema. The IWM schema and the control tables are created in the target database during the DB2 Query Patroller installation. The control tables contain all of the information DB2 Query Patroller requires to process queries. For example, the User Profile table contains information such as the user's ID, authority level, and the maximum number of jobs a user can have running simultaneously. When a user submits a query, DB2 Query Patroller will reference the User Profile table for these parameters.

The following list describes the control tables used by DB2 Query Patroller:

Job Table

The Job table stores information about queries under the control of DB2 Query Patroller. This information includes the ID and user name of the user that submitted the query, the priority of the query, when the query was submitted, and the status of the query. Various components of DB2 Query Patroller access and manipulate the data stored in the Job table. For example, when a user views a job's status through the QueryMonitor tool, it is the information in the Job table that is being displayed, or when a system administrator changes a job's status using the QueryMonitor tool, information in the Job table is updated.

User Profile Table

The User Profile table stores user profile information for DB2 Query Patroller users and groups. The User Profile table contains information such as the user or group ID, authority level, and the maximum number of jobs a user or group member can have running simultaneously. A user or group profile record must exist in the User Profile table before the corresponding user or group member can access the DB2 Query Patroller system. The system administrator uses the QueryAdministrator tool to add or manage user and group profiles for the DB2 Query Patroller system. See User Administration for more information.

Node Information Table

The Node Information table contains information concerning the status of each node in the system. The information contained in the Node Information table includes the Node ID, node status, the number of scheduled jobs for the node, and node CPU utilization. The DB2 Query Patroller server and agent components maintain the information in this table. The system administrator can alter the status of any node using the QueryAdministrator tool or the command line interface. For more information on changing node status using the QueryAdministrator tool, see Node Administration. For more information about using the command line to administer DB2 Query Patroller, see Using the Command Line Interface to Administer DB2 Query Patroller.

System Parameters Table

The System Parameters table contains system-wide information that DB2 Query Patroller uses to control processing. Information contained in the System Parameters table includes the maximum cost allowed for a query, and the maximum number of jobs allowed to be running. The system administrator maintains this table using the QueryAdministrator tool or the command line interface. For more information about the QueryAdministrator tool, see System Administration. For more information about using the command line to administer DB2 Query Patroller, see Using the Command Line Interface to Administer DB2 Query Patroller.

Result Destinations Table

Normally, the database stores query results as tables. The Result Destinations table contains the descriptions of alternate destinations for query results. Information stored in the result destinations table includes the destination name, destination type, and description. The system administrator maintains this table using the QueryAdministrator tool. For more information, see Result Destination Administration.

Data Source Table

The Data Source table contains information that DB2 Query Patroller uses to control processing for the database instance. The Data Source table stores information such as data source status and the maximum number of jobs that can be running simultaneously against the data source. Presently, a single installation of DB2 Query Patroller controls only one data source. The system administrator maintains the Data Source table using the QueryAdministrator or command line interface. For more information about maintaining the Data Source table using the QueryAdministrator tool, refer to Data Source Administration. For more information about using the command line to administer DB2 Query Patroller, see Using the Command Line Interface to Administer DB2 Query Patroller.

Job Queues Table

The Job Queues table defines the job queues for each data source. The Job Queues table contains information such as the maximum number of jobs for a queue and the maximum cost for a job on a particular queue. 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 QueryAdministrator tool. For more information about maintaining the Job Queue table using the QueryAdministrator tool, see Job Queue Administration.

Job Accounting Table

The Job Accounting table contains information on completed jobs. This information is used by the Tracker tool to provide reports that display database usage history for queries managed by DB2 Query Patroller. For this table to be populated with data, accounting status must be activated. This can be done using the System Administration page of the QueryAdministrator tool. For more information about using the QueryAdministrator tool to set the job accounting status, see System Administration.

When job accounting status is set to active, the notifier component of DB2 Query Patroller adds one row to the Job Accounting table each time a job completes. Job accounting status is set through the System Administration page of the QueryAdministrator tool. Only successful jobs are parsed by the Tracker tool. For more information about using the Tracker tool, see The Tracker Tool. For more information about job accounting, see Job Accounting.

Result Tables

Each time DB2 Query Patroller executes a job containing an SQL 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. For information about QueryMonitor or QueryEnabler, refer to the DB2 Query Patroller User's Guide.