Query Patroller

Definition updates for managed query states

The Canceled and Done query status meanings are updated as follows:

8

Canceled

8

The query was canceled, through either the Query Patroller Center or 8the Query Patroller command line, by the administrator, submitter, or an operator 8whose profile has the MONITORING privilege with edit authority. Only running, held, released, and queued queries can be canceled. 8

8

Done

8

The query completed successfully. 8 8

Note:

8

Although the query 8itself completed without error, the application may receive an error if the 8completion was caused by an external event, such as a DB2 force application.

8

8

Create Explain tables before running Query Patroller historical data 5generator

When running the historical data generator for Query Patroller, if the 5Explain tables do not already exist, the generator will create them for you. 5However, it is strongly recommended that you create the Explain tables before 5running the historical data generator. When you create the Explain tables 5be sure you create them on the same partition. Actively creating the Explain 5tables on the same partition improves the performance of the Explain facility. 5This improvement increases the performance of the historical data generator.

Checking Query Patroller log files for historical analysis

If the Explain Run column of the Query Activity 5over Time (Historical Analysis) report shows a status of Ran unsuccessfully for a query, historical data has not been generated 5for that query. Therefore, the query will not appear in any historical analysis 5reports or graphs. As documented in Version 8, to determine why the query 5was unsuccessful, you can examine the qpuser.log file.

In addition to examining the qpuser.log file, you should examine the qpdiag.log 5file.

Abnormal shutdown of the historical data generator

If you run the historical data generator and shut it down in an abnormal 6way, you will receive an error the next time you attempt to run the historical 6data generator. Examples of abnormal shutdown include:

6
• DB2 Universal Database stops unexpectedly
6
• Issuing a db2stop force command
6
• Issuing a killdb2 command

When the historical data generator shuts down abnormally, you must issue 6the following command before attempting to rerun the historical data generator:

    qp -d database generate historical_data stop

where database identifies the database that the command 6is being run against.

Dynamic query class updates

Certain query class operations no longer require Query Patroller to be 6stopped and restarted to take effect.

In the table that follows, an active query is a query whose status is Running 6or Queued.

Nested query behavior

Nested queries cannot be queued. Instead, a nested query will run immediately 6if it exceeds a threshold that would normally cause it to be queued.

Limitations by SQL statement type

Contrary to previous documentation, the queries with the following statements 6can be queued:

6
• Queries containing static SQL with host variables
6
• Queries containing an identity value function (IDENTITY_VAL_LOCAL) or 6a sequence value function, such as NEXT VALUE FOR or PREVIOUS VALUE FOR

Resolution limitation when using the Terminal Services Client

When using the Terminal Services Client at resolution 640x480 to connect 7to a remote desktop that is running the Query Patroller Center, the Submission 7Preferences window might appear blank. For the Submission Preferences window 7to display properly, you must use a resolution higher than 640x480.

New group support for query submissions

Starting in Version 8.2, DB2 Universal Database (UDB) supports user 7groups beyond operating system groups. Therefore, there is a slight change 7in the Submitter Profile to Use drop-down list 7in the Query Submission Preferences window of the Query Patroller Center.

If you are logged in, but do not have either DBADM authority or Edit privilege 7for Query Patroller user administration, you can only add or update a submission 7preference for yourself. In this case, the Submitter 7Profile to Use drop-down list contains existing submitter profiles of 7the DB2 UDB groups that you belong to, instead of just the operating system 7groups that you belong to.

If you are logged in, and have either DBADM authority or Edit privilege 7for Query Patroller user administration, you can add or update submission 7preferences for other users. In this case, the Submitter Profile to Use drop-down list contains all existing group submitter 7profiles.

Query Patroller schedule limitations

When working with schedules in the Query Patroller Center, you can use 7the Schedule window to save schedules to a file and import them later. If 7you have a schedule that you saved using FixPak 6 or earlier, you cannot import 7the schedule using Version 8.2 or later. This limitation is due to the change 7in serialization between JDK levels introduced with DB2 UDB Version 8.2.

Authorization required to use RUN IN BACKGROUND QUERY command

To run the RUN IN BACKGROUND QUERY command, you must be the submitter who 7submitted the query originally.

Creating an alias for a result table

As of Query Patroller Version 8.1 FixPak 5, Query Patroller stopped creating 7result tables in the schema that matched the authorization ID of the submitter 7of the query. Instead, Query Patroller started creating result tables in a 7common DB2QPRT schema. To allow result tables to be referenced using the schema 7of the submitter, Query Patroller Version 8.2 introduces an option to automatically 7create an alias for each new result table that Query Patroller creates. The 7result table is created in the DB2QPRT schema and the alias is created in 7a schema that matches the submitter's authorization ID.

To turn this option on or off, issue the UPDATE QP_SYSTEM command with 7the CREATE_RESULT_TABLE_ALIASES option:

7>>-UPDATE QP_SYSTEM USING--------------------------------------->
7
7>--+-DEFAULT------------------------------+--------------------><
7   '-CREATE_RESULT_TABLE_ALIASES--+-'Y'-+-'
7                                  '-'N'-'
7

Removing orphaned result table aliases

Aliases created with CREATE_RESULT_TABLE_ALIASES option are automatically 7dropped when a result table is dropped. However, there are two situations 7in which a result table may be dropped without the corresponding alias being 7dropped.

7
• When the result table is dropped manually without using the qp command 7line or Query Patroller Center.
7
• When the result table is dropped using the qp command line 7or Query Patroller Center under the authority of an operator who is not the 7submitter of the query and does not have DBADM authority.

To clean up aliases that have no corresponding result tables, a new command, 7REMOVE RESULT_TABLE_ALIASES, has been created. This command is automatically 7executed whenever result tables are purged as part of the Query Patroller 7scheduled result table purging process. The REMOVE RESULT_TABLE_ALIASES command 7obtains the list of aliases to purge using the following query:

7with a as (select tabschema, tabname from syscat.tables 
7           where type = 'A' and tabname like 'QUERY%_RESULTS'), 
7     t as (select tabname from syscat.tables 
7           where type = 'T' and tabname like 'QUERY%_RESULTS')
7  select all tabschema, tabname from a 
7  where not exists (select * from t where t.tabname=a.tabname)

7>>-REMOVE RESULT_TABLE_ALIASES---------------------------------><
7

Fenced user ID requires write access qpdiag.log file and path

Query Patroller uses some fenced stored procedures which may log entries 8to the qpdiag.log file. Therefore, the fenced user ID must have access to 8write to the qpdiag.log file and the path where the qpdiag.log file resides.