Under The Radar Visual Explain Addition

 The under the radar phrase originates from World War II aviation practices where a plane would fly low to avoid being detected by the enemy’s radar system.  Over time, the phrase is also now used to describe things or people that can be overlooked. With the constant stream of enhancements to Db2 for i, it’s easy to miss some improvements that can make working with Db2 easier. 

The new Show Indexes Considered support in Visual Explain is an enhancement that falls into this category. This improvement didn’t make the cut when I highlighted Db2 enhancements back in April, but that’s only because there’s a limit to how much time and space are allocated for each blog post.

Show Indexes Considered is a significant usability enhancement to Visual Explain that should make it easier and faster to consume the performance information displayed by this tool which is part of IBM Access Client Solutions (ACS).  This new feature makes it easier to understand why your existing indexes were not used by the Db2 query optimizer. 

 

This feedback for your existing indexes has always been available in Visual Explain, but it was hard to find and difficult to understand as shown in the following figure.  On table access methods like the Table Probe method in this example, a list of the indexes considered by the optimizer is provided.  However, a cryptic numeric reason code was all that you had for feedback. 

Understanding these reason codes involved going to the Db2 for i documentation, accessing the Database Performance and Query Optimization book, finding the description of the QQQ3007 database monitor view, and then reviewing the reason code explanations documented under the Index_Names column in this view.  Just a walk in the park – NOT!

 

The new Visual Explain enhancement in ACS 1.1.9.8 makes this process a simple walk in the park. First, you just right-click on the table access method and select the Show Indexes Considered option as demonstrated in the following screen capture.

Once that option has been selected, a new window like the following will be displayed on your workstation.  You can see that the cryptic codes are still there, but they’re accompanied with a Description column to provide a detailed explanation on why an index was not used by the query optimizer.

These easily accessible explanations were quite useful when recently working with a customer who had contracted our IBM Technology Expert Labs team to assist with SQL performance tuning.  In their situation, the table had lots of great indexes for the query being run. However, none of them were usable because the indexes were not created with the same sort sequence that the application was using. The new Show Indexes Considered feature made it quick and easy to find the root cause of their performance problems.

Although this is a relatively small improvement, it should be easy to see how it can provide a boost in productivity. No changes are required on the server, you just need to upgrade your ACS version.  It should be noted that this support is currently not working when your query references a view instead of a table. This limitation will be addressed in the next version of ACS.