Why do SQL Procedures have a Program Type Setting?

 If your application uses a lot of SQL stored procedures, then you may have noticed there’s a PROGRAM TYPE setting. A stored procedure is really SQL’s version of a program object. There’s nothing in the SQL standards that connects a stored procedure to a program object.  However, many relational databases, Db2 for i included, utilize a program object in their stored procedure implementation to improve performance.

When you execute the CREATE PROCEDURE statement on Db2 for i, you may or may not know that the Db2 engine generates a C program object to implement the procedural logic  that was coded with SQL.  This enables Db2 for i to interpret the SQL-based logic a single time and store the implementation in a program object. The alternative is dynamically interpreting the SQL logic every time that the SQL stored procedure is invoked – it doesn’t take a rocket scientist to understand that performance would be unacceptable if the SQL procedure had to be interpreted on every execution.

 

The fact there’s a program object associated with your SQL stored procedure is hidden from the application. The application just needs to know the name of the SQL procedure to invoke and Db2 is responsible for finding and using the corresponding C program object. 

 

Now that you understand the association between an SQL procedure and IBM i program, the PROGRAM TYPE setting for an SQL stored procedure should make a little more sense. Many moons ago when the SQL procedure  support was first delivered with the V4R2 release in 1998, there was only a single PROGRAM TYPE setting MAIN.  Not sure where the values of the PROGRAM TYPE setting came from, but regardless of the origin MAIN meant that a C program was generated every time that an SQL stored procedure was created. 

 

When I was working on the IBM PartnerWorld team (now known as IBM Parter Plus) in the late 1990’s & early 2000’s, one of my responsibilities was helping software vendors port their applications based on other databases to the IBM i (then known as the iSeries). The Db2 for i SQL stored procedures made it much easier for vendors to port their applications.

 

A couple of years after the SQL stored procedure support was delivered, our porting team helped an Oracle-based vendor port their application to run natively on the IBM i. This software vendor’s application relied almost exclusively on stored procedures – if I recall correctly that application used over 8,000 different stored procedures. Every business function supported by the applications resulted in a significant number of stored procedure calls. As this porting project moved into the performance testing phase, the cost of the high number of stored procedure invocations was consuming a nontrivial amount of  the overall application runtime.

 

That’s when the second PROGRAM TYPE value of SUB entered the picture to give Db2 for i the option of generating a C service program object. With a service program object, IBM i only performs the activation processing once within a job (or database connection). In contrast, activation processing is performed every time that a program object is called within a job. While the performance difference is relatively small – my testing showed about almost a 10% performance improvement on a stored procedure called repeatedly within a job - the multiplicative impact it had on the vendor’s stored procedure heavy solution was significant. 

 

Another bonus is the simplicity of the coding change as shown in the following example. All that you need to do is add the PROGRAM TYPE clause to your CREATE PROCEDURE statement.

CREATE PROCEDURE simpleproc (IN i1 INT, OUT o1 INT)
  LANGUAGE SQL
  PROGRAM TYPE SUB 
BEGIN
  SET o1 = i1;
END

Your application doesn’t need hundreds or thousands of stored procedures to benefit from the performance benefits provided by the PROGRAM TYPE SUB option. If you have a single SQL stored procedure that’s called multiple times within a job or connections which is often the case with connection pools, then your application performance will increase from the multiplicative effect as well. 

Use the Force - NOT!

In a pivotal scene towards the end of the original Star Wars film, Obi-Wan Kenobi issues the iconic line: Use the Force, Luke. The ghost of Obi-Wan is urging to Luke turn off his targeting computer on his attack of the Death Star and instead rely on his feelings (i.e., the Jedi Force) to guide his proton torpedoes.

I’m sure  you’re probably thinking how in the world does the Jedi Force relates to Db2 objects on IBM i…   Db2 tables and indexes, which includes physical files and logical files, actually have a legacy force setting. That force setting is the Force Write Ratio (FRCRATIO) parameter on Db2 for i tables and indexes have the Force Access Path (FRCACCPTH) parameter.  If you’re using non-default values for these parameters, then effectively you’re turning off the targeting computer like Luke decides to do.

While turning off the computer works for Luke (I figured a spoiler alert wasn’t required for a movie that I saw as a kid) – hopefully, the “NOT!” in this entry title makes it clear that’s not a recommended practice on IBM i. These force settings were introduced at a time (in the 1980’s) when computer hardware was less reliable. Thus, there may have been situations in the old days where applications needed the ability to ensure critical data changes were forced from memory to permanent storage. While this may sound appealing for some applications, be aware that there is a performance cost to using these force settings.

