They’re Here is a famous quote from Poltergeist, a horror movie from the 1980s. In this movie, spirits visit a family’s home and appear playful at first. These spirits, however, do not remain cordial and end up wreaking havoc on the family. This turn of events shouldn’t be too much of a surprise given that this film is a horror movie.
Last June, I had given everyone a heads up about a pending
Db2 for i change for Shared Common Table Expressions (CTEs) that would be coming
soon to visit your systems. Well, to borrow the quote from Poltergeist… They’re
Here. The implementation changes to
make the Db2 for i Shared CTE support compliant with the SQL Standard are now part
of the IBM i 7.4 & 7.5 releases. This implementation change is required to
eliminate inconsistent results which were a possibility with the prior non-compliant
implementation. These changes were part
of the IBM i 7.5 GA code delivered in May 2022. For the IBM i 7.4 release, the
changes were introduced with the 7.4 Database Group PTF level #23 which was
delivered in December 2022.
While these Shared CTE implementation changes are not to be
feared like the evil spirits in the movie, they should not be ignored.
The reason that they should not be ignored is that these changes could make
some of your queries with Shared CTEs run faster or could make some of your
queries using Shared CTEs run slower. Unfortunately, recent client
interactions have made it clear to the IBM team that some clients have been
ignoring this pending implementation change despite the warnings raised in this
blog and the IBM i Memo to Users.
Before delivering the Shared CTE implementation change, the
IBM i development team did a great job delivering flagging support for the IBM i 7.3 and 7.4 releases which makes it easy to
use an SQL Plan Cache Snapshot or SQL Monitor to identify your SQL requests that
might be impacted by this change. Regrettably, it appears few clients have used
this flagging support to proactively identify and evaluate queries using a Shared
CTE.
A query with a Shared CTE running slower doesn’t sound like
that big of a threat. However, if that
same query is run frequently and processes a large amount of data, there is the
potential for your overall system performance being negatively impacted. In
fact, IBM has seen clients experience system performance issues due to queries affected
by the Shared CTE implementation change. In these cases, some rewriting and tuning of
the queries was necessary.
Instead of ignoring this pending performance behavior change in your queries, the following proactive actions are recommended:
- Before updating your 7.4 Database Group PTF level or upgrading to IBM i 7.4 or 7.5, use the flagging support to identify which SQL statements on your system use a Shared CTE.
- Before updating your 7.4 Database Group PTF level or upgrading to IBM i 7.4 or 7.5, simulate the Shared CTE coding change by adding the following predicate, AND RAND() IS NOT NULL, to your Shared CTE definition. Rerun the query after making this change and measure the performance.
- If performance is not acceptable, try to improve your query performance by rewriting it or tuning it with new indexes. The Db2 team in IBM Technology Expert Labs can help with this!
- When your systems get updated with new IBM i 7.4 or 7.5 code, make sure the following PTFs, MF70861 (7.4) & MF70868 (7.5), are applied to ensure you have the latest version of the Shared CTE code. As you hopefully gather from this update, this implementation change is significant - so it would be advantageous to stay current with the latest PTFs for Shared CTEs.
· As the old saying goes, an ounce of prevention is worth a pound of cure. Please reach out if you need help with your prevention plan.
