Welcome to 2021! I hope that everyone’s new year is off to a good start. A new year often brings discussion of regeneration and rebooting to start the year with a clean slate when it comes to developing good habits or dropping bad habits.
In the spirit of developing new habits for the new year, you may want to look into starting the habit of regular regeneration of your SQL routines. When an SQL function, procedure, or trigger objects gets created, you may or may know that behind the scenes Db2 generates a C program with embedded SQL to implement the specified SQL procedural logic. The efficiency of the program generation can have a direct impact on the runtime performance of your SQL routines. As part of the code generation process, Db2 tries to implement some assignments and comparisons statements with pure C code to get the best performance.
Over time, the smart developers in Rochester have expanded Db2 for i’s ability to generate pure C code in more situations to speed up the performance of your SQL functions, procedures, and triggers. However, your SQL routines can only benefit from the C code generation enhancements when they are recreated with the newer version of Db2 for i. This article shows you can how you can query the system catalogs to identify those SQL routines that have not been recreated in a while. Or you could just choose to recreate all of your SQL routines since that’s a relatively easy operation to try to see if it improves performance?
Hopefully, I’ve convinced you to add regular SQL routine recreation after Db2 for i updates to list of new habits to develop in the new year since it’s such a simple operation that may deliver faster performance.
