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.