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 DB2 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:

1. To determine the maximum number of queries currently running against 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 the active number of 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 is locked indefinitely, DB2 Query Patroller will appear to be hung as it waits for the database lock to be freed.

[ DB2 List of Books | Search the DB2 Books ]