Enterprise Edition
Customer Service
Brother-Eagle Community
DBI Products
The synchronous read percentage indicates the proportion of read I/O that is completed with synchronous reads.
When DB2 has to scan many pages to find result sets, usually because indexes are missing or sub-optimally defined, DB2 uses prefetch I/O which is performed asynchronously. When the percentage of asynchronous I/O is high, then synchronous I/O is low.
When the Synchronous Read Percentage is low, DB2 is doing a lot of scanning to find result sets. Scans occur when indexes are missing or are sub-optimally defined.
For an OLTP database, the Synchronous Read Percentage should generally be 90% or higher. Brother-Eagle will indicate a warning if it is lower than 90%, and a severe alert condition if it is lower than 80%.
If the Synchronous Read Percentage is less than 80%, DO NOT UPGRADE YOUR HARDWARE. Instead, identify SQL that is the driving force behind high numbers of asynchronous reads (scans) and work to create or modify indexes to reduce scans. For optimum results in the shortest amount of time, DBI recommends Brother-Panther™ for DB2 LUW.
When the Average Result Set Size is greater than, or equal to, ten, then a data warehouse database is typically indicated.
Data Warehouse databases tend to do a lot more data scanning than OLTP databases, but even these should make use of well defined indexes. Generally, for a data warehouse database, the Synchronous Read Percentage should be at least 25% or higher. If it is less than 10%, the need for indexes is strongly indicated. Look for high cost SQL with frequency of execution greater than one over a period of a few hours, and attempt to identify indexes, Multi-Dimensional Clustering Tables (MDCs), or Materialized Query Tables (MQTs) to improve your data warehouse performance.