IBM 5655-DB2, 5695-DF1 manual Synchronous Writes, Immediate Write Threshold, Write Quantity

10.3.2 Synchronous Writes

Synchronous writes occur exceptionally, when:

•The virtual buffer pool is too small and the immediate write threshold (IWTH, see 10.3.3, “Immediate Write Threshold” on page 108) is exceeded.

•More than two DB2 checkpoints have been taken during the execution of a unit of work, and an updated page has not been written out to disk.

When the conditions for synchronous write occur, the updated page is written to disk as soon as the update completes. The write is synchronous with the application program SQL request; that is, the application program waits until the write has been completed. These writes are shown in the DB2 accounting trace (See F in Figure 33 on page 109) and in the DB2PM statistics report (see G in Figure 35 on page 110).

10.3.3 Immediate Write Threshold

The immediate write threshold is set when 97.5% of all pages in the virtual buffer pool are unavailable, and cannot be changed. Monitoring buffer pool usage includes checking how often this threshold is reached. Generally, you want to set virtual buffer pool sizes large enough to avoid reaching this threshold.

Reaching this threshold has a significant effect on processor usage and I/O resource consumption. For example, updating three rows per page in 10 sequential pages ordinarily requires one or two asynchronous write operations. When IWTH is exceeded, the updates require 30 synchronous writes.

10.3.4 Write Quantity

DB2 writes a variable number of pages in each I/O operation. Table 25 on page 108 shows the maximum pages DB2 can write in a single asynchronous I/O operation. Some utilities can write twice the amount shown in this figure. The actual number of pages written in a time interval can be obtained from the DB2 PM statistics report. For an example, see I in Figure 35 on page 110.

Table 25. Maximum Pages in One Write Operation

Page Size	Maximum Pages
4K	32
8K	16
16K	8
32K	4

10.3.5 Tuning Write Frequency

Large virtual buffer pools benefit DB2 by keeping data pages longer in storage, thus avoiding an I/O operation. With large buffer pools and very high write thresholds, DB2 can write large amounts of data at system checkpoint time and impact performance.

The DB2 administrators can tune virtual buffer pool parameters to cause more frequent writes to disk and reduce the impact of the writes at system checkpoint. The tuning parameters are the DWQT and the VDWQT. The DWQT works at virtual buffer pool level, while the VDWQT works at data set level.

108Storage Management with DB2 for OS/390