OLAP Setup and User's Guide

Managing the Size of Your Database Log File

When DB2 OLAP Server loads and calculates data, rows are inserted into and updated in the fact and key tables. These actions cause DB2 to write records to the DB2 log files. By default, loading and calculating a multidimensional database are single transactions. If the OLAP database is large, many log records will be written, and DB2 will require a large number of log files.

If there is a failure during calculation, DB2 uses the log files to recover the database. After recovery, the database is restored to the state held before the beginning of the transaction. Any calculations from the failed transaction will be lost, and you will need to run the calculation again.

You can manage the size of your database log file in one of two ways:

• By setting the Commit Block parameter.
• By allocating enough space in your database log file to hold the entire cube during a recalculation or load operation.

To improve performance:

• On Windows NT and UNIX, set the log file size to the maximum.
• On Windows NT, UNIX, and OS/390, store log files on separate physical devices.

See Chapter 9, "Enhancing DB2 OLAP Server Performance" for more information.

Setting the Commit Block Parameter

This section refers to a parameter that you can access only in the Application Manager or the ESSCMD command line interface. These interfaces are not available with the DB2 OLAP Starter Kit.

Using the synchronization point settings, Commit Block and Commit Row, you can control how often DB2 OLAP Server commits changes during data load and calculation transaction. A single data load or calculation transaction can be handled as a series of small transactions. Frequent transactions can adversely affect performance.

Tuning the Commit Block and Commit Row parameters has two benefits:

• On workstation servers, DB2 needs less log-file space because it needs to hold records for only the actions between commits.
• On workstation and OS/390 servers, if a failure occurs, DB2 returns the database to its state at the last commit. If the problem is corrected, and the data load or calculation is restarted, in many circumstances, the time that it takes to complete the task will be significantly shorter than the time that it would take to start the task again.

Each time a commit is issued, the data and index caches for the multidimensional database are flushed, and the changes are committed in the relational database. Each synchronization point that you set can be time-consuming in terms of performance, so you will need to strike a balance between the need to reduce the log file size and the need to achieve optimum product performance.

By default, the Commit Block parameter is set to 3000. To change this parameter, in the Application Manager window:

1. Click Database --> Settings. The Database Settings window opens.
2. Click the Transaction tab.
3. Set the Commit Block parameter to a specific number of blocks.

DB2 OLAP Server issues a commit when the number of blocks specified by the Commit Block parameter are updated. If a problem occurs, you can roll back changes only as far as the last commit.

This is the recommended method of managing your database log file.

See the Database Administrator's Guide or Application Manager online help for information on the Commit Block and Commit Row settings. See your DB2 documentation to learn how to control the number of primary and secondary log files available to DB2.

Allocating Space in Your Database Log Files

If you choose not to use the Commit Block parameter, you need to allocate enough space in your database log file to hold the entire cube during a recalculation or load operation. When you recalculate a cube or load data into a cube, each of those operations is handled as a single transaction that can be very large. If a problem occurs, DB2 OLAP Server rolls back the entire transaction.

For such transactions, consider increasing the following log settings:

• Log buffer size: set to maximum
• Log file size: set to maximum
• Number of primary log files
• Number of secondary log files

See your database manager documentation for information about how to change log settings.

Other methods to help ensure that your database log file does not run out of space include:

• Creating multiple calculation scripts for a single relational cube, and running the calculation scripts in the appropriate order, one at a time.
• Loading into a relational cube in smaller amounts, choosing only portions of your data to load at one time.