Blog
DB2utor

Categories

February 01, 2011

DB2 10 Scalar Function

DB2 provides the facility to extend SQL functionality through user-defined functions that can then be evaluated in the SQL statement. These functions can be written in host languages such as COBOL and PL/1 or through SQL stored procedure language. Here's a description of the different types of functions created using the DB2 CREATE FUNCTION statement based on the language and returned data:

•    External Scalar – The function is written in a programming language such as COBOL and returns a scalar value.
•    External Table – The function is written in a programming language such as COBOL and returns a complete table.
•    Sourced – The function is implemented by invoking another function that already exists. This function inherits the attributes of the underlying source function.
•    SQL Scalar – The function is written exclusively in SQL statements and returns a scalar value. The body of a SQL scalar function is written in the SQL Procedure Language.
•    SQL Table – The function is written exclusively in SQL statements and returns a complete table. The body of a SQL scalar function is written in the SQL Procedure Language.

Starting with DB2 8 for z/OS, a user-defined SQL scalar function was limited to a single RETURN statement that either specified NULL or an expression (excluding full select). If you needed to do anything beyond the basic, the function had to be written in a host language such as COBOL or PL/1. This required the function to be external, which brought portability as well as performance concerns.

DB2 10 for z/OS new function mode removed the need for a host programming language through the incorporation of SQL procedural language. This provides for the portability of the function from one platform to another. Here are the SQL scalar function enhancements in DB2 10 for z/OS new function mode:

•    Include SQL PL control statements
•    Scalar fullselect in the RETURN statement
•    Using distinct type in the definition of a parameter or SQL variable
•    Defining a parameter for a transition table
•    Using MODIFIES SQL DATA attribute on the CREATE FUNCTION (SQL scalar) statement
•    Using the DB2 Unified Debugger for non-inline SQL scalar functions

Next week we'll take a closer look at how DB2 classifies a scalar function. We'll also go through some SQL to help you find the packages created for non-inline scalar functions.