This chapter provides information and procedures for using the QueryMonitor utility. The following topics are covered:
The DB2 Query Patroller system is used to prioritize and schedule queries so that query completion is more predictable and computer resources are efficiently utilized. DB2 Query Patroller provides many components to use in the query process. Using these components, jobs can be submitted for execution, resubmitted, scheduled, and canceled. Results tables can be viewed and dropped. The QueryMonitor utility enables you to monitor and manage your individual queries.
The QueryMonitor main window is illustrated below:
The table below contains a description of each column that displays by default
in the QueryMonitor main window. See Choosing Columns for information on how to add columns to or delete columns
from the window.
Table 4. Default Columns in the QueryMonitor Main Window
Column | Description |
---|---|
Job ID | Displays the job ID. |
User Name | Displays the user name. |
Job Status |
Contains the job status. Valid values are:
|
Priority | Indicates the priority assigned to the job. |
Created | Displays the date and time the job was created. |
Start After | Displays the date and time after which the job can be scheduled for execution. |
Completed | Displays the date and time the job was completed, aborted, or canceled. |
Result Rows | Indicates the number of rows returned in the result set. |
Result Destination | If results were saved to an alternate destination, this field contains the name of that destination. |
Result Table Name | If results were saved to the default location, this field contains the result table name. |
Estimated Cost | Displays the estimated database cost for the job. |
The table below contains descriptions of each push button on the QueryMonitor
main window.
Table 5. push buttons on the QueryMonitor Main Window
Push Button | Description |
---|---|
List Jobs | Used to display the job list. Also used to refresh the contents of the job list. |
Job Operations | Displays a list of other functions that can be performed in QueryMonitor. You can use this push button to view a job detail, cancel a job, drop a result set, and submit a new job. |
Choose Columns | Used to select which columns display in the job list. |
Clear Filter | Used to clear the filtering criteria for the job list. |
To display a job list, complete the following steps:
DB2 Query Patroller searches for all jobs that match your user ID and QueryMonitor displays the job list on the screen in tabular format. If you are an administrative user or operator, all jobs will display.
Note: | If you click List Jobs after resizing the column widths, the default column widths redisplay. |
You can use the filter criteria at the bottom of the QueryMonitor screen to choose which jobs to display. For example, you can display jobs with a done status, jobs residing on a particular node, or a combination of the two. However, you cannot obtain a job listing for user IDs other than your own.
Note: | If you are an administrative user or operator, you can view jobs for all users. You can also specify the user ID as an additional filter criterion. |
To specify which jobs to display, complete the following steps:
Note: | Whenever you change the Job Status field, the job list automatically redisplays. |
To clear the filtering criteria, complete the following steps:
Note: | If you specified a job status other than All, the job status reverts to All and the job list automatically redisplays. |
Whenever you are displaying the job list, you can choose which columns you want to display.
To choose the columns, complete the following steps:
The Select Columns for Display window displays as shown below:
Note: | Any changes you make to the columns displayed are retained for the current session only. |
You can reorder the job list in ascending or descending order by any displayed column.
To reorder the job list by a particular column, click the column heading. The job list will be displayed in ascending or descending order.
Note: | The first time you click a column heading, the job list is reordered in descending order. Clicking the same column again reorders the job list in ascending order. |
You can resize any column in the job list.
To resize a column, complete the following steps:
The mouse pointer changes to a double arrow.
Note: | Any changes you make to the columns displayed are retained for the current session only. If you click List Jobs after resizing the column widths, the default column widths redisplay. |
You must refresh the job list to retrieve the most current information from the DB2 Query Patroller system. Some operations such as changing the job status filter criteria, or submitting a new job, automatically refresh the job list for you.
To manually refresh the job list, click List Jobs.
Note: | If you click List Jobs after resizing the column widths, the default column widths redisplay. |
From the QueryMonitor main window, you can select a specific job and drill down to view detailed information about that job.
Note: | To select more than one job, press and hold the Shift key while selecting the jobs. |
Detailed job information displays for each selected job in a separate
Detailed Information for Job window similar to the one shown below:
Note: | You can also double-click a job to display detailed job information for that job. |
The Result Information page displays the SQL statement, error descriptions, estimated and threshold costs, and additional result information.
The Time Information page displays the time the job was created, scheduled, started, updated, and completed. This page also indicates if and when the user notification was sent.
The General page displays the submitting node and ID; job priority and predecessor, explain ID, queue ID, process ID, node ID, source ID; and security and text hash values.
The table below contains a description for each field at the top of the
Detailed Information for Job window.
Table 6. Fields Above the Tabs in the Detailed Information for Job Window
Field | Description |
---|---|
Job ID | Contains the user ID. |
User Name | Contains the user name. |
Status |
Indicates the query status. Valid values are:
|
Job |
Indicates the job type. Valid values are:
|
Result Status |
Indicates the result status. Valid values are:
|
Cancellation Status |
Indicates the status of any cancellation requested for this job. Valid values are:
|
To view result information, click the Result Information tab on the
Detailed Information for Job window. The Result Information page is
illustrated below:
The table below contains a description of each field on the Result
Information page.
Table 7. Fields on the Result Information Tab
Field | Description |
---|---|
Command | Contains the SQL command. |
Error Description | Indicates the reason the job was put on hold or aborted. |
Estimated Cost | Indicates the estimated database cost. |
Threshold Cost | Indicates the user's threshold cost. |
Result Rows | Indicates the number of rows returned in the result set. |
Max Result Rows | Indicates the user's threshold for the maximum number of rows in a result set. |
Result Table Name | Contains the name of the result table. |
Result Destination | Contains the name of the alternate result destination. |
Result Table Owner | Contains the name of the result table owner. |
Query Source | Indicates the application that created the job. |
Data Source | Indicates the data source against which the query was run. |
To view time information, click the Time Information tab on the
Detailed Job Information window. The Time Information page is
illustrated below:
The table below contains a description of each field on the Time
Information page.
Table 8. Fields on the Time Information Page
Field | Description |
---|---|
Created | Indicates the date and time the query was created. |
Updated | Indicates the date and time the query was updated, if applicable. |
Started | Indicates the date and time the query began execution, when applicable. |
Completed | Indicates the date and time the query completed, if applicable. |
Notified | Indicates the date and time the user notification of query completion was sent, if applicable. |
Start After | Indicates the date and time after which the job can be scheduled for execution. |
Elapsed Time | Displays the number of seconds it took the job to run. |
Max Elapsed Time | Indicates the maximum number of seconds allowed for the job run time. |
System Time | Indicates the system CPU time in seconds that were used to run this job. |
User Time | Indicates the user CPU time in seconds that were used to run this job. |
To view general information, click the General tab on the Detailed
Information for Job window. The General page is illustrated
below:
The table below contains a description of each field on the General
page.
Table 9. Fields on the General Page
Field | Description |
---|---|
Submitting Node | Contains the name of the node from which the query was submitted. |
Submitter ID | Contains the ID of the user who submitted the job. |
Explain ID | Contains the ID of the SQL statement in the explain cache. |
Priority | Displays the priority level for the query. |
Queue ID | Indicates the job queue ID. |
Predecessor | Identifies which job must complete successfully before this job can be scheduled for execution. |
User Notified | Indicates whether or not the user was notified of query completion. Valid values are Yes or No. |
Process ID | Contains the process ID of the executor component. |
Node ID | Contains the node ID on which the job was executed. |
Text Hash | Contains a hash value used to limit the number of rows scanned to find a specific SQL statement. |
Security Hash | Contains the hash value used to ensure that job data is not modified. |
To refresh the Detailed Job Information window with the most current information, select Refresh from the File menu.
To close the Detailed Information for Job window, select Close from the File menu, or close the window.
Typically, you will use a 32-bit query application to submit queries. However, you can use QueryMonitor to submit simple SQL statements to the DB2 Query Patroller system.
To submit a new job, complete the following steps:
A pop-up menu displays.
The Job Operations window displays.
The job list is refreshed and displays the new job.
If you are finished viewing a result set, you can drop the result set to free up space in the database.
To drop a result set, complete the following steps:
Note: | To select more than one job, press and hold the Shift key while selecting jobs. |
A pop-up menu displays.
You can use QueryMonitor to modify job status.
To cancel or queue a job, complete the following steps:
Note: | To select more than one job, press and hold the Shift key while selecting jobs. |
A pop-up menu displays.
The Job Operation window displays.
Note: | It may take a few moments for the job list to refresh. |
You can use the QueryMonitor to resubmit a job that has already completed.
To resubmit a job, complete the following steps: