Buffer pool pages

Db2 functions that process data do not directly access page sets on disk. Instead, they access virtual copies of the pages that are held in memory in buffer pool pages.

The rows in a table are held inside these pages. Pages are the unit of management within a buffer pool. The unit of I/O is one or more pages chained together.

Db2 makes a getpage request operation whenever there is a need to access data in a page, either in an index or a table, such as when executing an SQL statement. Db2 uses random, sequential and list getpage requests. The type of getpage request is determined during the bind process for static SQL and the prepare process for dynamic SQL, and depends on the access path that Db2 chooses to satisfy the SQL request. Db2 checks whether the page requested is in the local buffer pool or is currently being read by a prefetch engine, in which case Db2 waits for the prefetch to complete. If the requested page is not found in the local buffer pool in a data sharing environment, then the global buffer pool is also checked. If the page is still not found, a synchronous I/O is scheduled.

The pages in a database buffer pool can be classified into the following types of pages:

In-use pages

Pages that contain data that is currently being read or updated. This group of pages is important because insufficient space for these pages might cause Db2 to queue or even stop work. These pages are not available to be overwritten, or stolen, by a new page of data that is read into the buffer pool.

Updated pages

Pages that contain data that has been updated but not yet written to disk storage. These data on the pages may be reused by the same thread in the same unit of work and by any other thread if row locking is used and the separate threads do not lock the same row. However, these pages are not available to be stolen and overwritten when a new page of data is read into the bufferpool.

Available pages

Pages that contain data that can be considered for reuse to avoid I/O and can be overwritten by data from a new different page that has to be read into the buffer pool. Available pages are normally stolen on a least recently used basis, but you can also specify a first-in-first-out (FIFO) page-stealing algorithm. An important subset of the available pages consists of those that have been prefetched into the pool by a sequential, list, or dynamic prefetch, but have not yet been used. These pages, like other available pages are available for page stealing. When an available page is stolen before it is used and is subsequently needed, Db2 schedules a synchronous I/O operation to read the page into the buffer pool.