Quick Techie Note
With each new version of Db2 also comes a new version of the Db2 Optimizer, embedded somewhere in the Db2 installation bundle, and you will get it whether you like it – or want it – or not.
So, for example, with Db2 v10.5 comes the Optimizer v10.5; with Db2 v11.1, Optimizer v11.1; with Db2 v11.5, Optimizer v11.5; and so on…
Sometimes, right after the Db2 upgrade, a job stands up that has prior to the upgrade always executed quickly and efficiently and never drew any attention to itself. But now, after it was recompiled (or rebound) on the new Db2 version, it suddenly runs for hours – or days – and becomes a major issue for the whole database.
Does that sound familiar?
Speaking from experience, this is practically always caused by (unwanted) changes in the Access Plan, conjured by the new version of the Db2 Optimizer, which inevitably arrived with the new version of Db2.
Actually, it may be better to call these “changes” what they really are: proper degradations – for example, switching from index access to full table scans; switching from nested loop joins to hash joins, or vice versa; to name a few. It is usually obvious from simply looking at the Access Plan where the problem is, but the recompilations/rebinds usually don’t help, because the (new) Optimizer stubbornly sticks to the new – and degraded – Access Path.
To try and fix the issue, we then try to painstakingly tune the wronged SQL statements – even though they may already be very much optimal – twist them a little bit this way, or that way, even make them less optimal than they were, just in order to persuade the Db2 Optimizer to return to the good old (and optimal) Access Plan.
And there is not much else we can do about it, is there?
Or is there?
Did you know that you can directly control the active version of the Db2 Optimizer if you are running Db2 v11.5.4 or later?
This enables you to quickly verify and confirm that your SQL query still works OK, in the new version of Db2, if you go back to the old version of the Optimizer!
And do you know how easy it is to switch back to an older version of the Optimizer?
All you have to do is set the registry variable DB2_OPTIMIZER_VERSION to your previous Db2 version and you are done. You even don’t need to restart anything (instance, database) – just set the variable and recompile your SQL statement(s)! (details on all this in the link below)
The Db2 Knowledge Centre is very clear about what the variable does to the Optimizer:
Setting it [DB2_OPTIMIZER_VERSION variable] to a previous version level disables query optimizations introduced between that level and the current Db2 version level.
So, with this simple setting you can get back to where you were before the upgrade, and life is (should be) good once again!
However, solving your performance issues this way is not a permanent solution (also not recommended by IBM Support – again speaking from experience), but just a quick workaround to get the jobs running. And a way to get the proof that the problem is indeed in the Optimizer.
For a permanent solution, you are strongly advised to open a case with the IBM Support and have them analyse the issue, find the problem, and fix it. Because there should really be no other reason why an optimal query should suddenly become a problem!
Here’s a link to the Db2 Knowledge Centre article describing this registry variable in more detail:
Good to keep all this on your mind when you do your next Db2 upgrade 😊