This chapter provides information and outlines approaches for configuring the DB2 Query Patroller server for best performance.
There are no specific guidelines for setting the DB2 Query Patroller configuration parameters, because each data warehouse has its own set of unique attributes, such as size of the database, the complexity of the queries, the number of users supported, and the availability of hardware resources. This section describes an approach that can be taken to configure the DB2 Query Patroller server.
If feasible, profile the set of queries that will access the data warehouse through DB2 Query Patroller by submitting each query in isolation. This method provides valuable statistics, such as the cost estimate of the query, the time for the query to be executed, and the size of the answer set. Additional information, such as the behavior patterns of the end users and hardware resources, help you determine the appropriate setting for DB2 Query Patroller configuration parameters.
Once you have profiled the set of queries that will access the database, the behaviour patterns of end users, and the hardware resources, apply what you estimate to be the proper parameter configuration. In this initial phase, monitor the data warehouse during peak hours to determine if the data warehouse is being under utilized or is performing poorly in an attempt to process more queries than it can manage. If DB2 Query Patroller is configured too conservatively, the queries may not be submitted to the data warehouse even though resources are available. On the other hand, if DB2 Query Patroller configuration is too liberal, too many queries may be running against the data warehouse, causing the system to waste valuable resources paging or switching contexts.
It is important to gather information from DB2 Query Patroller in order to determine the most effective configuration. The process of gathering this information must be performed systematically. Typically, you should modify only one parameter at a time. After each modification, observe the system to determine the specific impact of the modification.
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:
SELECT COUNT(*), SUM(STATIC_COST) FROM IWM003_JOB_TABLE WHERE JOB_STATUS='R'
SELECT COUNT(*), SUM(STATIC_COST) FROM IWM003_JOB_TABLE WHERE JOB_STATUS='R' AND TIMESTAMP(DTIME_START_AFTER) < CURRENT TIMESTAMP
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.