Finding Inline Candidates

 A couple of months ago, I highlighted the performance benefits of Db2’s ability to inline calls to a user-defined function (UDF). While I discussed how to determine whether or not a UDF is inline eligible, I didn’t cover methods to identify UDFs that are candidates to make inline eligible.

Db2’s main requirement for inlining UDFs is that the function logic must be simple. Inlining is not an option for UDFs with complex logic which requires many SQL statements. Therefore, it would make sense to find the UDFs with a small number of SQL statements to determine if they can be made inline eligible. The good news is that the catalogs and services provided by Db2 for i make this candidate UDF search quite easy.

 

The following query uses the SYSFUNCS catalog view which contains a row for every UDF to find the functions that are currently not being inlined. This data set is then joined to the SYSPROGRAMSTAT catalog view which view returns a row for every program object which contains an embedded SQL statement. One key thing to note is that this view uses an underlying service to dynamically generate this list of program objects with calls to a UDF – the SYSPROGRAMSTAT view is not just reading rows from a table like other Db2 catalog views in QSYS2. Thus, it’s critical for performance to limit the scope of the program objects by searching for SQL program objects in a specific library (i.e., system_program_schema='KMEXAMPLE')  instead of Db2 generating a row for every SQL program object on the systems.  A couple years ago, I highlighted the importance of limiting scope for all IBM i services.

SELECT routine_schema, routine_name, number_statements
FROM qsys2.sysfuncs f INNER JOIN
       qsys2.sysprogramstat p
     ON p.system_program_name = specific_name AND
        p.program_schema = routine_schema
  WHERE f.INLINE='NO' AND
        p.system_program_schema = 'KMEXAMPLE' AND 
        p.number_statements <= 5 AND 
        p.routine_type ='FUNCTION' AND 
        p.routine_body  =  'SQL'
  ORDER BY number_statements 

 

Also notice that the SYSPROGRAMSTAT view returns a count of the number of SQL statements embedded in the program object in the NUMBER_STATEMENT column. This column makes it easy to find those UDFs whose logic is composed of a small number of SQL statements. On my system, this query returns the following result set which can now be used to determine if any of these simpler UDFs can be modified to make them inline eligible.

The first step would be reviewing the source of the UDF to determine the feasibility of making the function inline eligible.  The following source code for the CALC1 UDF1 reveals that its logic contains one of the key inhibitors to inline eligibility – an intermediate result variable (i.e., OUT1).

CREATE FUNCTION  CALC1 ( P1 CHAR(4) , P2 CHAR(2) , P3 CHAR(2) ) 
RETURNS CHAR(10)   
LANGUAGE SQL 
DETERMINISTIC 
NOT FENCED
BEGIN 
  DECLARE OUT1 CHAR (10) ; 
  SET OUT1 = CHAR(SUBSTR(P1, 1, 2), 2) CONCAT P2 CONCAT P3; 
  SET OUT1 = REPLACE(OUT1, 'A', 'X') ; 
  RETURN OUT1 ; 
END 

 

An intermediate result variable requires an SQL statement to assign a value to it.  UDFs can be only inlined if they contain a single RETURN statement, so the assignment statement for the intermediate result is a showstopper.  That’s the bad news – the good news is that frequently these intermediate result statements aren’t needed.  Often programmers just utilize these intermediate results to simplify the coding logic without understanding the performance impacts. For this UDF, the intermediate result variable and assignments aren’t needed if the UDF logic is updated to the following:

 

CREATE FUNCTION  CALC1 ( P1 CHAR(4) , P2 CHAR(2) , P3 CHAR(2) ) 
RETURNS CHAR(10)   
LANGUAGE SQL 
DETERMINISTIC 
NOT FENCED
BEGIN 
  RETURN REPLACE( 

           (CHAR(SUBSTR(P1, 1, 2), 2) CONCAT P2 CONCAT P3), 
           'A', 'X') ; 
END 

 

