Blog
DB2utor

Categories

February 08, 2011

DB2 10 Scalar Function: Inline vs. Non-Inline

Last week we discussed the different types of scalar functions. Here I'll share the specific differences between inline and non-inline scalar functions.

Inline scalar function supports the functionality in previous releases. This means that if you can execute the CREATE FUNCTION statement in a previous DB2 release, the function is inline.

These CREATE FUNCTION (SQL scalar) options are considered inline:
o    LANGUAGE SQL
o    SPECIFIC
o    PARAMETER CCSID
o    NOT DETERMINISTIC
o    DETERMINISTIC
o    EXTERNAL ACTION
o    NO EXTERNAL ACTION
o    READS SQL DATA
o    CONTAINS SQL
o    CALLED ON NULL INPUT
o    STATIC DISPATCH

Non-inline scalar function uses one or more of the enhanced function options not listed above. DB2 creates a package and associates it to the non-inline SQL scalar function. The SQL procedural statements are converted to a representation and stored in the database directory. When the function is invoked, DB2 loads the internal representation from the database directory and then executes within the DB2 engine.

The package is generated using the bind options that are implicitly or explicitly specified through corresponding function options. The implications of having a package is that you may have to rebind it when you have a new index to use or a dependent table that was dropped and recreated. However, the rebind will only process the SQL statements in the function body and not the control statements. The ALTER FUNCTION REGENERATE statement rebinds both the SQL statements in the function body as well as the SQL control statements.

The following SQL statement returns a list of packages created as a result of a non-inline SQL scalar function. (Note: The substring size I used was just to make the output fit on the page for this blog entry. You'll want to increase the size for COLLID and NAME to get the entire content. Also, the SCHEMA name for a function is the same as the COLLID name in a package.) The column "V" is to identify if the package is valid.  A value of “Y” means you can execute the package without a problem. A value of "N" means the package will go through autorebind at the next execution. The "O" column is operative. If the package goes through auto-rebind and fails then operative will be marked "N," which means you must do a manual rebind once you fix whatever the package is dependent on.

SELECT                                
   SUBSTR(P.LOCATION,1,2)  AS LOCATION
  ,SUBSTR(P.COLLID,1,25) AS COLLID    
  ,SUBSTR(P.NAME,1,15) AS NAME        
  ,P.VALID AS V                       
  ,P.OPERATIVE AS O                   
  ,HEX(P.CONTOKEN) AS CONTOKEN        
FROM SYSIBM.SYSROUTINES R             
          , SYSIBM.SYSPACKAGE  P              
WHERE P.NAME = R.NAME                 
  AND R.ROUTINETYPE = 'F'             
  AND R.ORIGIN IN ('N','Q')           
  AND R.FUNCTION_TYPE = 'S'           
  AND R.INLINE = 'N'                  
ORDER BY                              
      R.ROUTINETYPE                      
#                                     

Click the following link to see code sample: Download DB2utor code 020811