OLAP Setup and User's Guide

Tuning the System for Run-time

When you have finished calculating the entire database, run the DB2 REORGCHK utility as described in "Defragmenting (Reorganizing) Your Relational Database". If any of the indicators are set, use the REORG utility on the table and its index. This reclaims unused space in the table and optimally organizes the table storage with respect to the index, which might improve query performance.

Before running queries, enable the DB2 database system monitor switches to take a snapshot of the system, and reset the counters.

When your users are querying the data, take the following steps:

• Use an operating system monitor to check CPU and memory utilization.
• Occasionally take DB2 snapshots and verify the buffer pool hit rates and I/O rates. Adjust the size of the buffer pools as necessary.
• Monitor page faults on the DB2 database agent processes (db2syscs). If the page fault level is consistently over 30, it is an indication that memory is over-committed.
• Adjust the DB2 OLAP Server index cache to achieve good data hit rate. A good data hit rate is between .95 and 1.0.
• Adjust the DB2 OLAP Server data cache size until returns diminish as measured by the hit rate.
• Carefully consider the effect of allowing users to make ad-hoc SQL queries against the fact table, which can affect performance.

When tuning is complete, turn off the DB2 database system monitor switches.