An alternative route to identify which UDFs to target for inlining is to find the UDFs that are being called most frequently on your system that are not being inlined by Db2. Plan Cache Snapshots can be leveraged for this type of analysis, but you’ll have to come back to the blog next month to learn about that approach,  In the interim, you have a method for identifying the UDFs with simple logic on your system that are the easiest candidates for coding changes to make them eligible for inlining by Db2.

SQL FOR Statement Delivers

 Christmas is near which means it is gift giving season, so I thought I’d highlight a powerful capability that the SQL procedural language delivers to programmers – the FOR statement.

 The SQL procedural language (SQL PL) itself was a powerful addition to the SQL Standard because it enables developers to code both their data access and application logic entirely in SQL.  No need to learn the syntax for embedding SQL in  a high-level programming language such as RPG or COBOL, all the coding can be done with SQL.

 

For the most part, the SQL PL support mirrors other programming languages with support for variables, assignment statements, conditional statements, and looping constructs. While other languages support a for loop, the SQL FOR statement is unique in the fact that it streamlines the use of a cursor to process the rows returned by a SELECT statement.

 

In a high-level programming language, the use of a cursor requires a specific logic flow that looks like the following:

DECLARE pgm1cur CURSOR FOR
  SELECT colPK, col2 FROM table1 WHERE col3>100;

OPEN pgm1cur;

FETCH pgm1cur INTO :host1, :host2;

 
while SQLCODE = 0
   /* application logic using colPK and col2 values 

      stored in host1 & host2 variables*/
   FETCH pgm1cur INTO :host1, :host2;
end;

CLOSE pgm1cur;

The cursor needs to be declared and opened. Next, the FETCH statement is used with a looping construct to process each row returned by the cursor and copy the values needed for the application logic into local variables.

The coding for cursor processing is nothing complex, but it is somewhat tedious. The SQL FOR statement stands out because it reduces the amount of code that needs to be created and maintained. As the following example shows, the coding load is reduced because the FOR statement eliminates the need for opening, advancing, and closing the cursor along with the need to copy column values into host variables.

FOR myloop AS pgm1cur CURSOR FOR
  SELECT colPK, col2 FROM table1 WHERE col3>100
DO
     /* application logic using 

        colPK and col2 values */ 

END FOR;

The FOR statement makes cursor usage so simple that one can easily overlook that they’re even using a cursor – let’s look at customer example from a recent IBM Expert Labs engagement which reinforces this. 

A client needed help optimizing the performance of their SQL stored procedures and one of their stored procedures contained the following FOR statement:

FOR myloop AS proc1Cur CURSOR FOR
  SELECT colPK, col1, col2 FROM table1 WHERE rowProcessed ='N'
DO
    SET currentPK = colPK;

     /* application logic using col1 and col2 values */

     UPDATE table1 SET rowProcessed='Y' WHERE colPK = currentPK;
END FOR;

The client’s developer coded a separate UPDATE statement to update the row which the loop was currently processing because they lost track of the fact the FOR statement provides a cursor that can be used for a positioned update.  While the client’s FOR statement functionally worked, performance was sub-optimal because the separate UPDATE statement must search and find the row that needs to be updated. 

 

The positioned update used in this optimized version of the FOR statement eliminates the search and find overhead by just updating the row that the cursor is  currently pointing to.

FOR myloop AS proc1Cur CURSOR FOR
  SELECT colPK, col1, col2 FROM table1 WHERE rowProcessed ='N'
   FOR UPDATE OF rowProcessed
DO
    SET currentPK = colPK;

     /* application logic using col1 and col2 values */

    UPDATE table1 SET rowProcessed='Y' WHERE CURRENT OF proc1Cur;

END FOR;

One performance test that I ran showed the positioned update to be two times faster. I’m guessing that a 2x performance increase would be pretty high on the Christmas wish list for most programmers!

 

That's a wrap for 2025 -  Merry Christmas and I look forward to interacting with all of you in the new year!

