Skip to main content

Techdocs Library > White papers >

Db2 Prefetching - Understanding, Configuring, Monitoring, Tuning

Document Author:

Malte Schuenemann

Document ID:


Doc. Organization:


Document Revised:


Product(s) covered:

DB2 for AIX; DB2 for Sun Solaris; DB2 for Windows NT; DB2 HPU; DB2 Universal Database for Linux UNIX and Windows

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 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 have become of increasing importance for database administrators who need to ensure 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’s 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 is expected to be helpful in all environments running on Db2 LUWę.

Db2 Prefetching v1.0.0 (final).pdfDb2 Prefetching v1.0.0 (final).pdf






AIX; HP-UX; Linux; Solaris; Windows


Db2 Prefetching

The Techdocs Library
Is this your first visit to Techdocs (the Technical Sales Library)?

Learn more

Techdocs QuickSearch