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ę.