Thankful for UDF Inlining

 This week we celebrate the Thanksgiving holiday in the US, so I decided to write about an overlooked Db2 enhancement that I’m grateful to have as a tool in the toolbox when our IBM Expert Labs team helps clients optimize their SQL performance. 

One of the great things about the SQL user-defined function (UDF) support is that it allows programmers to use modular programming as they develop queries and reports. If there’s a calculation commonly used by your business, then good modular programming would dictate that the calculation is coded in a single program and reused as opposed to embedding that calculation in every application program that needs it.  UDFs enable this same type of code reuse, but at an SQL statement level.

 

Let’s use Costs of Goods Sold (COGS) as a common calculation or derivation that your reports frequently use.  COGS is a simple calculation that adds together beginning inventory and purchases during the period and subtracts the ending inventory. Note: You may be thinking that the conversion of a legacy date value to an SQL Date value would be a better example of a common derivation that queries need - however, remember that I've previously covered a Date lookup table as a better solution for that.

Here’s the SQL UDF definition to support the COGS calculation:

CREATE FUNCTION CostOfGoodsSold
           (beginInv DECIMAL(8,2), 

            endInv DECIMAL(8,2),

            purchasedInv DECIMAL(8,2))
 RETURNS DECIMAL(8,2) 
 LANGUAGE SQL
 NOT FENCED 
 GLOBAL DETERMINISTIC 
  RETURN( beginInv + purchasedInv — endInv)

While using the UDF is easy from a programming perspective, there is a performance cost since each call to the CostsOfGoodsSold UDF involves a call to an external program object. Invoking an external program takes much more time and resources than retrieving a column from a row in a table. Thus, the modular programming approach of using a UDF adds overhead due to the program call. 

 

IBM knows that both performance and modular programming are important, so they delivered SQL UDF inlining to allow SQL developers to get the best of both worlds. Inlining is a technique that is used by program compilers to improve performance of the generated program object. The compiler scans the program source code and notices that the main body of a program calls the same procedure or routine multiple times to perform a specific task. The compiler could decide to replace each procedure call in the program’s main body with the procedure logic itself. This inline replacement is done to eliminate the procedure call overhead to improve the overall performance. 

Db2 for i takes a similar approach with the UDF logic. Instead of taking time to invoke the external program object to perform the UDF logic, Db2 moves the function inline to the query as demonstrated in the following graphic. The SELECT statement coded in the application at the top of the figure is automatically transformed into the bottom SELECT statement when it’s run by the application program.

First, the Db2 inline support enables the application delivers to employ modular programming because their SQL statements reuse the calculation by invoking the UDF. Second, the Db2 inline support improves performance because when the SQL statement is run the UDF calculation is moved inline to the SQL statement which eliminates the overhead of the external program call. 

A simple performance test of the inline and non-inline versions of a UDF against a table with a half million rows showed a 4x performance improvement for the query using the inline version of the UDF.  This performance result reinforces how expensive it is for the database engine to perform external program calls.  

 

When the function is created, Db2 for i reviews the function definition to determine if the function is inline eligible. The main requirement is that the function logic must be simple – the function body must contain a RETURN statement or a RETURN statement wrapped within a BEGIN … END  pairing.  If the calculation requires multiple assignments and SQL statements, then that logic cannot be moved into another SQL statement.  If the UDF is a scalar user-defined function, the function must be defined with the GLOBAL DETERMINISTIC setting.  A user-defined table function must be defined with the NO EXTERNAL ACTION setting.  These are the main requirements, but the complete list of the inline requirements is documented for both scalar UDFs and table UDFs. 

 

At this point, you’re probably wondering how to verify if your existing SQL UDFs are being inlined. The SYSFUNCS view in QSYS2 contains all the details about UDFs on your system, so it’s easy to determine the inline status by running a query like the following:

SELECT routine_schema, routine_name, inline

 FROM qsys2.sysfuncs 
WHERE external_language IS NULL

 

Here’s an example of the output that you’ll receive:

