IDUG Content Committee

 View Only

INDEXES–RECAP of important recent blogs from IDUG CC

By Brian Laube posted Sep 17, 2022 11:12 PM



September 2022 has a content committee primary focus on indexesIndexes are those critical Db2 objects that define uniqueness and/or importantly help us quickly find our data. 

There have been some great IDUG blogs via the content committee in recent years. This article you are reading here is a RECAP blog and pointer back to some of those important blogs from the past.  Click on the links below and let yourself be distracted.


Db2 Z introduced fast index traversal in V12.  In this article from 2017, Akiko Hoshikawa & Nina Bronnikova from the IBM Silicon Valley Lab explain this new concept.


In March of 2022, Tony Andrews gave us a blog on the basics of Db2 access path with a reminder of why indexes are important (in fact, most of Tony’s blogs on SQL and explain remind us of the importance of indexes).  In May 2018, Tony also gave a blog on SQL Sparse indexing for Db2 Z which helps clarify what is a sparse index (sparse indexes is one of those Db2 phrases we often read but sometimes don’t fully understand)


Index compression in Db2 Z has been available since Db2 V9.  Is it an under used capability of Db2 Z?  Maybe?  Is it worthwhile?  It depends!  Index compression allows you to save physical space (smaller datasets) but you must put the index in a bufferpool larger then usual 4K page size.  Index compression is more about space savings then performance.  Why?  Because the index is not compressed in the bufferpool so any index in the large bufferpool, compressed or not, will have same basic performance impact to SQL at runtime (excluding the I/O considerations).  DSN1COMP tells you which is the optimal bufferpool to use to save the most space (although, to over-simplify, most of the time the BP8K is the optimal.  A few times the BP16K is optimal.  I have never seen an index that is best in 32K and sometimes… an index is not worth compressing at all).  The full article on Db2 Z index compression is here


In June of 2018, Javier Estrada Benavides posted a blog on modern indexes for modern data.  This blog is beyond Db2 Z and Db2 LUW.  Read it and think about “modern” indexes.


Back in 2014, Fred Sobotka put together a summary blog with more useful index related blogs and presentations from IDUG and beyond.  Importantly, Fred reminds us to look on Ember Crooks’s db2commerce blog for Db2 LUW indexes.  Fred also has the link to Dave Buelke on temporal table index performance design (my over-simplification is that temporal tables do NOT need the same index as the real base table – the index requirements are likely to be different > think about it : another blog is here)


In February 2019 we had a blog for Db2 LUW on Choosing among indexes suggested by db2 advisor, using python

Joe Geller gave us an article on Db2 LUW and his top 10 performance features in Db2 10.5 and 10.1.  Granted, this is an older article on Db2 LUW from 2014, but the top performance features of the day included a number of items related to indexes, including index on expression (also great in Db2 Z) and index without null keys. 


Way back in 2012, Kurt Struyf asked “should we backup our indexes?”  An excellent question.   And the answer, as usual, is it depends.  It is a great short article on how backup strategy needs to match recovery SLA.  With regards to indexes, just because we can now backup indexes (in Db2 Z) does not mean it is a useful thing to do!  Think about it.  What is YOUR recovery objective?


A take-away for readers of this article up to this point.  Do not be afraid to use the IDUG website search bar to find interesting discussions and blogs from the past.  After you get the result list, don’t forget to limit to “blog” instead of “all content” to start reading first within blogs, otherwise, you get results from IDUG listserver… which is fine… but there can be lots returned depending upon your search phrase.