IBM Support

Db2 Prefetching - Understanding, Configuring, Monitoring, Tuning

White Papers


Abstract

Prefetching has always been part of the Db2 LUW© database system. However, with version 10.1, Db2 LUW© has moved from a mostly synchronous I/O model to a mostly asynchronous I/O model. Database systems upgrading from lower Db2 versions to Db2 LUW© 10.1 or higher reflect this change.

The following paper shows the daily synchronous (non-prefetch) access vs. the asynchronous (prefetch) access of a database on Db2 LUW© 9.7. After upgrading to Db2 LUW© 11.1, the same database reveals - apart from a generally higher I/O - that the ratio between synchronous I/O and asynchronous I/O is reversed. Therefore, the understanding of the prefetching system, its configuration, monitoring, and basic tuning options has become of increasing importance for database administrators who need to assure and improve the performance of a Db2 LUW© database.

This document explains the Db2 LUW© prefetcher model and shows how you configure and monitor asynchronous I/O, particularly for SQL query processing. It is meant for administrators of Db2 LUW© environments who have a fair knowledge of Db2 LUW© administrative topics and tasks. The paper is based on the author’s experience with Db2 systems in SAP environments, but the information provided will be helpful in all environments running on Db2 LUW©.

[{"Type":"SW","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PlPAAU","label":"Database Objects->Bufferpools"},{"code":"a8m500000008PkqAAE","label":"Performance"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"},{"Business Unit":{"code":null,"label":null},"Product":{"code":"SS2PTZ","label":"Db2 for Windows"},"ARM Category":[],"Platform":[{"code":"","label":"Platform Independent"}],"Version":""},{"Business Unit":{"code":null,"label":null},"Product":{"code":"SSAUQA","label":"Db2 for HP\/UX"},"ARM Category":[],"Platform":[{"code":"","label":"Platform Independent"}],"Version":""}]

Document Information

Modified date:
06 May 2021

UID

WP102804