The YES and NO values returned as inline status above are obvious, but what does mean if the INLINE status is Null (i.e., -) like the INT_ARRAY function in this example?  The Null value means that Db2 for i hasn’t had  a chance to determine if your function is inline eligible.  Meaning that the UDF was created before Db2 had the inline feature that was first made available on the IBM i 7.3 release via a Database Group PTF. 

 

One of the strengths of IBM i is that programs like an SQL UDF keep on running when you install a new release.  No program recompiles or recreations are needed. One downside of this run forever approach is that your program can miss out on new performance optimization like UDF inlining.  Because of this, I recommend that clients recreate all their SQL functions, SQL procedures, and SQL triggers after installing a new release of the IBM i operating system.  With this approach, you guarantee that your SQL procedural objects are leveraging all the latest optimizations delivered by the Db2 team in Rochester.

 

You should now have a firm understanding of how UDF inlining delivers the best of both worlds in terms of UDF performance and using UDFs for modular programming.  And hopefully, you're thankful for it like me!

Notable Db2 Usability Enhancements in Latest IBM i TRs

 Fall means it is the time of year that we’re treated to a beautiful set of colors outdoors and that another round of IBM i Technology Refreshes have been announced.  As is the norm, the latest round of technology refreshes includes additions that enhance my favorite database, Db2 for i.

 I believe that this most recent round of Db2 for i enhancements really improves usability. One of the more notable enhancements in the Db2 performance tooling is backing away from the use of scientific notation in the Visual Explain tooling. Our IBM Expert Labs team frequently helps clients use Visual Explain more effectively to analyze and tune the performance of slow-running queries. While helping clients become more proficient with Visual Explain, I often heard requests to make the statistical data easier to understand by eliminating the use of scientific notation as shown in the following figure.

 

 

Visual Explain returns a ton of information that can be challenging to digest and some users (myself included) found that also being asked to perform mental math on the scientific notation (i.e., 1.231E7 means the table has 12.7 million rows) was too much to ask. Now, the Visual Explain interface does the math for you as demonstrated below.

 

This enhancement is only available when using Visual Explain with Db2 performance data (e.g., plan cache snapshot) captured after applying the Database Group PTF level associated with the IBM i 7.5 and 7.6  Technology Refreshes.

 

The latest round of Db2 enhancements also improve usability by eliminating work. Cleaning up old index advisor data is a best practice that I continually share with clients, but not everyone chooses to implement this recommendation. With these latest advancements, Db2 automatically will remove  old index advice.  Any index advice that is older than 365 days (according to the LAST_ADVISED column) will automatically be deleted from the SYSIXADV table in QSYS2 on a daily basis. 

 

You can change the definition of old related to index advice on your system with the IBM provided global variable named QIBM_SYSIXADV_BY_DAYS. For example, if you wanted index advice greater than 6 months old  to be automatically removed by Db2, then you would just run the following SQL statement to change the global variable default to 180.

CREATE OR REPLACE VARIABLE SYSIBMADM.QIBM_SYSIXADV_BY_DAYS

   INTEGER DEFAULT 180

 

The same automatic cleanup is also available for the error logging table used by the SQL Error Logging Facility (SELF) via the QIBM_SELF_BY_DAYS global variable.

 

On the performance front, parallel index creation - an overlooked capability provided by the Db2 Symmetric Multiprocessing (SMP) feature – has also been enhanced. The algorithm used to create an index with parallel processing was enhanced to use less temporary storage during the index creation process. Some internal tests showed a 99% reduction in the use of temporary storage! If you’re thinking of using Db2 SMP for parallel index creation, don’t forget to follow SMP best practices.

 

These are my favorite, notable Db2 enhancements in the newly announced IBM i Technology Refreshes, visit this website for a more complete list of the enhancements for IBM i 7.5 & 7.6.

 

Watch Your Epoch

 I have to admit that I didn’t know what an epoch was until a recent customer engagement. This customer enlisted the assistance of our IBM Technology Expert Labs team to help build a solution that would continually stream IBM i system performance data to a Splunk server. The message sent to the Splunk server needed to include the epoch value associated with the time that system performance data was generated.

