DB2I/O Operations 107

10.2.7 Sequential PrefetchThreshold

The sequential prefetch threshold (SPTH) is set by DB2 at 90% of each virtual
buffer pool.T his threshold is checked at two differentt imes:
• Before scheduling a prefetch operation. If the threshold has been exceeded,
the prefetch isnot scheduled.
• During buffer allocation for an already-scheduled prefetch operation. If the
threshold has been exceeded, the prefetch is canceled.
When the sequential prefetch threshold is reached, sequential prefetch is
disabledunt il more buffersbecome available. When this occurs, the performance
of operations that use sequential prefetch is adversely affected.
10.3 Data Write Operations
When an application updates data, the updated pages are kept in the virtual
buffer pool.E ventually,the updated data pages in the virt ual bufferpoolhave to
be written to disk. Write operations can be either asynchronous or synchronous,
with the execution of the unit of work.

10.3.1 Asynchronous Writes

Most DB2 writes are donea synchronouslyfrom the application program and
chained wheneverpossible. This helps performance and implies that the
application may havelong since finished, by the time its data updates are wr itten
to disk. Updated pagesare kept in the virtual bufferpool for possible reuse. The
reuse ratio can be obtained from the DB2PM statistics report. See Jin Figure 35
on page 110, for an example.
Updated pages are written asynchronously, when:
• A checkpoint is taken, which happens whenever:
• The DB2 parameter LOGLOAD limit is reached.
• An active log is switched.
• The DB2 subsystem stops executing normally.
• The percentage of updated pages in a v irtual buffer pool fora singledata set
exceeds a preset limit called the vertical deferred write threshold (VDWQT).
• The percentage of unavailable pages in a virtual buffer pool exceeds a preset
limit called the deferred write threshold (DWQT).
Because these operations are independent from the application program, the
DB2 accounting trace cannotshow these writes. The DB2 PM statistics report is
required to see theasynchronous writes. This is shown in Hin Figure 35 on page
110.