Let’s start this discussion candidly – I am a big fan of the FETCH FIRST n ROWS ONLY clause coded in a SQL statement. That assertion will require some clarification, since there are some valid use cases, and others that make little sense. Regardless, I will try not to over-complicate the discussion.
By now, most DB2 for LUW users have probably heard about the release of DB2 11.1 that became available in June of this year. In this article I’d like to provide more information on some performance optimizations in DB2 11.1 that will benefit queries accessing both row and column-organized tables.
There has been quite a bit of attention paid to the access path stability features of DB2 for z/OS since they first appeared in DB2 9. Enhancements have followed in each subsequent release. This article will discuss these features.
Scenario – your query is performing poorly. You see DB2 picked an access path that is less than optimal. First you make sure there are appropriate indexes. You look to see if the statement is written in a way that enables DB2 to use those indexes. Then you check the Filter Factors of the predicates.
Dynamic SQL statements can remain in the dynamic statement cache for a long period of time. When the statement gets refreshed, its access path could change dramatically. This article shows one way of limiting the impact of such an access path shift.