With this epoch requirement, our Expert Labs team was pleasantly surprised to find that Db2 for i SQL support provides an EXTRACT function which has the ability to return the epoch value associated with a timestamp value. This built-in SQL function made it  simple to return the epoch value of 1,757,182,530 seconds for the input timestamp of 2025-09-06 18:15:30 as demonstrated in the Run SQL Scripts screen captured below. 

 

A solution to generate a message to Splunk with the required epoch value was put together quickly using this EXTRACT function. As testing began, however, we discovered that the EXTRACT function generated an unexpected epoch value. The prior example was run on an IBM i server with the time zone set to US Central Time.  So, all of us working on the project assumed that the epoch value returned by the EXTRACT function would be the number of seconds that have elapsed since January 1, 1970 00:00:00 (US Central).

 

Closer examination of the epoch value returned by the SQL function showed that the time zone setting of your IBM i server does not matter. The EXTRACT function always returns an epoch value which is the number of seconds that have elapsed since January 1, 1970 00:00:00 (UTC).  Depending on your application requirements, this may be good or bad news. For our client, the epoch value needed to be based on the time zone of the server and not UTC.

 

The IBM Expert Labs team relied on another SQL capability - the current timezone special register – to address this requirement. As the following example shows, subtracting the current timezone value from the input timestamp converts the timestamp value from your system to the corresponding UTC  value so that the generated epoch value is based off your system time.  

By sharing our adventures with the epoch values returned by the EXTRACT function, hopefully you can avoid your own missteps with epoch values. 

ACS Streamlines Index Housekeeping

 One of the most common questions that our Db2 team in IBM Technology Expert Labs hears on SQL performance engagements is, Does my table have too many indexes? Given the efficiency of the Db2 for i index maintenance algorithms, I often respond by asking When was the last time you did housekeeping on the indexes for the table?.

If a table has a large number of indexes (which also includes keyed logical files), then there’s a strong chance there are probably some indexes that are no longer used by the Db2 query optimizer or your applications.  Meaning clients can often make room for new indexes by removing indexes that are no longer providing value.

 

The good news is that IBM i Access Client Solutions (ACS) for many yes has provided the ability to perform index housekeeping.  The bad news is that this ACS function was hard to find.  The even better news is that with the 1.1.9.9 version of ACS released this month, ACS provides an interface that makes it easier to evaluate the impact of your existing indexes.

 

Let’s start with the original ACS interface for index housekeeping, so that you can better appreciate the new streamlined interface delivered by ACS 1.1.9.9.  The biggest issue with the existing support is that the interface could not be accessed from the ACS SQL Performance Center - even though everyone agrees that indexes are a critical success factor for achieving good SQL performance.  The index usage feedback information was buried inside the ACS Schemas tool. 

 

The following figure shows the steps that were required to access the index usage feedback information. After launching the ACS Schemas tool, one needed to navigate to the table whose indexes they wanted to analyze and to select the Work With->Indexes option. 

After performing all these navigation steps, you were presented with a window like the following which displays the usage metrics needed to perform index housekeeping.

The 1.1.9.9 version of ACS streamlines access to these same index usage metrics. This streamlining was made possible by leveraging the launchpad that was added to the SQL Performance Center at the end of 2024. The following figure shows how the latest version of ACS adds an Index Evaluator option right in the middle of the SQL Performance Center launchpad. 

 

Once you click on the Index Evaluator icon in the launchpad, you are prompted for the name of the schema and table that you want to perform index housekeeping on.  No need to launch ACS Schemas tool, just a couple of clicks and you’re all set to start finding indexes which are just collecting dust and can be removed!

Hardware-driven Query Plan Compression

Earlier this month, IBM announced support for Power11. That announcement reminded me of a Db2 enhancement tied to the IBM Power10 portfolio which hasn’t been widely promoted. With Power10, the processors include an on-chip unit that supports hardware-based compression and decompression.

 