If a non-default value of 1 is specified for the FRCRATIO parameter on a Db2 table, then every new row or changed row will result in the associated data being forced to disk while the application waits. The default behavior enables the program to continue processing while the row data is saved to memory and depends on the IBM i operating system to decide when the best time is to write the changed data to disk. The non-default behavior will also prevent other jobs from performing blocked reads on the Db2 table with the force setting which can also negatively impact system performance.

The FRCACCPTH setting available for Db2 indexes can have a similar negative impact on performance. A setting of *YES can degrade performance since it causes the application program to wait while Db2 forces every change to the access path (i.e., index tree) to disk. In contrast, the default setting of *NO allows the program to continue and on the IBM i operating system to decide when the best time is to write the changed index data from memory to disk.

If you’re really worried about losing critical database changes made by your application, a better alternative is to rely on the IBM i journaling support. With journaling enabled, data changes are first written to the attached journal receiver, and the journal receiver entries are written to storage before the changes are written to the memory copy of the Db2 object. Journaling provides protection against unexpected system outages and depends on the IBM i operating system to efficiently write change data to disk.

Despite the Db2 force parameters primarily being historical artifacts, our IBM Expert Labs team regularly finds Db2 objects with non-default settings for the FRCRATIO and FRCACCPTH parameters. on client systems when performing a Db2 & SQL Assessment for customers.  The SYSFILES IBM i service in QSYS2 makes it easy to find Db2 objects using the non-default settings as the following query demonstrates:

SELECT table_name, table_schema, 
       Force_Write_Ratio, Force_Keyed_Access_Path FROM qsys2.sysfiles
 WHERE system_table_schema  = 'DBSCHEMAX' AND 
       (Force_Write_Ratio IS NOT NULL OR 
         Force_Keyed_Access_Path = 'YES')

Since SYSFILES is an IBM i service based on a user-defined table function, make sure you scope the search request as much as possible to minimize the performance impact of this query.

Hopefully, I’ve been successful in convincing you not to use the Db2 force parameters and save your intuition and feelings for other aspects of application development and design.  Your applications and system will perform best when you rely on your IBM i computer to manage when changed data in memory gets written to permanent storage.

Db2 system names for SQL objects - do I feel lucky?

 In the past, I’ve written about the performance considerations of using longer, more descriptive names for you SQL object names on Db2 for i. SQL object names can be up to 128 characters long. In contract, the names for DDS-created Db2 objects are limited to 10 characters. There’s also a usability consideration when utilizing SQL object or column names greater than 10 characters in length.

 The reason that there’s a usability concern is that IBM i commands (e.g., Save Object) and interfaces support object names up to 10 characters in length. Does that mean you’re out of luck when it comes to using these legacy interfaces with SQL objects which have long object names?  No - Db2 for i “helps” you out by generating a system name which can be used with these non-SQL interfaces when your SQL object has a long name greater than 10 characters in length.

 The good news is that Db2 automatically generates these system names for you  … and the bad news is that Db2 automatically generates these system names for you.  This good news and bad news conundrum is why this entry title asks: Do I feel lucky?

 When an SQL table name is greater than 10 characters in length (e.g., EMPLOYEE_MASTER), Db2 for i will generate a 10 character system name. The Db2 system name generation algorithm takes the first 5 characters of the table (i.e., EMPLO) and then uses a 5 digit number for the suffix. The 5 digit number is incremented for each SQL object that starts with the same 5 characters to guarantee that the system name will be unique within the target schema (or library) that will house the SQL table.

 Assuming that the EMPLOYEE_MASTER is the first table created within the database, the generated system name will be: EMPLO00001. This generated name can be used on IBM i commands and will be used on interfaces or commands that return a list of objects such as the Display Library (DSPLIB) command. Obviously, this generated system name is not user friendly. In addition, the generated system name can vary from system to system depending on the order your SQL objects get created. 

 Let’s say the employee database has a second table named EMPLOYEE_EMAIL in addition to EMPLOYEE_MASTER. On the development system, EMPLOYEE_MASTER was created first and EMPLOYEE_EMAIL table created second. The system names for these two tables respectively would be EMPLO00001 and EMPLO00002.  Let’s assume that on the test system, the table creation process is reversed, and the EMPLOYEE_EMAIL table is created first. As a result, the EMPLOYEE_EMAIL table will have a system name of EMPLO00001 on the test system and EMPLO00002 on the development system.

 This potential difference in system names is not ideal, so it’s strongly recommended to provide a system name for Db2 to use instead of having Db2 for i generate a random name. A system name can be easily provided using the FOR SYSTEM NAME clause on your SQL CREATE statements as shown in the following example.

 CREATE TABLE employee_master FOR SYSTEM NAME empmast ( 
  emp_id CHAR(6),
  emp_fname VARCHAR(20),
  emp_lname VARCHAR(30), 
  emp_dept_number CHAR(3))

 Now, the EMPLOYEE_MASTER table will always have a system name of EMPMAST. Luck is no longer involved in terms of the table creation order determining if the system name is EMPLO00001 or EMPLO00002. Just as important, the user now has a system name which is user friendly. Let’s say you’re looking at output from the DSPLIB command would you rather see EMPMAST or EMPLO00001?

 The same usability issue exists with column names longer than 10 characters in length - the emp_dept_number column is an example of this in the earlier example. The generated system name would be EMP_D00001.  By the way, this ugly generated column name will be what you get by default if you have the compiler generate an externally described data structure based on the table definition using DCL-DS in RPG or the COBOL COPY statement. 

 The good news is that the FOR COLUMN clause can be used to override the generated name as the following example demonstrates. The system name for EMP_DEPT_NUMBER is now EDEPTNUM instead of the ugly and hard to comprehend name of EMP_D00001. 

 CREATE TABLE employee_master FOR SYSTEM NAME empmast ( 
  emp_id CHAR(6),
  emp_fname VARCHAR(20),
  emp_lname VARCHAR(30), 
  emp_dept_number FOR COLUMN edeptnum CHAR(3))

 It should now be clear that the lucky developer is the one using their own system names (EMPMAST & EDEPTNUM) instead of relying on the names automatically generated by Db2 (EMPLO00001 & EMP_D00001).

