108 Storage Management with DB2 for OS/390
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 Fin Figure 33 on page 109) and in the DB2PM statistics report (see Gin
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 vir tual buffer
pool are unavailable,and c annotbe 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,updat ing 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. Table25 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 ofpages wr ittenin a time interval can be obtained from the DB2
PM statistics report. For an example,see Iin Figure 35 onpage 110.
Table25. MaximumPages in One Write Operation
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.
PageSize MaximumPages
4K 32
8K 16
16K 8
32K 4