IBM DB2 Query Patroller User's Guide

Before You Get Started

The steps outlined in this chapter assume that you have already installed QueryEnabler, according to the instructions in the DB2 Query Patroller Installation Guide. Before using QueryEnabler, you should ensure that your administrator has created a user profile for your user account and is familiar with the details in this section.

Since QueryEnabler is a Java-based tool, you should ensure that your workstation has enough Java Virtual Machine heap space. You should set the JAVA_HEAP_SZ database manager configuration (dbm cfg) parameter to a value no less than 2000. We recommend that you set this parameter to 4096.

To update the JAVA_HEAP_SZ dbm cfg parameter, perform the following steps:

Step  1.	Log on to the system as a user with System Administrative (SYSADM) authority on the instance where you will issue your database queries. For more information on SYSADM authority and the users to which this privilege is granted, refer to the Administration Guide.
Step  2.	Enter the following command to update the JAVA_HEAP_SZ dbm cfg parameter to 4096: db2 update dbm cfg using JAVA_HEAP_SZ 4096
Step  3.	Enter the db2 terminate command if you are updating the dbm cfg file on a DB2 client. If you are updating the dbm cfg file on a DB2 server, you must stop and restart the database manager by entering the db2stop and db2start commands.

For more information on this dbm cfg parameter, refer to the Administration Guide.

In order to capture queries using QueryEnabler, you must set the DYN_QUERY_MGMT database configuration (db cfg) parameter to ENABLE for the database where the queries that you want to trap are to be run. After updating the DYN_QUERY_MGMT db cfg parameter, you can then submit queries that will be captured by the DB2 Query Patroller Server.

To update the DYN_QUERY_MGMT db cfg parameter, perform the following steps:

Step  1.	Log on to the system as a user with at least Database Administrative (DBADM) authority on the database where you will issue your database queries. For more information on DBADM authority and the users to which this privilege is granted, refer to the Administration Guide.
Step  2.	Terminate all connections to the database that you want to enable for DB2 Query Patroller by entering the following commands: db2stop force db2start
Step  3.	Enter the following command to update the DYN_QUERY_MGMT db cfg parameter to DYN_QUERY_MGMT ENABLE: db2 update db cfg for database_alias using DYN_QUERY_MGMT ENABLE where database_alias is the database alias name of the database that you want to enable for DB2 Query Patroller.

For more information on this dm cfg parameter, refer to the DB2 Query Patroller Administration Guide.

For example, to enable the SAMPLE database for DB2 Query Patroller, and then submit a query that will be routed to the DB2 Query Patroller Server, enter the following commands:

   db2stop force
   db2 update db cfg for SAMPLE using DYN_QUERY_MGMT ENABLE
   db2start
   db2 connect to SAMPLE
   db2 "select * from org"

The QueryEnabler: New Query or the QueryEnabler: Result Sets window opens if the cost of the query being run exceeds the management threshold defined in the user's profile.

If this query exists in the job table on the DB2 Query Patroller Server, the QueryEnabler: Result Sets window opens. If this query does not exist, the QueryEnabler: New Query windows opens. For now, click on Cancel to close the opened window.

If a window did not open, the query you submitted did not exceed the management threshold defined by your administrator. By default, any queries that do not exceed the Management Threshold parameter set for a user or group by an administrator will not be trapped or routed to the DB2 Query Patroller Server. If the query does not exceed the management threshold, the query will be run against the database and the result set will be returned automatically to the application. A result table will not be created on the DB2 Query Patroller Server.

If you want to work with QueryEnabler using the examples outlined in the sections that follow, your administrator must set the Management Threshold parameter for your user ID to 0 using QueryAdmin. For more information, refer to the DB2 Query Patroller Administration Guide.