Finding Inline Candidates - Part II

 Last month, I reviewed how to identify user-defined functions that are a candidate for inlining from the perspective of finding functions that are small and simple with the usage of a system catalog object. Another method is to identify the user-defined functions that are being used most frequently on your system. With this approach, you maximize the performance impact of any user-defined function (UDF) that can be made inline eligible because the UDF is run so frequently. 

This methodology requires a Plan Cache Snapshot. A Plan Cache Snapshot is a point in time copy of the data from the “live” Plan Cache. When a snapshot is created, the result is a Db2 table. The rows in this table contain information about the query implementation along with query execution statistics. The query execution statistics include a count of the number of UDF executions that were not inlined for each SQL statement. Given that this execution information is stored in a Db2 table, we’ll see that it is relatively simple to write a query to find these candidate UDFs.

 

The first piece of information you’ll need for your query is the name of the snapshot table. If the IBM i Access Client Solution (ACS) SQL Performance Center was used to create a snapshot, then you’ll need to figure out the name it generated for the snapshot object. This task is easily accomplished by bringing up the ACS SQL Performance Center and navigating to the Plan Cache Snapshot tab as shown in the figure below.

Now that we have name of the table for the query, let’s look at the following query it will be plugged into. The QQSMINT8 column is the key column for our inline UDF candidate analysis – this is the column that contains the number of times that an SQL statement invoked a UDF that is currently not being inlined by the Db2 engine. This query uses this column on the WHERE clause to only return queries which used non-inlined UDFs that in addition to returning the count of UDFs that were not inlined in the final result set.

SELECT qqsmint8 AS SQL_UDFs_Not_Inined, qvp15f AS Times_Run,
       CASE
         WHEN LENGTH(RTRIM(qqc104)) = 0 THEN 'No Program'
         ELSE RTRIM(qqc104) CONCAT '/' CONCAT qqc103 END AS Pgm, 
        qq1000 AS sql_stmt
  FROM snaplib.snapshot_table
  WHERE qqrid = 1000 AND qqsmint8 > 0
  ORDER BY qvp15f DESC

 

The other QQRID column referenced on the WHERE clause is also critical because the 1000 record type is the row in the snapshot table which contains the query execution statistics. Most of the other record types contain data regarding the query implementation. The SELECT list also includes the QVP15F column which contains the number of times the query was run while the query plan resided in the Plan Cache. This count helps you understand how many times the non-lined UDFs are being invoked. The more times that these UDFs are run, the bigger the performance impact will be on your system if you’re able to make the UDF inline eligible.

 

Here's an example of the output this query can return using a snapshot on my system.  It only found one query that’s dependent on non-lined UDFs.  However, that query has been run over 500 times to this point. 

The QQ1000 column contains the query text, so that you can review and find the name of the non-inlined UDFs for further analysis. Hopefully, this method used along with the previously discussed method will help you identify user-defined functions that can be made inline eligible in order to improve the performance of your applications and servers.

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!