This is part 2 of Db2 application tuning for Db2 Explains and db2advis. We are on a mission to make Explains and the Db2 Advisor faster and more efficient. PLEASE review PART 1 as a prerequisite so that the information flow is fresh in your mind before continuing. This blog picks up where Part 1 left off.

[ more .. ]

Would you like Db2 LUW Explains and db2advis.exe to run faster and more efficiently? If so, then this blog is for you! Explain and Db2Advis are applications just like any other, and their performance can be improved by adding indexes to EXPLAIN and ADVISE tables!

In this new series of Db2 LUW Performance blog posts, we will share with you our performance analysis of Db2 EXPLAIN and Db2Advis processing and SQL with the goal of ultimately providing you with CREATE INDEX commands that will make Explain and Db2Advis processing faster and more efficient.

You might be thinking "Who cares?" Well, everyone should care because efficiency is important! And if your management is hounding you for a performance fix to a failing application, you probably want your Explains and Db2Advis to run as fast as possible!

DBI Customers using DBI pureFeat™ Performance Management Suite for IBM Db2 LUW V6.3 or V7.0 will also find this analysis, and the recommended new indexes, very beneficial to DBI's Predictive Index Impact Analysis processing! Briefly, Impact Analysis discovers all of the SQL contributing I/O to a table, then Db2 Explains are run with and without contemplated indexes to ascertain the benefits and risks, if any, of creating a new index.

[ more .. ]

I've recently had the good fortune of reviewing the book IBM DB2 9.7 Advanced Application Developer Cookbook by author Sanjay Kumar. Sanjay will be the special guest of The DB2Night Show™ on 15 June 2012 at 10am CDT for Episode #85 talking about OLAP, Stored Procedures, Optimization Profiles, PL/SQL, SQL tips, and more! Frankly, I think the book title does this book a disservice, for the content is much broader than just application development. DBAs should also find the book to be a very useful resource...

[ more .. ]

On Christmas Day, while delayed at the Atlanta airport, I wrote a quick tutorial blog on using the vi (very intuitive) editor. In my haste to get on my delayed flight, I ran out of time to share some more important vi "how-to's" with you. So, here's "Part 2" which includes more vi techniques and tricks...

[ more .. ]

I recently spoke with a customer that was rehosting a mainframe DB2/CICS/COBOL application to pSeries AIX. I did this in the mid-90's. When you find yourself in the UNIX or Linux world, you need to know how to use the VI editor. Since I'm now stranded in the ATL airport on Christmas day, this mini VI class is my "gift" to the DB2 LUW community.

[ more .. ]

OH WOW! Right now I am slapping myself on my back so hard that it hurts! In an earlier blog post on I/O Optimization and Solid State Disk (SSD), I referenced a recent IBM DM Magazine article and discussed an improved weighting formula for determining which tablespaces would benefit most from SSD storage. WELL, I just created a masterpiece SQL Snapshot command that implements best practices weighting factor calculations for SSD! This SQL Snapshot command is amazing...

[ more .. ]

DB2 LUW Database Professionals: If you can only get budget to attend ONE class this DECADE, I strongly encourage you to do whatever it takes to get yourself enrolled in my NEW Ed Seminar DB2 for LUW Performance Analysis and Tuning Workshop being given at IDUG in Tampa, Florida, on 10 May 2010.

For this lab, you should bring with you copies of SYSIBMADM Snapshot Catalog Views (detailed instructions and scripts will be provided separately). In this lab, you will receive dozens and dozens and dozens of SQL Snapshot commands (on CDROM) to analyze your database's performance data, you will learn what the numbers and metrics mean, and you will graduate fully equipped to conquer amazing performance tuning miracles for your organization...

[ more .. ]

Recently a customer wrote me and asked if there was a way to run a query that would report on any pending (deferred) DB CFG or DBM CFG changes. More specifically, he wanted to create an alert in DBI's Brother-Hawk™ that would alert him if any changes would occur upon the next database restart. With V9+ SQL Snapshots, this is easy...

[ more .. ]

Very few DB2 LUW performance blog posts have delivered so many emails to my Inbox as the prior posting on VOLATILE tables. One of our blog readers brought some very important information to my attention that I feel an obligation to share...

[ more .. ]

Do you have a table that has statistical mood swings? Sometimes the table has a few rows, and sometimes it has many? Or would you like to strongly encourage the DB2 optimizer to use available indexes? VOLATILE isn't a nice description for a person, but you can assign this label to a moody table and often get excellent results. More...

[ more .. ]

Are you Joe the DBA? Or Jane? Do you have questions about DB2 LUW Performance? Would you like to learn about DB2 Performance in real-time? Join the studio audience for The DB2Night Show and get the latest tips, techniques, and answers to your questions --- like this question about PCTFREE that we recently received.

[ more .. ]

We just completed our Webinar on DB2 LUW Index Design Best Practices and Case Studies. Clustering Indexes were discussed, and one of the Webinar participants sent an email asking this question:

Great webinar today, thank you. A quick question on clustering indexes. When trying to determine the best columns by looking at the highest aggregate sort time, what should one do if there is already a cluster index on the table? That is, what to do if the existing cluster index is suspected to not be the right one? Here's my answer...

[ more .. ]

Congratulations on hunting your elephants. The prior blog post was the most popular ever; it has received well over 3,000 hits and continues to ascend in popularity. I readily concede that elephants can wreak havoc in your databases, but there exists a much more dangerous predator of your database's performance: mosquitoes.

Mosquitoes are, on an individual basis, small, inexpensive statements with an apparently low timeron cost, but, when they are executed with high frequency, they will suck the life out of your system and degrade the performance of your business. Two blog posts ago, the topic of statement workload analysis was introduced. If you have not read it yet, please do so now.

[ more .. ]

Hopefully you read the prior blog post on elephants and mosquitoes which discusses different approaches to statement performance analysis. If you haven't read it yet, please do so now. In this post, we will look at ways to hunt and kill your elephants. And, by no means do I favor cruelty to animals in any way, this is just a metaphor. Let us imagine that your phone just rang and your boss is screaming "What's happening RIGHT NOW?!?!?!!?!?" ...

[ more .. ]

It's said that there is more than one way to skin a cat, meaning there are multiple ways to accomplish the same objective. As for the saying, I don't think this is very kind to cats even though I prefer dogs. The prior blog post discussed making adjustments to CHNGPGS_THRESH to reduce, avoid, or mitigate transient "brown outs" in transaction throughput. There is another way...

[ more .. ]

A student who took this class last week at WDUG emailed me the day after to thank me. He said he enjoyed the class and successfully reduced the elapsed time of a troublesome query from two hours to three minutes! If you missed the WDUG session, I hope you will be able to join my Ed Seminar at IDUG "DB2 LUW Performance Diagnosis Learning Lab". This post covers topics related to optimizing Write I/O performance.

[ more .. ]

The database is often presumed guilty if there is a performance issue. But your database seems fine; all the symptoms of good performance are present, so now how do you prove the innocence of your database?

In the prior blog post, I provided a checklist of some metrics that would help you assemble your defense if it was asserted that your database was the source of performance problems. But those ratios and indicators are just circumstantial evidence of probable innocence. Here comes the DNA test. It's hard. It's time consuming. It's complex. But the analytical effort just might help get you out of the hot seat and properly direct a performance issue to application or networking teams...

[ more .. ]