This article is one in a series that focuses on improving performance in Db2 Warehouse. The first part provides some background information and lays out the first and most important aspect of query optimization, namely, cardinality estimation or estimating the number of rows in an execution plan.
I spend every day of my life since 1990 as a Db2 DBA both on Db2 for z/OS and Db2 for LUW. During that time, I’ve collected a lot of knowledge that makes my job easier and gets easier and gets the answers to the developer’s questions in a timely fashion.
There are tremendously powerful programming possibilities with SQL and many of these features are translatable across RDBMS’s with only a modest amount of effort. Utilizing these advanced SQL features can drastically reduce development time and improve performance, and so let's explore them!
Sparse indexing has been around for a number of versions. The history of these on the z/Os side goes back to originally helping with non-correlated subqueries in V4, then star joins in V7, to hashing support of these indexes in V10.
Calisto Zuzarte from IBM explains the ins and outs of writing SQL for performance