The Db2 for i development team decided to leverage this high-speed compression capability with its Plan Cache support. When a query running on Power10 (& Power11) servers produces a large query plan, the Db2 optimizer will store a compressed version of the query access plan in its Plan Cache. Compressed query plans means that Db2 can store more plans in the Plan Cache without increasing its overall size. More query plans increase the chances of  Db2 reusing a query plans which contributes to faster query performance since building a new query plan takes time and system resources. 

As you can see in the following figure, Db2 uses a couple of new Plan Cache properties in the ACS SQL Performance Center to highlight the temporary storage savings of query plan compression on newer Power servers. In this example, query plan compression is saving over half a gigabyte of temporary storage usage.

 

With this compression capability available, clients running with Power10 or Power11 may also want to review their Plan Cache configuration setting for the Plan Cache auto-sizer. That setting is also visible on the SQL Performance Center Plan Cache properties display as shown below.

 

 

Combining plan compression with auto-sizing enables clients to get a bigger bang for their buck when it comes to the temporary storage footprint used by Db2 for query workloads. That’s because the size of the Plan Cache not only controls the amount of temporary storage allocated for the Plan Cache, but it also impacts how much temporary storage is used to cache temporary runtime data structures to speed up future executions of the same query.

 

If the Plan Cache uses auto-sizing, Db2 will keep the same number of plans and cached temporary structures as it did before Power 10 hardware, but it will do so with a smaller temporary storage footprint. That’s due to the fact that the hit ratio drives the number of plans in the cache instead of the fixed size of the cache.  A comparison of the two sample Plan Cache configuration should make it clearer why auto-sizing can have an advantage when it comes to overall temporary storage usage by Db2 for query workloads.

 

First, let’s start with a Plan Cache using auto-sizing. On older hardware, assume that twenty thousand query plans require 2 GB of temporary storage in the auto-sized Plan Cache. With Power10 servers, we’ll assume that twenty thousand query plans could be stored in 1.5 GB of storage. 

 

In contrast, consider a Plan Cache manually set to a fixed size of 2 GB. On older hardware, it makes sense that twenty thousand query plans can also be stored in that fixed-size cache. Imagine that Power10 compression enables twenty-three thousand plans to be stored in the fixed-size 2 GB cache. While it’s great the fixed-size plan cache can store three thousand more plans with compression, keep in mind that the number of query plans dictates how much temporary storage Db2 allocates for the caching of temporary runtime structures mentioned earlier. The higher the number of query plans, the higher the number of temporary runtime structures that can be cached by Db2 for i.

 

With twenty-three thousand plans, let’s assume Db2 allocates 2 GB for cached temporary runtime structures. That means the total temporary storage footprint for the fixed-size cache is 4 GB – 2 GB for the fixed size Plan Cache and 2 GB for the cached temporary structures.  In comparison, assume that the twenty thousand plans in the auto-sized Plan Cache results in Db2 allocating 1.75 GB of storage for cached temporary runtime structures. That results in total temp storage footprint for the auto-sized Plan Cache being 3.25 GB.  Almost 20% less storage than the fixed-size cache temporary storage footprint.

 

The next logical question that pops to mind is why are not all clients using Plan Cache auto-sizing?  One of the primary reasons for this choice is that Db2’s auto-sizing algorithm prior to the IBM i 7.4 release was too conservative in its usage of temporary storage for the Plan Cache. This conservative nature often caused the auto-sizer to not allocate more temporary storage to store more query plans in the Plan Cache. Some customers’ query workloads were not able to meet their target Plan Cache Hit Ratio due to this behavior. As a result, some clients turned off the auto-sizer and manually increased the size of the Plan Cache to achieve a higher hit ratio. 

 

So, if you’re using newer Power hardware (Power10 or Power11),  it’s a good time to review your Plan Cache configuration to ensure that your query workloads are getting the most out of the temporary storage and hardware compression used by Db2 for your query workloads. If you’re not yet using newer Power hardware, then maybe it’s a good time to put a bug in the ear of your management.