Notable Db2 Usability Enhancements in Latest IBM i TRs

 Fall means it is the time of year that we’re treated to a beautiful set of colors outdoors and that another round of IBM i Technology Refreshes have been announced.  As is the norm, the latest round of technology refreshes includes additions that enhance my favorite database, Db2 for i.

 I believe that this most recent round of Db2 for i enhancements really improves usability. One of the more notable enhancements in the Db2 performance tooling is backing away from the use of scientific notation in the Visual Explain tooling. Our IBM Expert Labs team frequently helps clients use Visual Explain more effectively to analyze and tune the performance of slow-running queries. While helping clients become more proficient with Visual Explain, I often heard requests to make the statistical data easier to understand by eliminating the use of scientific notation as shown in the following figure.

 

 

Visual Explain returns a ton of information that can be challenging to digest and some users (myself included) found that also being asked to perform mental math on the scientific notation (i.e., 1.231E7 means the table has 12.7 million rows) was too much to ask. Now, the Visual Explain interface does the math for you as demonstrated below.

 

This enhancement is only available when using Visual Explain with Db2 performance data (e.g., plan cache snapshot) captured after applying the Database Group PTF level associated with the IBM i 7.5 and 7.6  Technology Refreshes.

 

The latest round of Db2 enhancements also improve usability by eliminating work. Cleaning up old index advisor data is a best practice that I continually share with clients, but not everyone chooses to implement this recommendation. With these latest advancements, Db2 automatically will remove  old index advice.  Any index advice that is older than 365 days (according to the LAST_ADVISED column) will automatically be deleted from the SYSIXADV table in QSYS2 on a daily basis. 

 

You can change the definition of old related to index advice on your system with the IBM provided global variable named QIBM_SYSIXADV_BY_DAYS. For example, if you wanted index advice greater than 6 months old  to be automatically removed by Db2, then you would just run the following SQL statement to change the global variable default to 180.

CREATE OR REPLACE VARIABLE SYSIBMADM.QIBM_SYSIXADV_BY_DAYS

   INTEGER DEFAULT 180

 

The same automatic cleanup is also available for the error logging table used by the SQL Error Logging Facility (SELF) via the QIBM_SELF_BY_DAYS global variable.

 

On the performance front, parallel index creation - an overlooked capability provided by the Db2 Symmetric Multiprocessing (SMP) feature – has also been enhanced. The algorithm used to create an index with parallel processing was enhanced to use less temporary storage during the index creation process. Some internal tests showed a 99% reduction in the use of temporary storage! If you’re thinking of using Db2 SMP for parallel index creation, don’t forget to follow SMP best practices.

 

These are my favorite, notable Db2 enhancements in the newly announced IBM i Technology Refreshes, visit this website for a more complete list of the enhancements for IBM i 7.5 & 7.6.