OLAP Setup and User's Guide

Calculating the Database

Before performing the steps in this section, see the Database Administrator's Guide to decide if the calculation would benefit from a calculator cache. The basic calculator cache is most effective when calculating a complete database. If your database has any large flat dimensions, the calculator hash table can be very effective.

Before calculating the database, run the DB2 RUNSTATS utility to update the DB2 statistics that can help you optimize queries. Also, enable the DB2 database system monitor switches to take a snapshot of the system, and reset the counters.

Then take the following steps:

• Start the calculation.
• While the database is calculating, use an operating system monitor to verify that paging is not occurring and that DB2 OLAP Server is fully utilizing one CPU. Less than 100% utilization of one CPU indicates a problem with I/O.
• When the calculation completes, take a snapshot with the DB2 database system monitor.
• Verify the hit rates for the buffer pools and tune accordingly.
• Verify that DB2 is carrying out asynchronous I/O and tune accordingly.
• Verify that the buffer pool cleaners are efficiently triggered and tune accordingly.
• Verify that all logging has taken place to primary log files and tune accordingly.
• Verify that physical I/O rates are acceptable.
• Check the number of commits issued. If the commit block parameter is set correctly, the calculation finishes with only one commit. If more than one commit occurs, use the Application Manager to adjust the commit block parameter to make use of any unused log space.
• In the Application Manager, use the Information facility from the Database menu to get information for the database you just loaded. Check the hit rate on the index cache and tune accordingly; for best results, the index cache should be large enough to cache all the keys. Check the hit rate on the data cache and tune accordingly.

If you performed any tuning during these steps, reset the DB2 database system monitor counters, and repeat the calculation. You might need to repeat this process several times, making adjustments and verifying the results until tuning is complete. When the system is tuned for calculation, repeat the data load to make sure the new settings are also effective for loading data.