IBM Books

DB2 Query Patroller User's Guide


Using QueryMonitor

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:
QueryMonitor main window


Default Columns in the QueryMonitor Main Window

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:

  • aborted

  • cancelled

  • done

  • estimating

  • hold

  • queued

  • running

  • scheduled

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.


Push Buttons on the QueryMonitor Main Window

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.


Displaying a Job List

To display a job list, complete the following steps:

  1. Start QueryMonitor.

  2. Click List Jobs.

    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.

Specifying Which Jobs to Display

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:

  1. Start QueryMonitor.

  2. Select the filter criteria as follows:

    1. Type the name of the node in the Node Name field to search for all jobs on a specific node.

    2. If you are the administrative user or operator, type the user name in the User Name field and press the Enter key to list all jobs for a specific user.

    3. Select a status in the Job Status field to list all jobs with a specific status.
      Note:Whenever you change the Job Status field, the job list automatically redisplays.

  3. If you did not change the Job Status field, click List Jobs to refresh the job list. The job list summary displays all jobs that match the specified criteria.

To clear the filtering criteria, complete the following steps:

  1. Click Clear Filter.
    Note:If you specified a job status other than All, the job status reverts to All and the job list automatically redisplays.

  2. If you did not change the job status field initially, click List Jobs to refresh the job list.

Choosing Columns

Whenever you are displaying the job list, you can choose which columns you want to display.

To choose the columns, complete the following steps:

  1. Click Choose Columns.

    The Select Columns for Display window displays as shown below:
    Columns for Display Window

  2. To add a column to the display, do the following:

    1. Select the column to be added from the Available Columns list.

    2. Click Add>> to add the column to the Selected Columns list.

    3. Select the newly added column in the Selected Columns list.

    4. Click Up or Down to move the newly added column to the position where you want it to be displayed.

  3. To remove a column from the display, do the following:

    1. Select the column to be removed from the Selected Columns list.

    2. Click <<Remove.

  4. Click Apply when you have finished adding and removing columns.
    Note:Any changes you make to the columns displayed are retained for the current session only.

Reordering the Job List by Column

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.

Resizing Columns

You can resize any column in the job list.

To resize a column, complete the following steps:

  1. Move the mouse pointer to the vertical line forming the right edge of the column heading.

    The mouse pointer changes to a double arrow.

  2. Left-click and hold the mouse push button on the vertical line. Move the line to resize the column.

  3. Release the mouse push button.
    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.

Refreshing the Job List

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.


Displaying Detailed Job Information

From the QueryMonitor main window, you can select a specific job and drill down to view detailed information about that job.

  1. Select the job that you want to display detailed information for.
    Note:To select more than one job, press and hold the Shift key while selecting the jobs.

  2. Right-click on Job Operations. A pop-up menu opens.

  3. Select Job Detail.

    Detailed job information displays for each selected job in a separate Detailed Information for Job window similar to the one shown below:
    Detailed Information for Job Window
    Note:You can also double-click a job to display detailed job information for that job.

  4. Select one of the following tabs to view specific job detail information:

Fields Above the Tab Section in the Detailed Information for Job Window

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:

  • estimating

  • queued

  • held

  • scheduled

  • running

  • done (completed successfully)

  • aborted (completed unsuccessfully)

  • cancelled

Job

Indicates the job type. Valid values are:

  • database. Indicates database command

  • OS. Indicates operating system command

Result Status

Indicates the result status. Valid values are:

  • purged due to abort

  • truncated

  • not existing

  • exists

  • dropped

Cancellation Status

Indicates the status of any cancellation requested for this job. Valid values are:

  • no cancellation - no cancellation has been requested

  • cancellation requested - cancellation was requested but not yet accepted

  • cancellation accepted - cancellation accepted by DB2 Query Patroller

Displaying Result Information

To view result information, click the Result Information tab on the Detailed Information for Job window. The Result Information page is illustrated below:
Detailed Information for Job Window Showing the Result Information Page.

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.

Displaying Time Information

To view time information, click the Time Information tab on the Detailed Job Information window. The Time Information page is illustrated below:
QueryMonitor Main Window Time Information Tab

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.

Displaying General Information

To view general information, click the General tab on the Detailed Information for Job window. The General page is illustrated below:
QueryMonitor Main Window General Information Tab

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.

Refreshing the Detailed Information for Job Window

To refresh the Detailed Job Information window with the most current information, select Refresh from the File menu.

Closing the Detailed Information for Job Window

To close the Detailed Information for Job window, select Close from the File menu, or close the window.


Submitting a New Job

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:

  1. Right-click on Job Operations.

    A pop-up menu displays.

  2. Select New Job.

    The Job Operations window displays.

  3. Enter the complete SQL statement in the text box.

  4. Click OK.

    The job list is refreshed and displays the new job.


Dropping a Result Set

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:

  1. Select the job for which you want to drop results. The selected job must have a status of done.
    Note:To select more than one job, press and hold the Shift key while selecting jobs.

  2. Right-click Job Operations.

    A pop-up menu displays.

  3. Select Drop Result Set.

  4. Click one of the following push buttons:

Modifying Job Status

You can use QueryMonitor to modify job status.

To cancel or queue a job, complete the following steps:

  1. Select the job you want to cancel or queue. The selected job must have a status of estimating, hold, queued, or scheduled.
    Note:To select more than one job, press and hold the Shift key while selecting jobs.

  2. Right-click on Job Operations.

    A pop-up menu displays.

  3. Select Modify Status.

    The Job Operation window displays.

  4. From the drop down list select either Cancel or Queue. The Queue option is only available to operators and administrative users.

  5. Click Yes to change the job status, or click No to cancel.
    Note:It may take a few moments for the job list to refresh.

Resubmitting a Job

You can use the QueryMonitor to resubmit a job that has already completed.

To resubmit a job, complete the following steps:

  1. Select the job you want to resubmit. The selected job must have a status of done.

  2. Right-click Job Operations. A pop-up menu displays.

  3. Select Resubmit Job.

  4. Click Yes to resubmit the job, or click No to Cancel.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]