DB2 LUW Performance: DB2 is ALIVE and WELL and IT'S NOT YOUR FAULT!

It is an unfortunate reality that the database and the DBA are too often presumed guilty by default. Everyone tends to want to blame the database first, even though performance degradation could be caused by network problems, storage problems, the Web server, sun spots, or poor application coding. So, as a database professional, how do you get yourself out of the hot seat and prove your database's innocence? Here's a checklist to assemble your defense:

---

1. [[ ]] Index Read Efficiency ( IREF ) is less than 10 for the database thus suggesting that good indexes are providing quality guidance to desired rows
2. [[ ]] The Synchronous Read Percentage ( SRP ) is 90% or higher thus indicating the general absence of scans and the presence of high quality indexes
3. [[ ]] Bufferpool Logical Index Reads per Transaction ( BPLITX ) is less than (The number of Selects per Transaction (SELTX) plus the number of DML per Transaction multiplied by 6), thus indicating the likely absence of costly index leaf page scans
4. [[ ]] There are no files closed ( DFC )
5. [[ ]] DB CFG LOGBUFSZ is at least 128, and preferably 256-512 if DMLTX is greater than 2
6. [[ ]] The Catalog Cache Hit Ratio ( CATHR ) is 95% or higher
7. [[ ]] Average Rows Read per Transaction for each and every table (TBRRTX) is less than 10

If you can put a check mark in the boxes for each of the aforementioned "innocence" tests, it is highly probable that you and your database are innocent and, indeed, not guilty of causing "performance beneath expectations". As a disclaimer, this is not an exhaustive list, and these defenses are largely circumstantial.

In a paternity suit, a man might claim that he doesn't know a woman, never had relations with a woman, and was not in the same geography as the woman during the suspected time frame of conception. While these are fairly convincing arguments to assert, memory is colored by the events of the day and it is possible that the man "forgot" the actual facts or materially misrepresented reality (lied under oath). Instead of circumstantial evidence and testimony, a DNA test can be used to determine matters of paternity with certainty.

Why is the DB2 instructor now blabbing about paternity suits during a DB2 educational blog? Glad you asked. In the next blog post, we're going to cover the DNA test of performance issue ownership.

Just for Fun

From the Press Release:

"This reminds me of the Pay It Forward movie and movement" said Scott Hayes, President & CEO of DBI. "Our intent is to help a lot of people, directly and indirectly. Several DBI team members have had their lives touched by cancer and diabetes, and others are active Big Brothers and Big Sisters. Good goes around. This is a good thing we are doing. We hope database professionals around the globe will participate" Hayes added.

Pay it Forward - Video Clip

Cheers,
Scott

Scott Hayes
President & CEO, DBI
IBM GOLD Consultant