Besides IREF, another key indicator of a database's health and efficiency is the Synchronous Read Percentage, or SRP. When DB2 has good indexes available to retrieve rows for result sets, it will use synchronous I/O to access precisely just the index and data pages required. When indexes are missing, or the physical design is otherwise sub-optimal, DB2 will resort to using asynchronous prefetch I/O to scan index or data pages. Scans are "evil" (a word borrowed from my teenage daughter) and should be avoided as much as possible, especially for OLTP databases.

The Synchronous Read Percentage (SRP) metric:

[ 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 .. ]

What type of database do you have?

You might think you have an OLTP transactional database. Or, you might think you have a Data Warehouse database. But what does your database think? How is it, or the queries within it, really performing?

The Average Result Set Size

[ more .. ]

When Kim Moutsos contacted me about the opportunity to blog for DB2 Magazine, I was thrilled. What better way, I thought, could there be to help DB2 users improve the performance and security of their databases than to blog about these topics?

I told Kim that one of the first things I'd like to do would be to teach online excerpts from my IDUG Education Seminar "DB2 LUW Performance Diagnosis Lab".

So, let's get started.

[ more .. ]

I've been writing the DB2 LUW Performance blogs on www.IBMDatabaseMag.com for about three years. The site used to be called DB2Mag.com but IBM "upgraded" it about a year ago. Unfortunately, the IBM DB2 community is about to suffer the loss of a very valuable information asset --- IBM intends to stop printing IBM Database Magazine and www.ibmdatabasemag.com is scheduled to go off the air around 30 April 2009. So, the DB2 LUW Performance blogs need a new home - even if temporary. Here they are. I hope you continue to enjoy this valuable reference material.

Best regards,
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
IBM Data Champion