IBM DB2 Query Patroller Administration Guide

Configuring the Data Warehouse

DB2 Query Patroller stores valuable information in DB2 tables. This information can be accessed to help configure the data warehouse. Since the DB2 Query Patroller schema resides in the same DB2 database as the data warehouse, the data warehouse should be configured to account for the requirements of DB2 Query Patroller.

Run the following queries to measure the DB2 Query Patroller requirements. These queries should be executed against the DB2 Query Patroller schema directly through DB2 and not through the DB2 Query Patroller client interface:

To avoid having these queries intercepted by the QueryEnabler tool, configure your user account to have a high management threshold. This can be done using the User Administration page of the QueryAdministrator tool.

1. To determine the maximum number of queries currently under the management of DB2 Query Patroller and the cost of the queries:

   SELECT COUNT(*), SUM(STATIC_COST) FROM IWM003_JOB_TABLE WHERE JOB_STATUS='R'

2. To determine how many jobs are in the queue state waiting to run, but are held up due to a resource limitation:

   SELECT COUNT(*), SUM(STATIC_COST) FROM IWM003_JOB_TABLE WHERE JOB_STATUS='R' AND TIMESTAMP(DTIME_START_AFTER) < CURRENT TIMESTAMP

3. To check the load on the DB2 Query Patroller nodes to determine their individual workloads:

   SELECT NODE_ID, SCHEDULED_JOBS, CPU_UTILIZATION FROM IWM003_NODE_TABLE WHERE NODE_STATUS='ACTIVE'

   Note:

   The number of scheduled jobs includes both active jobs and jobs waiting to run.

   You can run other queries against the DB2 Query Patroller schema to derive additional information. When accessing the DB2 Query Patroller schema, keep in mind that when DB2 Query Patroller is running, it is constantly accessing these tables to update status and retrieve information. Access to these tables must be shared with DB2 Query Patroller. If any of these tables are locked indefinitely, DB2 Query Patroller will appear to be hung as it waits for the database lock to be freed.