Comparison of routine implementations

Understanding the differences between the supported routine implementations can help you determine which routine implementation to use when building your own routines. This can save you time and effort as well as ensure that you are maximizing the functionality and performance of routines.

Built-in, sourced, SQL, and external routine implementations differ in a variety of ways. These differences are outlined in the following table:

Table 1. Comparison of routine implementations
Characteristic	Built-in	Sourced	SQL	External
Features and uses	Perform very well because their logic is native to the database manager code. Many common casting, string manipulation, and arithmetic built-in functions are located in the SYSIBM schema.	Used to provide basic extensions to the functionality of built-in functions. SQL and SQL PL provide high level programming language support that makes implementing routine logic fast and easy.	Used to extend the set of built-in functions with more complex functions that can execute SQL statements.	Developers can program logic in the supported programming language of their choice. Complicated logic can be implemented. External actions, actions with impact outside of the database, are directly supported. This can include reading from or writing to the server file system, invoking an application or script on the server, and issuing SQL statements that are not supported in the SQL, sourced, or built-in implementations.
Implementation is built into the database manager code?	Yes	No	No	No
Supported functional routine types that can have this implementation	Not applicable	Functions Scalar functions Aggregate functions	Procedures Functions Methods	Procedures Functions Methods
Supported SQL statements	Not applicable	Not applicable	Most SQL statements, including all SQL PL statements, can be executed in routines. Refer to the topic, "SQL statements that can be executed in routines".	Many SQL statements, including a sub-set of SQL PL statements, can be executed in routines. Refer to the topic, "SQL statements that can be executed in routines".
Performance	Very fast	In general, about as fast as built-in functions.	Very good performance if the SQL is efficiently written, database operations are emphasized more than programming logic, and SQL routine best practices are adopted. Refer to the topic, "SQL routine best practices".	Very good performance if the programming logic is efficiently written and external routine best practices are adopted. Refer to the topic, "External routine best practices".
Portability	Not applicable	Sourced functions can easily be dropped and recreated in other databases.	SQL functions can be easily dropped and re-created in other databases.	External functions can be dropped and re-created in other databases, however care must be taken to ensure that the environment is compatible and that the required supported software is available. Refer to the topic, "Deploying external routines".
Interoperability	Not applicable	They can be referenced wherever built-in functions can be referenced. Sourced functions cannot invoke other functions.	SQL routines can be referenced in many parts of SQL statements. A SQL routine can invoke other SQL and external routines with SQL access levels that are equal to or less than the SQL access level of the SQL routine.	External routines can invoke external routines and other SQL routines with SQL access levels that are equal to or less than the SQL access level of the external routine.

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.