Everything You Wanted to Know About (DB2) IO But Were Afraid to Ask

In the modern days, we expect the database to do majority of the operations in-memory. In reality though, very often the performance and effectiveness of input-output operations is crucial for the throughput of the system.

DB2 database does IO operations in multiple ways: doing direct reads/writes, asynchronously via prefetches and page cleaners, skipping or not skipping the file system cache. This presentation is indented to explain various activities on the database, that will cause DB2 to reach the storage subsystem, demonstrate how IO request are stripped and how DB2 interacts with the operating system. Analysis will include in-depth monitoring on both DB2 side (db2pd, MON_GET_*) and on the operating system level via not only iostat, but also powerful tracing utilities like ProbeVue (AIX) and SystemTap (Linux).