Comparison of functional types of routines

Understanding the differences between procedures, functions, and methods can help you determine which functional type to implement when building your own routines and can help you determine where and how you can reference existing routines. This can save you time and effort as well as ensure that you are maximizing the functionality and performance of routines.

Attention: With the release of Db2® 11.5.9, support for Microsoft .Net common language runtime (CLR) routines is deprecated and might be removed in the future. If you are currently running routines that have a dependency on .NET CLR, rewrite the routine logic in a supported language and then recreate the routines.

Procedures, functions, and methods differ in a variety of ways. These differences are outlined in the following table:

Table 1. Comparison of the functional types of routine
Characteristic Procedures Functions Methods
Unique functional characteristics and useful applications
  • Enable the encapsulation of logic and SQL statements.
  • Serve as sub-routine extensions to client applications, routines, triggers, and dynamic compound statements.
  • Procedures are invoked by executing the CALL statement with a reference to a procedure.
  • Nested procedure calls are supported
  • Recursive procedure calls are supported
  • Parameter support for input, output, and input-output parameters
  • Extensive support for SQL statement execution
  • Can return one or more result-sets
  • Savepoints and transaction control
  • Enable the encapsulation of logic and SQL statements.
  • Functions are relationships between sets of input data values and a set of result values.
  • Functions enable you to extend and customize SQL.
  • Functions are invoked from within elements of SQL statements such as a select-list or a FROM clause.
  • Moderate support for SQL statement execution.
  • Parameter support for input parameters and scalar or aggregate function return values.
  • External functions provide support for storing intermediate values between the individual function sub-invocations for each row or value using a scratchpad.
  • Efficient compilation of function logic into queries that reference functions.
  • Enable the encapsulation of logic and SQL statements.
  • Methods allow you to access structured type attributes as well as to define additional behaviors for structured types.
  • Ability to access structured type attributes.
  • Ability to set structured type attributes.
  • Ability to create operations on structured type attributes and return a function value.
Functional sub-types of routine
  • Not applicable
  • Scalar functions
  • Aggregate functions
  • Row functions
  • Table functions
  • Not applicable
Invocation interface
  • Invocation is done through execution of the CALL statement with a reference to the procedure.
  • Procedure invocation supported wherever CALL statement is supported.
  • Invocation is done within an SQL statement within a column select-list, an expression, or in a FROM clause of a select statement, among other locations.
  • Invocation is done within an SQL statement that references the structured type associated with the method.
Are there any built-in routines of this type?
  • Yes, many.
  • See the SQL reference for a list of built-in procedures.
  • Yes, many.
  • See the SQL reference for a list of built-in functions.
  • No
Supported user-defined routine implementations
  • SQL
  • External
    • C/C++ (with embedded SQL or CLI API calls)
    • COBOL
    • Java™ (JDBC)
    • Java (SQLJ)
    • .NET CLR
    • OLE: Visual Basic, Visual C++
  • SQL
  • External
    • C/C++
    • Java (JDBC)
    • Java (SQLJ)
    • .NET CLR
    • OLE DB: Visual Basic, Visual C++ (table functions only)
  • SQL
  • External
    • C
    • C++
Nested call support
  • Yes
  • No, however functions are repeatedly invoked for every value in the input set and intermediate values can be stored using a scratchpad.
  • No
Performance
  • Perform well if routine logic is efficient and best practices are adopted.
  • Perform well if routine logic is efficient and best practices are adopted.
  • Can perform better than a logically equivalent procedure, if the logic only queries data and does not modify data.
  • Good performance
Portability
  • Highly portable
  • Particularly portable if SQL implementation is used.
  • 32-bit and 64-bit external routines supported in a variety of programming languages
  • Highly portable
  • Particularly portable if SQL implementation is used.
  • 32-bit and 64-bit external routines supported in a variety of programming languages
  • Highly portable
Interoperability
  • Procedures can call other procedures and can contain SQL statements that invoke functions with SQL access levels less than or equal to the SQL access level of the procedure.
  • Functions can contain SQL statements that invoke other functions and can call procedures with SQL access levels less than or equal to the SQL access level of the function.
  • Methods can invoke functions with an SQL access level less than or equal to the SQL access level of the method.
  • Methods cannot call procedures or other methods
Restrictions  
  • Table functions can only return a single table-reference that must be referenced in the FROM clause of a SELECT statement. output.
  

In general the functional characteristics and applications of routines determine what routine type should be used. However, performance and the supported routine implementations also play an important role in determining what routine type should be used.