This chapter provides information about DB2 Query Patroller parameters and system administration tasks.
You can perform system administration tasks for DB2 Query Patroller through the command-line interface or QueryAdministrator. System administration tasks include setting various DB2 Query Patroller parameters to control its operation, maintaining the cost statistics, and adding users to the system.
The following information outlines the DB2 Query Patroller parameters.
The system administrator can set environment variables to alter the behavior of DB2 Query Patroller. To have an effect, the environment variables must be set for the iwm account before starting DB2 Query Patroller. The DB2 Query Patroller Installation Guide provides information about setting DB2 Query Patroller environment variables.
Set the IWM_DISKMON environment variable to the file system where the result sets are created. Each agent node can monitor the space available in a different file system. When available disk space goes below a system threshold, no more jobs will be submitted to that node. If IWM_DISKMON is not set, disk space is not monitored and creation of result tables will fail when the file system becomes full.
DB2 Query Patroller invokes the exit analysis program if you set IWM_EXIT_AN to the path name of the executable program before starting the server processes. The exit can be a shell script or any other executable. DB2 Query Patroller invokes the exit under the iwm user ID. DB2 Query Patroller passes the job owner's user ID as the only argument to the exit. The SQL statement can be read from STDIN. If the exit routine terminates with an exit code of zero, and without having written anything to STDOUT, DB2 Query Patroller continues to process the job normally. If the exit routine terminates with a non-zero exit code or writes anything to STDOUT, the job ends abnormally and sends a message to STDOUT.
Set the IWM_INTERVAL environment variable to the number of seconds that each server and agent process sleeps between searches for work. The default value is 20, which can be appropriate on a system with a moderate load. On a system with a light load, use a smaller value, such as 5, so DB2 Query Patroller will be more responsive to new requests.
Set the IWMLOG environment variable to the directory into which DB2 Query Patroller writes its log files. The default value is $IWM_RUNTIME/log.
If you set IWM_LOGMON, the iwm startup process will start the log monitor when it starts the server processes, and stop the log monitor when the server processes stop. The IWM_LOGMON environment variable should be set to the path name of the configuration file.
You can set IWM_MAIL to cause the notifier component to use the program named to format e-mail messages. If you do not set this variable, DB2 Query Patroller uses $IWM_RUNTIME/bin/iwm_mail.sh.
IWM_NET must be set to the TCP/IP address and port of the server node, unless the command is run on the server node or one of the agent nodes. Use the format address:port, where address represents either the dotted notation for the address or a name resolvable in the /etc/hosts file or through DNS, and port represents either the port number or a name resolvable in the /etc/services file.
If you want to ensure that all jobs run and that none are disqualified due to their estimated cost, disable cost analysis for all DB2 Query Patroller jobs by setting the IWM_NOEXPLAIN environment variable to any non-null value. Setting this variable prevents DB2 Query Patroller from performing a cost analysis on queries.
Set the IWM_RES_TBLSPC environment variable to the name of the tablespace that will hold result tables. Each node may specify a different name, or multiple nodes may share a name. If this variable is left unset, the result tables will be placed into tablespaces determined by DB2.
Set the IWM_RUNTIME environment variable to the path where the DB2 Query Patroller software is installed.
Set the IWM_server environment variable to the TCP port and IP address of the DB2 Query Patroller server component. Use the format address:port. The address represents either the dotted notation for the address or a name resolvable in the /etc/hosts file or through the Domain Name System (DNS). The port represents either the port number or a name resolvable in the /etc/services file.
The System Parameters table stores the following parameters. These global parameters apply to all of DB2 Query Patroller. You can display and change these parameter values through the command-line interface and QueryAdministrator.
This parameter indicates the maximum total cost allowed for all running jobs.
This parameter indicates the maximum total number of jobs allowed to be running.
Setting this parameter to a non-zero value informs DB2 Query Patroller to purge information about a job after a defined number of days have passed.
Setting this parameter to a non-zero value informs DB2 Query Patroller to drop result tables after the defined number of days have elapsed since job completion. A value of zero will reatain all result tables.
This parameter indicates if rows should be added to the Job Accounting table when jobs complete. This parameter needs to be enabled to run the Tracker component.
The Data Sources table stores parameters that apply to a specific database instance. You can display and change these parameters' values through the command-line interface and QueryAdministrator.
As indicated by this status, new jobs either will be accepted normally, held, or rejected.
This parameter indicates the maximum total cost allowed for all jobs running against the data source.
This parameter indicates the maximum total number of jobs allowed to run against the data source.
If the CPU utilization of a node exceeds this value, no additional jobs are scheduled to run on the node.
If the number of available bytes of disk for a node is less than this value, no additional jobs are scheduled to run on the node.
The Job Queue table stores the following parameters. You can display and change these parameters' values using the QueryAdministrator.
DB2 Query Patroller assigns jobs to the queue that has the smallest cost limit that is not less than the cost of the job.
This parameter indicates the maximum number of jobs allowed to run in the queue.
The User Profile table stores the following parameters. You can display and change these parameter values through the QueryAdministrator.
This parameter indicates whether the user can bypass cost analysis when submitting a job. Bypassing cost analysis makes DB2 Query Patroller less able to effectively manage the system workload.
Any jobs with an estimated cost greater than this value receive a hold status. The DB2 Query Patroller system administrator must either release or cancel jobs with a hold status. The job cost is measured in timeron units.
This parameter indicates the maximum number of jobs allowed to run for a specific user.
Each user has one of three priority levels defined: low, normal, and high.
The following subsections provide information about performing the system administrator tasks of maintaining cost statistics and adding users to the system.
If 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.
Each DB2 Query Patroller user must be defined in the User Profile table, and must have a system account on the database server system with the CONNECT database privilege.
The task of adding users includes defining an e-mail address to which DB2 Query Patroller can send notifications. Mail can be sent to other mail packages like CC-Mail and Microsoft Exchange, given the proper configuration of the mail system. (The scope of this document does not address mail system configuration.) If not set up properly, the notification message bounces back to the iwm account. The mail for this user should be monitored, either directly or by forwarding it to another user.