database activation, so wait for a certain period after resetting the counters. For 

convenience, the snapshot output was directed into a file, and then analyzed 

using the Linux 

grep

/

egrep

 tool. In the example, 630 pages were written for the 

period, which gives about 

630 / (23+2+1) = 25

 pages per transaction. Looking 

at the value 

Log pages written

 it is not possible to tell what the average size of 

transactions was, because the basic DB2 read or write unit is one page (4 KB). 

Issuing only one small 

INSERT

 will force a flush of 4 KB from the log buffer to the 

disk. The partially filled log page remains in the log buffer, and can be overwritten 

to disk more than once until it is full. This guarantees that the log files are 

contiguous. 

When setting the value for log buffer, also look at the ratio between 

log pages 

read

 and 

log pages written

. An ideal value is zero log pages read, while seeing a 

large number of log pages written. When there are too many log pages read, it 

means a bigger 

LOGBUFSZ

 can improve performance.

9.5.5  SQL execution plan

When a query is issued against a database, DB2 prepares an execution plan. 

The execution plan defines the necessary steps that should be done to get the 

requested data. In order to prepare an optimal execution plan, the DB2 optimizer 

considers many elements such as configuration parameters, available hardware 

resources, or the characteristics of the database objects (available indexes, table 

relationships, number of records, data distribution). The database characteristics 

are collected manually with the 

RUNSTATS

 utility, and are stored in special system 

catalog tables. The 

RUNSTATS

 command should be executed when:

A table has been loaded with new data.

Recommendation:

 After loading data to DB2 tables, run 

RUNSTATS

 before 

starting testing.

The appropriate indexes have been created.

There have been extensive updates, deletions, and insertions that affect a 

table and its indexes (for example, 10% to 20% of the table and index data 

has been affected). 

Data has been physically reorganized (by running the 

REORG

 utility, or adding 

new containers).

The 

RUNSTATS

 command should be executed against each table in the database. 

The DB2 Control Center can be very helpful with running statistics on a group of 

tables. 

308 

MySQL to DB2 UDB Conversion Guide