Db2 Gives the Gift of Cache

If you’re like me during the Christmas season, you’re devoting a fair amount of brain cycles and time trying to come up with that perfect gift idea for a family member. There are obvious Christmas gifts to avoid like clothes for little kids, a gym membership for your spouse, etc. Then, there’s the controversial gift of cash which can lead to the question of how much thought you put into the gift (if you don’t think cash is controversial, try your own internet search on: is cash a good Christmas gift).

Luckily, Db2 for i has avoided all that controversy and debate by giving you the gift of cache instead of cash. Even better, you don’t have to wait until Christmas Day for this gift since the Db2 query engine has already given you the gift of cache - way back in V5R2!.

Several months ago, I highlighted one of the cache gifts – the Plan Cache. This month, I’m going to highlight some under the radar caching associated with Plan Cache that Db2 performs automatically to improve the performance of frequently run queries on your system.

Occasionally, the access plan (aka, query plan) constructed by the query optimizer utilizes temporary data structures and access methods  to implement your query. For example, a temporary sorted list may be used to perform an in-memory sort if your query contains an ORDER BY clause like the following. 

SELECT ordID, ordQty FROM orders 

  WHERE ordCustID = :inpID ORDER BY ordDate

When a query is done running and the query plan includes a temporary data structure, Db2 sometimes decides to leave that temporary data structure on the system populated with the data from your table.  The reason that it decides to leave your table data cached in temporary data structures is that it can improve performance on the next run of that query.

If that query is run again and the data in the underlying table has not been changed, Db2 can skip the work of repopulating the temporary data structure with your table data. Depending on the amount of table data being copied, this can provide a nice performance boost to the next run of the query. If a query contains predicates that reference parameter markers or host variables like ordCustID = :inpID in this example query, then Db2 will also validate that the query is searching for the same value In addition to verifying that the table data is unchanged. If a different host variable or parameter value is being searched for by the query, then the cached data in the temporary data cannot be used.

The Db2 query engine provides feedback when it boosts your query performance with cached temporary data structures on a couple of different interfaces.  If you’re analyzing a query plan with Visual Explain (VE), you can see below that the highlighted Final Select icon has a couple of fields in the Actual Runtime Information section providing feedback on the use of cached temporary data structures.

VE provides a count of the number of times a cached temporary result was used during the execution of the query. If Db2 was unable to reuse the cached data in a temporary data structure, then it provides the last reason that the cached data could not be used. For this query, the cached data was not able to be reused because the rows in the table had had changed (e.g., Insert, Update or Delete) since the temporary data structure had been populated.

The live Plan Cache viewer in the IBM i Access Client Solutions (ACS) SQL Performance Center can also be used to get feedback on the reuse of cached data from temporary data structures. After you launch the ACS SQL Performance Center, the live Plan Cache viewer is accessed by clicking on the Show Statements button. As you can see in the figure below, this interface allows you to see cached results feedback at a statement level. Most likely, you’ll have to use the Show Statements Columns editor to get your Total Cached Results Used column to display next to the query text.

In this case, half of the query runs were able to use the table data cached in a temporary data structure instead of spending time and resources copying the same data into the temporary data structure. The application did nothing but run the query 8 times and Db2 automatically boosted the performance on half of those runs with its temporary data structure caching.

As you’ve learned here, your mileage may vary on the frequency of cached results on your system based on how often the underlying tables are changed and how often the same host variable and parameter marker values are searched. Our IBM Technology Expert Labs team has noticed several instances were 10-15% of some query executions on busy systems are benefitting from this cache as we’ve assisted IBM i clients with SQL performance tuning.

This Db2 gift of cached temporary results is not a new gift, but hopefully you have a better understanding of the query performance benefits that this type of caching can provide. Merry Christmas and I look forward to talking to you in 2024!