OLAP Setup and User's Guide

Tuning DB2

DB2 OLAP Server stores multidimensional data in DB2 relational storage. It is very important that DB2 performance is optimal, and that your multidimensional model is well tuned for relational storage. When you configure DB2, perform any tasks you would normally perform to tune your DB2 system, such as taking a snapshot using the DB2 database system monitor.

For any DB2 system, use the following guidelines:

• Use multiple physical drives for your data. For example, you should store your log files on a separate physical drive.
• Make sure the DB2 locklist value is large enough. If you receive error messages about contention for locks in the database, you might need to increase the value of the locklist database configuration parameter.
• Make sure the DB2 temporary table spaces are large enough. During some restructuring operations, DB2 might need to access temporary table spaces in the database. If you encounter problems, increase your temporary table space size; the default value might not be large enough for the restructuring.
• When you are done tuning, turn off the call-level interface (CLI) trace and any other DB2 diagnostic facilities that might be active.

If you use DB2 Universal Database on the workstation, follow these additional guidelines:

• Use Database Managed Storage (DMS) table spaces for tables and indexes.
• Put the fact table in a separate table space that has at least 4 containers, each mapped to a separate physical drive. Put the fact table index in another table space. Specify this using the FACTS parameter in the relational storage manager configuration file; see "Using Table Spaces" for more information.
• Configure the number of I/O cleaners to be two more than the number of table spaces used.
• Put the key and dimension tables into one table space, and put their indexes into another table space. You can store the key and dimension tables in the same table space because DB2 OLAP Server uses only a small fraction of the data held in the dimension tables, and this information is held in memory, so there is no contention for key and dimension table I/O. Specify this using the TABLESPACE parameter in the relational storage manager configuration file. See "Using Table Spaces" for more information.
• Allocate as many maximum-sized primary log files as you need. Use the maximum log buffering size.
• Use multiple physical devices with one I/O server, and one table space, per device. Distributing your data across as many physical storage devices as possible can minimize I/O time.
• The number of I/O servers should be one more than the number of physical drives for the database.
• Increase the Application Heap value to three or four times the default value.
• Make sure you are using asynchronous page cleaning. The number of asynchronous page cleaners should be equal to the number of physical drives for the database.

• Put each fact table into a separate table space, and put each fact table index in a separate storage group.
• Put the key and dimension tables in a separate table space, and put the indexes for the key and dimension tables in a separate storage group.