Default bufferpool

CHNGPGS_THRESH 60 %

The new larger bufferpool

CHNGPGS_THRESH 60 %

More work 

for page cleaners

Figure 9 7   Visualizing CHNGPGS_THRESH parameter

9.5.4  Large transactions

By default, databases are created with relatively small space for transactional 

logs, only three log files with each 250 pages on Windows and 1000 pages on 

Linux and UNIX. 

A single transaction should fit into the available log space to be completed; if it 

does not fit, the transaction is rolled back by the system (

SQL0964C The 

transaction log for the database is full

). To process transactions which are 

modifying large numbers of rows, adequate log space is needed.

The currently total

 log space 

available for transactions can be calculated by 

multiplying the size of one log file (database parameter 

LOGFILSIZ

) and the 

number of logs (database parameter 

LOGPRIMARY).

From the performance perspective, it is better to have a larger log file size 

because of the cost for switching from one log to another. When log archiving is 

switched on, the log size also indicates the amount of data for archiving. In this 

case, a larger log file size is not necessarily better, since a larger log file size may 

increase the chance of failure, or cause a delay in archiving or log shipping 

scenarios. The log size and the number of logs should be balanced. 

The following Example 9 33 allocates 400 MB of total log space.

 Chapter 9. Testing and tuning 

303