Shared CTEs - They're Here...

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.