OLAP Setup and User's Guide

Tuning Data Loads

Before following the steps in this section, read the information about ordering data in "Loading Data into a Database". The order of the dimensions in the database, and the order in which you load the data, can greatly affect performance.

It is recommended that you load a subset of the database first, then follow the instructions in this section and in "Calculating the Database". After you finish tuning the data load and calculation, you can load the entire database.

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

While the data is loading, 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 data load is complete, take the following steps:

• Take a snapshot using the DB2 database system monitor.
• Verify that DB2 has not deleted or updated any rows in the fact and key tables. Deleted or updated rows indicate the data was not ordered correctly during loading.
• Verify the hit rates for the buffer pools and tune accordingly.
• Verify that all logging has taken place to the 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 data load 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. Make sure the index cache was large enough to cache entries for all the loaded data, and tune accordingly.

If you performed any tuning during these steps, clear the loaded data, reset the DB2 database system monitor counters, and repeat the data load.