Understanding Table I/O performance is critically important to properly diagnosing the health and efficiency of a database and pinpointing problems. In fact, if I only had just a few minutes to quickly assess a database, I'd look at 3 key measurements...

[ more .. ]

Returning our attention to the question "Where does the time go?", we need to look at Direct I/O times. Direct I/O is I/O that occurs directly to disk without an intermediate visit or presence in the Bufferpools. Direct I/O is used by DB2 in support of LONG and LOB objects. Even if you think you are not using LONG and LOB objects, you are implicitly using them as these data types are found throughout the DB2 catalog.

[ more .. ]

How many rows must be read (evaluated) to retrieve one row? If DB2 lacks sufficient indexes to filter the result set according to the WHERE predicates, then DB2 will have to evaluate many, possibly too many, rows from the data pages to find result sets.

The Index Read Efficiency (IREF) Metric:

[ more .. ]