P3

DB2 V9.5 SQL Procedure Developer exam 735 prep, Part 3: DB2 SQL Functions Skill Level: Intermediate Roger E. Sanders ([email protected]) Consultant Corporate Systems Engineer EMC Corporation

16 Oct 2008 User-defined functions (UDFs) are used to enrich the capabilities of IBM® DB2® by providing new functionality that is not available with the rich set of built-in functions provided. This tutorial introduces you to UDFs and walks you through the basic steps used to construct UDFs. This tutorial also introduces you to the structure of SQL functions and covers the ins and outs of SQL function development. This is the third tutorial in a series of six tutorials that are designed to help you prepare for the IBM DB2 9.5 SQL Procedure Developer certification exam (Exam 735).

Section 1. Before you start About this series This series of six DB2 SQL Procedure Developer tutorials covers all the basic constructs and methods of the SQL Procedural Language and shows how it can be used in stored procedures, UDFs and triggers, including error handling and deployment. It also covers some DB2 9.5 advanced features like optimistic locking, hierarchical queries and declared global temporary tables. This series provides specifics on how stored procedures, UDFs, and triggers can be invoked and how you can share data between procedures and functions. It introduces DB2 development tools, including IBM Data Studio. These tutorials provide a solid base for each section of the exam. However, you should not rely on these tutorials as your only preparation for the exam. More exam resources can be found in the Resources section of this tutorial. DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 1 of 38

developerWorks®

ibm.com/developerWorks

About this tutorial This tutorial provides an in-depth look at UDFs (UDFs) with an emphasis on SQL functions. This is the third tutorial in a series of six tutorials that are designed to help you prepare for the IBM DB2 9.5 SQL Procedure Developer certification exam (Exam 735).

Objectives After completing this tutorial, you should be able to: • Identify the proper usage of functions • Use the CREATE FUNCTION statement to create an SQL function • Identify the proper structure of an SQL function body • Return values and tables from an SQL function • Invoke functions

Prerequisites To take the DB2 9.5 SQL Procedure Developer exam, you must have already passed the DB2 9 Family Fundamentals exam (Exam 730). You can use the "DB2 Family Fundamentals tutorial series" to prepare for that exam. It is a very popular series that has helped many people understand the fundamentals of the DB2 family of products (see Resources). This tutorial is written for DB2 programmers whose skills and experience are at a beginning to intermediate level. You should have a general background knowledge of how a relational database works as well as basic knowledge of databases and database programming constructs. You should also be familiar with using the DB2 Command Line Processor (CLP) and you should have a working knowledge of SQL.

System requirements To run the examples in this tutorial, you need access to a DB2 9.5 database server and the SAMPLE database that is provided with DB2. (The SAMPLE database can be created by executing the command db2sampl from the DB2 Command Line Processor.)

DB2 SQL Functions Page 2 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

Section 2. What are functions? In DB2, a function is an encapsulated set of instructions that are designed to perform a specific action; one or more input parameters may be used to customize the action, and one or more output parameters may be used to return results. Four types of functions are available: • Scalar functions • Aggregate functions • Table functions • Row functions A robust set of built-in functions are provided with DB2 and these functions are defined in the SYSIBM schema. The built-in functions available consist of scalar functions (such as UCASE()), aggregate functions (such as AVG()), operator functions (such as "+"), and casting functions (such as DECIMAL()). Typically, functions are invoked from within select-lists and FROM clauses of queries.

Scalar functions A scalar function is a function that returns a single scalar value. Scalar functions can be used to perform simple tasks, or they can be designed to do complex mathematical calculations using values that are provided via function input parameters. Examples of scalar functions include the built-in functions LENGTH() and SUBSTR(). Scalar functions can be referenced by SQL statements wherever expressions are supported. When used in query predicates, a scalar function can improve overall query performance since the function's logic is executed directly on the server as part of the SQL statement that references it. Furthermore, when a scalar function is applied to a set of candidate rows at the server, it can act as a filter, thus limiting the number of rows that must be returned to the client. However, scalar functions have their limitations. For example, by design, scalar functions can only return a single value — multiple values and result sets cannot be returned. Additionally, transaction management is not supported within a scalar function. Thus, commit and rollback operations cannot be performed inside a scalar function's body. Scalar functions are typically used to perform basic mathematical operations and to manipulate strings. DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 3 of 38

developerWorks®

ibm.com/developerWorks

Aggregate functions An aggregate function (also known as a columnar function) is a function that returns a scalar value that is the result of an evaluation of a set of like input values. Typically, the set of like input values come from a column within a table, or from tuples in a VALUES clause. Examples of aggregate functions include the built-in functions MAX() and MIN().

Table functions A table function is a function that returns a table to the SQL statement that references it. Table functions can only be referenced in the FROM clause of a SELECT statement. However, the table returned by a table function can be used in any operation that could be applied to a read-only view (for example, joins, grouping operations, and set operations like UNION and INTERSECT). Table functions can make operating system calls, as well as read data from files and access data across a network. Additionally, SQL table functions can also be used to encapsulate SQL statements that modify table data. (External table functions cannot encapsulate SQL statements.) However, as with scalar functions, transaction management is not supported within table functions. Thus, commit and rollback operations cannot be performed within a table function's body. Table functions are typically used to encapsulate complex, but commonly used sub-queries and to provide a tabular interface to non-relational data. For example, a user-defined external table function could read a spreadsheet and produce a table of values that could then be inserted directly into a base table or be accessed immediately by a query.

Row functions A row function is a function that returns a single row. Row functions can only be used with user-defined structured types; row functions cannot be used in a standalone manner or within SQL statements outside of the context of abstract data types. Typically, row functions are used to map structured type attributes to a row of built-in data type values in order to make structured type attributes accessible in queries or SQL operations. For example, suppose your database has a user-defined structured data type named 'Manager' that extends another structured data type named 'Employee' and has a combination of 'Employee' and 'Manager'-specific attributes. If you wanted to refer to the attribute values in a query, you could do so by creating a row function to translate the attribute values into a row of values that could then be DB2 SQL Functions Page 4 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

referenced by the query.

Section 3. User-defined functions UDFs are special objects that can be used to extend and enhance the support provided by the built-in functions that are available with DB2. As the name implies, UDFs are created by database users who have been given the necessary authority and/or privileges. Unlike DB2's built-in functions, UDFs can take advantage of system calls and DB2's administrative APIs, thereby providing more synergy between systems, applications, and databases. Five different types of UDFs can be created: • Sourced (or Template) • SQL Scalar, Table, or Row • External Scalar • External Table • OLE DB External Table UDFs are created (or registered) by executing the CREATE FUNCTION SQL statement. Several flavors of this statement are available, and the appropriate form to use is determined by the type of function that is to be created.

Sourced (or Template) functions A sourced function is a UDF that is constructed from a function that has already been registered with a database (referred to as the source function). Sourced functions can be scalar, columnar, or tabular in nature, or they can be designed to overload a specific operator such as +, -, *, and /. When a sourced function is invoked, all arguments passed to it are converted to the data types that are expected by the underlying source function, and the source function itself is then executed. Upon completion, the source function performs any conversions necessary on the results produced and returns control to the SQL statement that invoked the UDF. The most common use of sourced functions is to enable a user-defined distinct data type to selectively inherit some of the semantics of the built-in data type on which it is based. Often, a UDF that is sourced on an existing function for the purpose of providing that function's support to a user-defined data type is given the same name as the

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 5 of 38

developerWorks®

ibm.com/developerWorks

sourced function used. This allows users to invoke the same function with a user-defined distinct type without realizing that an additional function definition was required. Generally, the same name can be used for more than one function provided there is some difference in each function's signature. The basic syntax for the form of the CREATE FUNCTION statement used to create a sourced function looks something like this: Listing 1. CREATE FUNCTION statement used to create a sourced function CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> ) RETURNS [OutputDataType] <SPECIFIC [SpecificName]> SOURCE [SourceFunction] <([DataType] ,...)>

where: Note: Parameters or options shown in normal brackets ([]) are required; parameters/options shown in angle brackets (<>) are not. The complete syntax for the CREATE FUNCTION statement can be found in the "DB2 9 SQL Reference, Volume 2" documentation.

• FunctionName identifies the name to be assigned to the sourced function that is to be created. • ParameterName identifies the name to be assigned to one or more function parameters. • InputDataType identifies the type of data the function expects to receive for the parameter identified by ParameterName. • OutputDataType identifies the type of data the function is to return. • SpecificName identifies the specific name to be assigned to the UDF. This name can be used later to refer to or delete (drop) the function; however, it cannot be used to invoke the function. • SourceFunction identifies the name that has been assigned to the existing function that is to be used to create the sourced function. • DataType identifies the type of data that the existing function expects to receive for each parameter required. If the SPECIFIC clause is specified when this form of the CREATE FUNCTION statement is used, a unique name is assigned to the UDF being created. Once created, the function can then be dropped by referencing the specific name in a DB2 SQL Functions Page 6 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

special form of the DROP SQL statement (DROP SPECIFIC FUNCTION [SpecificName]). However, if a specific name is not assigned to a UDF, both the function name and the function's signature (that is, a list of the data types used by each of the function's parameters, enclosed in parenthesis) must be provided as input to the DROP FUNCTION statement. If the AS TEMPLATE clause is specified, a function template is produced. A function template is a partial function that defines what types of values are to be returned but contains no executable code. Function templates are mapped to a data source (for example, Oracle or SQL Server) function within a federated system so that the data source function can be invoked from a federated database. A function template can be registered only with a database server that is designated as a federated server. Thus, if you created a distinct data type named YEAR that is based on the INTEGER built-in data type and you wanted to create a sourced function named AVG() that is designed to accept and return a YEAR value and is based on the AVG() built-in function (which accepts and returns an INTEGER value), you could do so by executing a CREATE FUNCTION statement that looks something like this: CREATE FUNCTION AVG(YEAR) RETURNS YEAR SOURCE SYSIBM.AVG(INTEGER)

SQL functions An SQL function is a UDF that is constructed using only SQL and procedural language SQL statements. An SQL function can be scalar in nature or it can return a single row or an entire table, depending on how it has been defined. The basic syntax for the form of the CREATE FUNCTION statement that is used to create an SQL function looks something like this: Listing 2. CREATE FUNCTION statement used to create an SQL function CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> ) RETURNS [[OutputDataType] | TABLE ( [ColumnName] [ColumnDataType] ,... ) | ROW ( [ColumnName] [ColumnDataType] ,... )] <SPECIFIC [SpecificName]> <EXTERNAL ACTION | NO EXTERNAL ACTION> <STATIC DISPATCH> [SQLStatements] | RETURN [ReturnStatement]

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 7 of 38

developerWorks®

ibm.com/developerWorks

where: • FunctionName identifies the name to be assigned to the SQL function that is to be created. • ParameterName identifies the name to be assigned to one or more function parameters. • InputDataType identifies the type of data the function expects to receive for the parameter identified by ParameterName. • OutputDataType identifies the type of data the function is to return. • ColumnName identifies the name to be assigned to one or more columns that the function is to return (if the function is designed to return a table or a row). • ColumnDataType identifies the type of data the function expects to return for the column identified by ColumnName . • SpecificName identifies the specific name to be assigned to the UDF. This name can be used later to refer to or delete (drop) the function; however, it cannot be used to invoke the function. • SQLStatements identifies one or more SQL statements that are to be executed when the function is invoked. Together, these statements act as a single dynamic compound SQL statement. • ReturnStatement identifies the RETURN SQL statement that is used to return to the application that called the function. (If the SQL function's body is comprised of a dynamic compound statement, it must contain at least one RETURN statement; and a RETURN statement must be executed when the function is called. If the function is a table or row function, it can contain only one RETURN statement, and that statement must be the last statement used.) As you can see, this form of the CREATE FUNCTION statement contains several additional clauses that are not found in the form presented earlier. In many cases, it is not immediately apparent what information these clauses are trying to convey, so before looking at an example, let's examine some of these clauses in detail. • indicates that the function was written using SQL. • is used to identify whether the function always returns the same scalar value, table, or row when it is called with the same parameter values specified (DETERMINISTIC) or not (NOT DETERMINISTIC). If neither clause is

DB2 SQL Functions Page 8 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

specified, it is implied that the function may return different results each time it is called with the same parameter values specified. • <EXTERNAL ACTION | NO EXTERNAL ACTION> identifies whether the function performs some action that changes the state of an object that DB2 does not manage (EXTERNAL ACTION) or not (NO EXTERNAL ACTION). An example of an external action is sending an email message or writing a record to an external file. If neither clause is specified, it is implied that the function may perform some type of external action. • identifies which types of SQL statements have been coded in the body of the UDF. Three different values are available: • CONTAINS SQL : The body of the UDF contains executable SQL statements that neither read nor modify data. • READS SQL DATA : The body of the UDF contains executable SQL statements that read but do not modify data. • MODIFIES SQL DATA : The body of the UDF contains executable SQL statements that can both read and modify data. • <STATIC DISPATCH> is used to indicate that at function resolution time, DB2 is to choose a function based on the static types (declared types) of the parameters of the function. • indicates that the function is to be called regardless of whether any of its parameters contain null values. Thus, if you want to create a scalar SQL function named JULIAN_DATE() that is designed to convert DB2 dates to Julian dates (the number of days from January 1, 4713 B.C.), you could do so by executing a CREATE FUNCTION statement that looks something like this: Listing 3. Scalar SQL function CREATE FUNCTION julian_date(in_date DATE) RETURNS CHAR(7) LANGUAGE SQL RETURN RTRIM(CHAR(YEAR(in_date))) || SUBSTR(DIGITS(DAYOFYEAR(in_date)), 8)

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 9 of 38

developerWorks®

ibm.com/developerWorks

External scalar functions An external scalar function is a function that is written using a high-level programming language such as C, C++, or Java™ that returns a single value. Although the process of creating and implementing a sourced function or an SQL function is fairly simple, the process used to create and employ an external scalar function (or any external function for that mater), is much more complex — to create any external function, the following steps must be performed: 1.

Construct the body of the UDF, using a supported high-level programming language.

2.

Compile the UDF.

3.

Link the UDF to create a library (or dynamic-link library).

4.

Debug the UDF and repeat steps 2 through 4 until all problems have been resolved.

5.

Physically store the library containing the UDF on the server workstation. Additionally, the system permissions for the library file containing the UDF must be modified so that all users can execute it. For example, in a UNIX environment, the chmod command is used to make a file executable by everyone; in a Windows environment, the attrib command is used for the same purpose.

6.

Register the UDF with a DB2 database using the appropriate form of the CREATE FUNCTION SQL statement.

Once these steps have been completed, the resulting UDF can be used in the same manner in which any other scalar or table function can be used. It is important to note that because the body of an external scalar function is coded using a high-level programming language rather than SQL, each value provided as a function argument must be converted from an SQL data type to an appropriate high-level programming language data type before it can be used. (If a function is defined such that one of its parameters expects a user-defined distinct data type, values for that parameter are converted to the appropriate built-in data type before they are passed to the external function.) Likewise, any value that is to be returned by the function must be converted from its high-level programming language data type to the appropriate SQL data type before it can be returned. Also, if memory is dynamically allocated within an external function, it should be freed before the function returns. The basic syntax for the form of the CREATE FUNCTION statement that is used to DB2 SQL Functions Page 10 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

register an external scalar function (that has been created as outlined above) looks something like this: Listing 4. CREATE FUNCTION statement used to register an external scalar function CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> ) RETURNS [OutputDataType] <SPECIFIC [SpecificName]> EXTERNAL LANGUAGE [C | JAVA | CLR | OLE] PARAMETER STYLE [DB2GENERAL | JAVA | SQL] <STATIC DISPATCH> <EXTERNAL ACTION | NO EXTERNAL ACTION> <SCRATCHPAD <[100 | [SPSize]]> | NO SCRATCHPAD>

where: • FunctionName identifies the name to be assigned to the external scalar function that is to be created. • ParameterName identifies the name to be assigned to one or more function parameters. • InputDataType identifies the type of data the function expects to receive for the parameter identified by ParameterName . • OutputDataType identifies the type of data the function is to return. • SpecificName identifies the specific name to be assigned to the UDF. This name can be used later to refer to or delete (drop) the function; however, it cannot be used to invoke the function. • ExternalName identifies the name of the library and the name of the function in the library that contains the executable code of the external function being registered. (We'll take a closer look at how this name is constructed shortly.) • Identifier identifies the name of the library that contains the executable code of the external function being registered, but only if the function was written using C or C++. The DB2 Database Manager looks for a function that has the same name as the library name specified. • SPSize specifies the amount of memory, in bytes, that is to be set aside and used as a scratchpad area.

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 11 of 38

developerWorks®

ibm.com/developerWorks

As you can see, this form of the CREATE FUNCTION statement contains several more clauses that are not found in the forms you looked at earlier. Once again, in many cases, it is not immediately apparent what information these clauses are trying to convey, so before looking at an example, let's examine some of these new clauses in detail. • The EXTERNAL clause identifies two things: the name of the library and, optionally, the name of the function within the library that contains the executable code for the UDF being registered. The high-level programming language used to construct the body of any external UDF determines how these names are to be provided. For example, if an external UDF was developed using the C or C++ programming language, the names of the library and function within the library that contains the body of the function can be specified in four different ways: • 'LibraryName' • 'LibraryName ! FunctionName' • 'AbsolutePath' • 'AbsolutePath ! FunctionName' If a library name is provided instead of an absolute path, DB2 looks in the /sqllib/function and /sqllib/function/unfenced subdirectories for the library name specified. (On Windows operating systems, DB2 looks for the function in a directory path that is specified by the LIBPATH or PATH environment variable.) On the other hand, if an absolute path is provided, the name of the library must be appended to the path, and DB2 looks in the location specified for the appropriate library. (If neither a library name nor an absolute path is provided, DB2 looks in the default subdirectories shown for a library and function that has the same name as the name that is to be assigned to the UDF being registered.) If a function name is provided, DB2 looks for a function that has the name specified within the library specified; if no function name is provided, DB2 looks for a function that has the same name as the library name specified. • The LANGUAGE [C | JAVA | CLR | OLE] clause is used to identify the high-level programming language convention that the body of the UDF conforms to. Four different values are available: • C : DB2 calls the UDF as if it were a C function. (The UDF must conform to the C language calling and linkage convention as defined by standard ANSI C.) • JAVA : DB2 calls the UDF as a method in a Java class.

DB2 SQL Functions Page 12 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

• CLR : DB2 calls the UDF as a method in a .NET class. (At this time, LANGUAGE CLR is only supported for UDFs running on Windows operating systems.) • OLE : DB2 calls the UDF as if it were a method exposed by an OLE automation object. (The UDF must conform with the OLE automation data types and invocation mechanism, as described in the OLE Automation Programmer's Reference. • The PARAMETER STYLE [DB2GENERAL | JAVA | SQL] clause is used to identify the parameter passing style that the UDF expects the calling application to use when passing values to it. Three parameter passing styles are available: • DB2GENERAL : Values are passed and returned using the calling conventions that are used to call a method in a Java class. (Can only be used when the external UDF is written using Java.) • JAVA : Values are passed and returned using calling conventions that conform to the Java language and SQLJ specifications. (Can only be used when the external UDF is written using Java.) • SQL : Values are passed and returned using calling conventions that conform to C language calling and linkage conventions, methods exposed by OLE automation objects, or public static methods of a .NET object. (Can only be used when the external UDF is written using C/C++, OLE, or .NET.) • The clause is used to identify whether the external UDF is considered "safe" enough to be run in the DB2 Database Manager operating environment's process/address space (NOT FENCED) or not (FENCED). If the (FENCED) clause is specified (or if neither clause is specified) the DB2 Database Manager will not allow the function to access its internal resources. • The clause is used to identify which types of SQL statements have been coded in the body of the external UDF. Three different values are available: • NO SQL : The body of the external UDF either does not contain any SQL, or it contains non-executable SQL statements. (Examples of non-executable SQL statements are the INCLUDE and the WHENEVER statements.) • CONTAINS SQL : The body of the UDF contains executable SQL statements that neither read nor modify data.

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 13 of 38

developerWorks®

ibm.com/developerWorks

• READS SQL DATA : The body of the UDF contains executable SQL statements that read but do not modify data. • The <SCRATCHPAD <[100 | [SPSize]]> | NO SCRATCHPAD> clause is used to indicate whether memory is to be allocated and set aside as a persistent storage area for the UDF (SCRATCHPAD) or not (NO SCRATCHPAD). If the SCRATCHPAD clause is specified, DB2 allocates the appropriate amount of memory the first time the function is invoked. Once a scratchpad is created and populated, its contents are preserved from one function invocation to the next — any changes made to the scratchpad by the UDF on one call will be there on a subsequent call. • The clause is used to identify whether information known by DB2 is to be passed to the UDF as an additional argument when the function is invoked (DBINFO) or not (NO DBINFO). If the DBINFO clause is specified, DB2 passes a data structure containing information like the name of the currently connected database, the application run-time authorization ID, the version, release, and modification level of the database server invoking the function, and the operating system being used by the server to the UDF. Thus, if you wanted to register an external scalar function named CENTER() that accepts two values (an INTEGER value and a DOUBLE value), returns a DOUBLE value, and is stored in a library named "double" that resides in the directory /home/db2inst1/myfuncs, you could do so by executing a CREATE FUNCTION SQL statement that looks something like this: Listing 5. External scalar function CREATE FUNCTION center(INT, DOUBLE) RETURNS DOUBLE EXTERNAL NAME '/home/db2inst1/myfuncs/double' LANGUAGE C PARAMETER STYLE SQL DETERMINISTIC NO SQL

External table functions Like external scalar functions, external table functions are written using a high-level programming language. And although an external scalar function returns a scalar value, an external table function returns a result data set each time it is invoked. DB2 SQL Functions Page 14 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

External table functions are powerful because they enable you to make almost any source of data appear to be a DB2 base table. Furthermore, the result data set returned by an external table function can be used in join operations, grouping operations, set operations (for example, UNIONs), and any other operation that can be applied to a read-only view. The basic syntax for the form of the CREATE FUNCTION statement that is used to register an external table function looks something like this: Listing 6. CREATE FUNCTION statement used to register an external table function CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> ) RETURNS TABLE ( [ColumnName] [ColumnDataType] ,... ) <SPECIFIC [SpecificName]> EXTERNAL LANGUAGE [C | JAVA | CLR | OLE] PARAMETER STYLE [DB2GENERAL | SQL] <STATIC DISPATCH> <EXTERNAL ACTION | NO EXTERNAL ACTION> <SCRATCHPAD <[100 | [SPSize]]> | NO SCRATCHPAD>

where: • FunctionName identifies the name to be assigned to the external table function that is to be created. • ParameterName identifies the name to be assigned to one or more function parameters. • InputDataType identifies the type of data the function expects to receive for the parameter identified by ParameterName . • ColumnName identifies the name to be assigned to one or more columns that the function is to return . • ColumnDataType identifies the type of data the function expects to return for the column identified by ColumnName . • SpecificName identifies the specific name to be assigned to the UDF. This name can be used later to refer to or delete (drop) the function; however, it cannot be used to invoke the function. • ExternalName identifies the name of the library and the name of the function in the library that contains the executable code of the external function being registered. DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 15 of 38

developerWorks®

ibm.com/developerWorks

• Identifier identifies the name of the library that contains the executable code of the external function being registered, but only if the function was written using C or C++. The DB2 Database Manager will look for a function that has the same name as the library name specified. • SPSize specifies the amount of memory, in bytes, that is to be set aside and used as a scratchpad area. So, if you want to register an external table function named EMPDATA() that accepts two variable-length character string values, returns a result data set that contains employee information (retrieved from an ASCII file), and is stored in a library named "EMPDATA" that resides in the directory /home/db2inst1/myfuncs, you could do so by executing a CREATE FUNCTION SQL statement that looks something like this: Listing 7.External table function CREATE FUNCTION empdata (VARCHAR(30), VARCHAR(255)) RETURNS TABLE (empid INT, lname CHAR(20), fname CHAR(20)) EXTERNAL NAME '/home/db2inst1/myfuncs/EMPDATA' LANGUAGE C PARAMETER STYLE SQL DETERMINISTIC NOT FENCED NO SQL NO EXTERNAL ACTION

OLE DB external table functions Microsoft OLE DB is a set of Application Programming Interfaces (APIs) that are designed to provide access to a wide variety of data sources. A data source consists of the data itself, it's associated Database Management System (DBMS), the platform on which the DBMS resides, and the network used to access that platform. OLE DB is designed to provide access to all types of data in an OLE Component Object Model (COM) environment. Along with the functionality provided with CLI/ODBC, OLE DB defines interfaces that are suitable for gaining access to data that cannot be accessed via SQL. OLE DB facilitates application integration by defining a set of standard interfaces, which are simply groupings of semantically-related functions through which one application accesses the services of another. Interfaces are the binary standard for component-object interaction, and each interface contains a set of functions that defines a contract between the object implementing the interface (the provider) and the client using the interface (the consumer). Two classes of OLE DB providers exist: OLE DB data providers, which own data and expose their data in tabular format as a rowset, and OLE DB service providers, which do not own their own data

DB2 SQL Functions Page 16 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

but encapsulate some services by producing and consuming data through OLE DB interfaces. Like external table functions, external OLE DB table functions are written using a high-level programming language. However, with OLE DB table functions, a generic built-in OLE DB consumer can be used to interface with any OLE DB provider to access data; you need only to register an OLE DB table function and refer to the appropriate OLE DB provider as the data source. No additional programming is needed. And like external table functions, the table returned by an external OLE DB table function can be used in joins, grouping operations, set operations (for example, UNIONs), and any other operation that can be applied to a read-only view. For example, you can define an OLE DB table function to return a table from a Microsoft Access database or a Microsoft Exchange address book and then create a report that seamlessly combines data from this OLE DB table with data in a DB2 database. It is important to note that in order to use OLE DB table functions with a DB2 database, you must install OLE DB 2.0 or later, which is available from Microsoft. Refer to your data source documentation for more information about the system requirements and OLE DB providers available for your specific data source. The basic syntax for the form of the CREATE FUNCTION statement that is used to register an OLE DB external table function is similar to the one in Listing 8. Listing 8. CREATE FUNCTION statement used to register an OLE DB external table function CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> ) RETURNS TABLE ( [ColumnName] [ColumnDataType] ,... ) <SPECIFIC [SpecificName]> EXTERNAL LANGUAGE OLEDB <EXTERNAL ACTION | NO EXTERNAL ACTION>

where: • FunctionName identifies the name to be assigned to the OLE DB external table function that is to be created. • ParameterName identifies the name to be assigned to one or more function parameters. • InputDataType identifies the type of data the function expects to receive for the parameter identified by ParameterName . • ColumnName identifies the name to be assigned to one or more columns

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 17 of 38

developerWorks®

ibm.com/developerWorks

that the function is to return . • ColumnDataType identifies the type of data the function expects to return for the column identified by ColumnName . • SpecificName identifies the specific name to be assigned to the UDF. This name can be used later to refer to or delete (drop) the function; however, it cannot be used to invoke the function. • ExternalName identifies the name of the external table being referenced and the name of the OLE DB provider for the function being registered. The syntax used to specify the external table name and the OLE DB provider is: '[Server]!'

or '!![ConnectString] '

where: • Server identifies the local name of a data source as defined by the CREATE SERVER SQL statement. • Rowset identifies the rowset (table) that is exposed by the OLE DB provider. • ConnectString specifies a connection string that identifies the initialization properties that are needed to connect to a data source via an OLE DB provider. This string consists of a series of keyword=value pairs that are similar to those used to construct a connection string for the CLI/ODBC function SQLDriverConnect(). Note: The clause is used to specify whether the data source being accessed uses the same collating sequence as DB2. • NumRows specifies an estimate of the expected number of rows to be returned by the function. (This value is used for optimization purposes only.) Thus, if you wanted to register an OLE DB external table function named ORDERS()

DB2 SQL Functions Page 18 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

that retrieves order information from a Microsoft Access database, you could do so by executing a CREATE FUNCTION SQL statement like the one in Listing 9. Listing 9. OLE DB external table function CREATE FUNCTION orders() RETURNS TABLE (orderid INTEGER, customerid CHAR(5), employeeid INTEGER, orderdate TIMESTAMP, requiredate TIMESTAMP, shipdate TIMESTAMP, shipcharges DECIMAL(19,4)) LANGUAGE OLEDB EXTERNAL NAME '!orders!Provider=Microsoft.Jet.OLEDB.3.51; Data Source=c:\sqllib\samples\oledb\nwind.mdb'

Section 4. Structure of an SQL function Earlier, you saw that an SQL function consists of the following components: • A function name • A sequence of parameter declarations (if there are any) • A declaration of what the function will return • One or more function options • The function body Figure 1 shows the parts of the CREATE FUNCTION statement that are used to define each component. Figure 1. Structure of an SQL function

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 19 of 38

developerWorks®

ibm.com/developerWorks

The definition of each SQL function begins with the words "CREATE FUNCTION" followed by the function name (designated by item 1 in the CREATE FUNCTION statement shown in Figure 1 ). The fully qualified name, combined with the names and basic data types of any parameters, defines the function signature. Two functions having the same function signature cannot reside in the same schema. Parameter declarations come next (as designated by item 2 in Figure 1 ). This is where you can define one or more variables that can be used to pass data between a calling application and the function when it is invoked. (It is possible to create a function that has no parameters.) After parameter declarations are made, information about the kind of information the function will return must be specified (as designated by item 3 in Figure 1 ). SQL functions can return any built-in or distinct data type except LONG VARCHAR, LONG VARGRAPHIC, or XML. They can also be designed to return a table or a single row. Next, specific properties of the function are identified (as designated by item 4 in Figure 1 ). The most important is the language used to code the body of the function, which in this case is SQL. Other characteristics that may be specified here include whether or not the function returns the same value when invoked with the same parameters (DETERMINISTIC), whether any external actions are performed (EXTERNAL ACTION), and the level of data access (CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA).

DB2 SQL Functions Page 20 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

The body of the function itself (as designated by item 5 in Figure 1 ) can consist of a single SQL statement or a compound SQL statement. The statement (or statements if a compound SQL statement is used) that can be used in the body of the function is determined by the properties of the function itself. Table 1 lists the SQL statements that can be used in the body of an SQL function, based on the level of data access specified. (SQL statements shown in bold are SQL PL statements.) Table 1. SQL statements that can be used in the body of an SQL function SQL statement CONTAINS SQL

READS SQL DATA

MODIFIES SQL DATA

CALL

Yes

Yes

Yes

DELETE

No

No

Yes

FOR

Yes

Yes

Yes

GET Yes DIAGNOSTICS

Yes

Yes

IF

Yes

Yes

Yes

INSERT

No

No

Yes

ITERATE

Yes

Yes

Yes

LEAVE

Yes

Yes

Yes

MERGE

No

No

Yes

SELECT

No

Yes

Yes

SET variable

Yes

Yes

Yes

SIGNAL

Yes

Yes

Yes

VALUES

No

Yes

Yes

WHILE

Yes

Yes

Yes

Let's explore some examples that illustrate this structure.

Single statement SQL scalar functions As the name implies, a single statement SQL function is a function whose body is composed of one and only one SQL statement. A single statement SQL scalar function is a single statement SQL function that returns a single scalar value. Figure 2 shows a single statement SQL scalar function that is designed to display the name of the current month. Figure 2. Single statement SQL scalar function

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 21 of 38

developerWorks®

ibm.com/developerWorks

The elements that make up the CREATE FUNCTION statement shown in Figure 2 are as follows: 1.

The name of the function, which is SQLFUNC.THIS_MONTH().

2.

The return value of the function, which is a variable-length character string (VARCHAR) that can be up to 25 characters long.

3.

The unique name to be assigned to the function, which is SQLFUNC.THIS_MONTH. This name is particularly useful when multiple functions having the same name but different signatures reside in the same schema.

4.

An indication that the language used to develop the body of this function was SQL.

5.

An indication that the function may not always return the same value when invoked with the same parameters.

6.

An indication that the function does not perform any external actions.

7.

An indication that the function contains SQL statements that do not read or modify data.

8.

The body of the function itself, along with a RETURN statement, which returns control to any user or application that invokes function. In this example, the body of the function calls the MONTHNAME() built-in function, using the current system date, to produce the name of the current month.

Figure 3 shows a single statement SQL scalar function that is designed to remove leading and trailing blanks from a character string. This function differs from the one

DB2 SQL Functions Page 22 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

shown in Figure 2 in that it has been designed to accept an input parameter value. Figure 3. Single statement scalar SQL function with an input parameter

The elements that make up the CREATE FUNCTION statement shown in Figure 3 are as follows: 1.

The name of the function, which is SQLFUNC.STRIP_LTBLANKS().

2.

The input parameter needed by the function, which in this case is a variable-length character string (VARCHAR) that can be up to 1024 characters long.

3.

The return value of the function, which is a variable-length character string (VARCHAR) that can be up to 1024 characters long.

4.

The unique name to be assigned to the function, which is SQLFUNC.STRIP_LTBLANKS.

5.

Specific properties of the function such as the language used to construct the body of the function, whether or not the function always returns the same value when invoked with the same parameter value, whether or not the function performs any external actions, and the level of data access required.

6.

The body of the function, along with a RETURN statement. In this example, the body of the function calls the LTRIM() and RTRIM() built-in functions to remove leading and trailing blanks from the character string provided.

Single statement SQL table functions A single statement SQL table function is an SQL function (whose body consists of a single SQL statement) that returns a table to the SQL statement that references it. Single statement SQL table functions differ from single statement SQL scalar functions in the way their RETURNS clause is coded; instead of declaring that a DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 23 of 38

developerWorks®

ibm.com/developerWorks

scalar data type value is to be returned, columns for the temporary table that will be the function's result are defined. Figure 4 shows a single statement SQL table function that is designed to return a result set that contains male employee salary information. Figure 4. Single statement SQL table function

The elements that make up the CREATE FUNCTION statement shown in Figure 4 are as follows: 1.

The name of the function, which is SQLFUNC.MALE_SALARIES().

2.

The return value of the function, which is a temporary table containing employee number, last name, and salary information.

3.

The unique name to be assigned to the function, which is SQLFUNC.MALE_SALARIES.

4.

Specific properties of the function such as the language used to construct the body of the function, whether or not the function will always return the same value when invoked with the same parameters, whether or not the function performs any external actions, and the level of data access required.

5.

The body of the function itself, along with a RETURN statement, which returns control to any user or application that invokes function. In this example, the body of the function is a query that retrieves employee number, last name, and salary information from a table named EMPLOYEE for all male employees.

Figure 5 shows a single statement SQL table function that is designed to return a result set containing salary information for all employees whose base salary is above the salary provided. This function differs from the one shown in Figure 4 in that it has

DB2 SQL Functions Page 24 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

been designed to accept an input parameter value. Figure 5. Single statement SQL table function with an input parameter

The elements that make up the CREATE FUNCTION statement shown in Figure 5 are as follows: 1.

The name of the function, which is SQLFUNC.TOP_SALARIES().

2.

The input parameter needed by the function, which in this case is a decimal value (DECIMAL) that has a scale of 9 and a precision of 2.

3.

The return value of the function, which is a temporary table containing employee number, last name, and salary information.

4.

The unique name to be assigned to the function, which is SQLFUNC.TOP_SALARIES.

5.

Specific properties of the function such as the language used to construct the body of the function, whether or not the function will always return the same value when invoked with the same parameter value, whether or not the function performs any external actions, and the level of data access required.

6.

The body of the function itself, along with a RETURN statement. In this example, the body of the function is a query that retrieves employee number, last name, and salary information from a table named EMPLOYEE for all employees whose salary is greater than the minimum salary specified.

Compound statement SQL scalar functions DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 25 of 38

developerWorks®

ibm.com/developerWorks

A compound SQL statement is a special mechanism that allows you to group multiple SQL statements into a single executable block. Although each SQL statement used in a compound statement could be executed individually, grouping them together reduces the amount of overhead required to process them; compound statements are compiled by DB2 as a single SQL statement. On their own, compound SQL statements are useful for creating short scripts that perform small units of logical work with minimal control flow, but that have significant data flow. When used in the body of SQL functions (in conjunction with supported inline SQL PL statements), they allow for more complex logic to be executed. Figure 6 shows an SQL scalar function that contains a compound SQL statement for its body. This function is designed to identify which day (that is, Sunday, Monday, and so on) that the last day of the current month falls on. Figure 6. Compound statement SQL scalar function

The elements that make up the CREATE FUNCTION statement shown in Figure 6 are as follows: 1.

The name of the function, which is SQLFUNC.LAST_DAY_OF_MONTH().

2.

The return value of the function, which is a variable-length character string (VARCHAR) that can be up to 24 characters long.

3.

The unique name to be assigned to the function, which is SQLFUNC.LAST_DAY_OF_MONTH.

4.

Specific properties of the function such as the language used to construct

DB2 SQL Functions Page 26 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

the body of the function, whether or not the function will always return the same value when invoked with the same parameters, whether or not the function performs any external actions, and the level of data access required. 5.

The key words BEGIN ATOMIC, which are used to indicate the beginning of a compound SQL statement block.

6.

The body of the function itself, which consists of several SQL statements that make up the compound SQL statement block.

7.

A RETURN statement, which returns control to any user or application that invokes function. In this example, the RETURN statement calls the DAYNAME() built-in function, using a data string that was constructed within the body of the function, and returns the results.

8.

The key word END, which is used to indicate the end of a compound SQL statement block.

Figure 7 shows a compound statement SQL scalar function that is designed to convert temperatures from Fahrenheit to Celsius. This function differs from the one shown in Figure 6 in that it has been designed to accept an input parameter value. Figure 7. Compound statement SQL scalar function with an input parameter

The elements that make up the CREATE FUNCTION statement shown in Figure 7 are as follows: 1.

The name of the function, which is SQLFUNC.CONV_F_TO_C().

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 27 of 38

developerWorks®

ibm.com/developerWorks

2.

The input parameter needed by the function, which in this case is a single-precision floating-point value that represents a temperature in degrees Fahrenheit.

3.

The return value of the function, which in this case is a single-precision, floating-point value that represents a temperature in degrees Celsius.

4.

The unique name to be assigned to the function, which is SQLFUNC.CONV_F_TO_C.

5.

Specific properties of the function such as the language used to construct the body of the function, whether or not the function will always return the same value when invoked with the same parameters, whether or not the function performs any external actions, and the level of data access required.

6.

The key words BEGIN ATOMIC, which are used to indicate the beginning of a compound SQL statement block.

7.

The body of the function itself, which consists of several SQL statements that make up the compound SQL statement block.

8.

A RETURN statement. In this example, the RETURN statement returns the temperature value (in degrees Celsius) that was produced in the body of the function.

9.

The key word END, which is used to indicate the end of a compound SQL statement block.

Compound statement SQL table functions A compound statement SQL table function is an SQL function (whose body consists of a compound SQL statement) that returns a table to the SQL statement that references it. As with single statement SQL table functions, compound statement SQL table functions differ from SQL scalar functions in the way their RETURNS clause is coded; instead of declaring that a scalar data type value is to be returned, columns for the temporary table that will be the function's result are defined. Figure 8 shows a compound statement SQL table function that is designed to return a result set that contains employee salary information for all employees whose salary is above the average salary for all employees. Figure 8. Compound statement SQL table function

DB2 SQL Functions Page 28 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

The elements that make up the CREATE FUNCTION statement shown in Figure 8 are as follows: 1.

The name of the function, which is SQLFUNC.TOP_SALARIES().

2.

The return value of the function, which is a temporary table containing employee number, last name, and salary information.

3.

The unique name to be assigned to the function, which is SQLFUNC.TOP_SALARIES.

4.

Specific properties of the function such as the language used to construct the body of the function, whether or not the function will always return the same value when invoked with the same parameters, whether or not the function performs any external actions, and the level of data access required.

5.

The key words BEGIN ATOMIC, which are used to indicate the beginning of a compound SQL statement block.

6.

The body of the function itself, which consists of several SQL statements that make up the compound SQL statement block.

7.

A RETURN statement, which returns control to any user or application that invokes function. In this example, the RETURN statement returns the results of a query that retrieves employee number, last name, and salary information from a table named EMPLOYEE for all employees whose salary is above the average salary obtained from the table.

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 29 of 38

developerWorks®

8.

ibm.com/developerWorks

The key word END, which is used to indicate the end of a compound SQL statement block.

Figure 9 shows a compound statement SQL table function that is designed to return a result set containing information for all employees who have a birth date this month that work in a specific department. This function differs from the one shown in Figure 8 in that it has been designed to accept an input parameter value. Figure 9. Compound statement SQL table function with an input parameter

The elements that make up the CREATE FUNCTION statement shown in Figure 9 are as follows: 1.

The name of the function, which is SQLFUNC.BDAY_THIS_MONTH().

2.

The input parameter needed by the function, which in this case is a variable-length character string (VARCHAR) that can be up to 3 characters long.

3.

The return value of the function, which is a temporary table containing employee number, last name, department, and birth date information.

4.

The unique name to be assigned to the function, which is SQLFUNC.BDAY_THIS_MONTH.

5.

Specific properties of the function such as the language used to construct the body of the function, whether or not the function will always return the

DB2 SQL Functions Page 30 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

same value when invoked with the same parameters, whether or not the function performs any external actions, and the level of data access required. 6.

The key words BEGIN ATOMIC, which are used to indicate the beginning of a compound SQL statement block.

7.

The body of the function itself, which consists of several SQL statements that make up the compound SQL statement block.

8.

A RETURN statement. In this example, the RETURN statement returns the results of a query that retrieves employee number, last name, department, and birth date information from a table named EMPLOYEE for all employees whose birth date is the current month that work in the department specified.

9.

The key word END, which is used to indicate the end of a compound SQL statement block.

SQL procedural language (SQL PL) statements The SQL Procedural Language (SQL PL) is a set of SQL statements that was introduced in DB2 Version 7 to provide the procedural constructs necessary for implementing control flow logic around traditional SQL queries and operations. SQL PL has evolved since then and the current set of SQL PL statements and language features provides support for comprehensive high-level programming in SQL. SQL PL has a simple syntax that includes support for variables, conditional statements, looping statements, transfer of control statements, error management statements, and result set manipulation statements. And, as you have already seen, you can use a subset of the SQL PL statements available in compound statement SQL functions. The SQL PL statements that can be used in the body of a compound statement SQL function include the following: • Variable related statements • DECLARE [Variable] DEFAULT [Value] • DECLARE [Condition] • SET (assignment-statement) • Conditional statements • IF • Looping statements

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 31 of 38

developerWorks®

ibm.com/developerWorks

• FOR • WHILE • Transfer of control statements • CALL • ITERATE • LEAVE • RETURN • Error management statements • SIGNAL Figure 10 shows a compound statement SQL scalar function that contains several SQL PL statements. Figure 10. Scalar SQL function with SQL PL statements

The SQL PL statements used in the SQL function shown in Figure 10 are as follows: 1.

The DECLARE [Variable] statement, which is used to create one or more local variables that will be used inside the function body.

2.

The SET statement, which is used to assign values to variables.

DB2 SQL Functions Page 32 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

3.

A user-defined label (named loop_label), which in this case is associated with a WHILE loop and can be used inside the WHILE loop for branching.

4.

The WHILE statement, which is used for looping.

5.

The IF statement, which is used together with the ELSEIF and the ELSE statement to perform conditional processing.

6.

The ITERATE statement, which is used to go to a particular label.

7.

The LEAVE statement, which is used to break out of a loop.

8.

The RETURN statement, which is used to return a scalar value or result set to the user or application that invoked the function.

Error handling in SQL functions Ideally, error handling in SQL functions involves trapping and processing any error and/or warning conditions that may occur within the scope of the function, and then communicating information about the error back to the user or application that invoked the function. The easiest way to do this is by using the SIGNAL SQL statement. The basic syntax for this statement is: SIGNAL [Condition_Value] SET MESSAGE_TEXT = [Message]

or SIGNAL SQLSTATE [SQLSTATE_Value] SET MESSAGE_TEXT = [Message]

where: • Condition_Value identifies a condition that was declared earlier within the compound statement that contains the SIGNAL statement. • SQLSTATE_Value identifies a string constant with exactly 5 characters that represents an SQLSTATE value. • Message identifies a string, up to 70 characters in length, that describes

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 33 of 38

developerWorks®

ibm.com/developerWorks

the error or warning condition encountered. For example, Figure 11 shows a compound statement SQL scalar function that is designed to convert a DECIMAL value to a DATE value that has been coded to trap and process errors. In this function, a condition is defined for SQLSTATE 22007 the SQLSTATE value for an invalid date (indicated by item 1 in Figure 11 ), and the SIGNAL statement is used to signal a warning or error condition to the user or application that invoked it if a value of 0 is provided as input (indicated by item 2 in Figure 11 ). Figure 11. Scalar SQL function with flow control statements

Section 5. Invoking SQL functions How an SQL function is invoked depends, in part, on the type of value the function has been designed to return. If the function is a scalar function, it can be invoked in a SELECT, VALUES, or SET statement. For example, the single statement SQL scalar function shown in Figure 3 could be invoked by executing a VALUES statement that looks something like this: VALUES sqlfunc.strip_ltblanks(' This is a test. '), When this particular VALUES statement is executed, the leading and trailing blanks are removed from the string provided, and the value "This is a test" is returned.

DB2 SQL Functions Page 34 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

On the other hand, if the function is a table function, it must be referenced in the FROM clause of a SELECT statement. For example, the compound statement SQL table function shown in Figure 9 could be invoked by executing a SELECT statement that looks something like this: SELECT * FROM TABLE(sqlfunc.bday_this_month('A00')) When this SELECT statement is executed, a result set containing employee number, last name, department, and birth date information for all employees who have a birth date this month that work in department 'A00' is returned.

Section 6. Conclusion In this tutorial, you have seen how UDFs (UDFs) can be used to extend the functionality available with the rich set of built-in functions that are provided with DB2.You have learned that four types of functions are available: scalar, aggregate, table, and row, and that five different types of UDFs can be created: sources, SQL, external scalar, external table, and OLE DB table. You've also seen that SQL functions are constructed using only SQL and can be scalar in nature, return a single row, or return an entire table, depending on how they have been defined. Every SQL function consists of five essential components: • A function name • A sequence of parameter declarations (if there are any) • A declaration of what the function will return • One or more function options • The function body The body of the function itself can consist of a single SQL statement or a compound SQL statement. A compound SQL statement is a special mechanism that allows you to group multiple SQL statements into a single executable block; the key words BEGIN ATOMIC and END are used to indicate the beginning and end of a compound SQL statement block. In this tutorial, you also saw that error handling in SQL functions involves trapping and processing any error and/or warning conditions that may occur within the scope of the function, and then communicating information about the error back to the user or application that invoked the function. Usually, this is done using the SIGNAL SQL

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 35 of 38

developerWorks®

ibm.com/developerWorks

statement. You also saw that if a function is scalar in nature, it can be invoked in a SELECT, VALUES, or SET statement and if a function is a table function, it must be referenced in the FROM clause of a SELECT statement. You should now be comfortable constructing and deploying SQL functions in any DB2 database environment.

DB2 SQL Functions Page 36 of 38

© Copyright IBM Corporation 2008. All rights reserved.

ibm.com/developerWorks

developerWorks®

Resources Learn • Read Part 1 of the series, "SQL Procedure Language," to learn about SQL Procedure Languag, including a variable, condition, and handler declaration, flow of control and iterative statements, as well as an error-handling mechanism. • In Part 2 of the series, "DB2 SQL Procedures," learn about DB2 9.5 SQL procedures, including an introduction to stored procedures, the advantages of using stored procedures, and the differences between SQL procedures and external procedures. • Part 5 of the series, "Advanced SQL features," learn about IBM DB2 temporary tables, ADMIN_CMD procedure, savepoints and other advanced SQL features. • Visit the Test 735: DB2 9.5 SQL Procedure Developer page to get comprehensive resources related to the exam. • The DB2 Family Fundamentals tutorial series gives you the resources you need to ace the DB2 Family Fundamentals test. • Visit the DB2 v9.5 Information Center to learn more about DB2 SQL procedures. • Visit the developerWorks resource page for DB2 for Linux, UNIX, and Windows to read articles and tutorials and connect to other resources to expand your DB2 skills. • Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community. Get products and technologies • Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®. • Download a free trial version of DB2 9 for Linux, UNIX, and Windows. • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications. Discuss • Check out developerWorks blogs and get involved in the developerWorks community.

DB2 SQL Functions © Copyright IBM Corporation 2008. All rights reserved.

Page 37 of 38

developerWorks®

ibm.com/developerWorks

About the author Roger E. Sanders Roger E. Sanders has been designing and developing databases and database applications for more than 20 years and has been working with DB2 9.5 and its predecessors since it was first introduced on the IBM PC (as part of OS/2 1.3 Extended Edition). He has written articles for IDUG Solutions Journal and Certification Magazine, authored DB2 tutorials for IBM's developerWorks Web site, presented at several International DB2 User's Group (IDUG) and regional DB2 User's Group (RUG) conferences, taught classes on DB2 Fundamentals and Database Administration (DB2 for Linux, UNIX, and Windows), and is the author of seventeen books on DB2 and one book on ODBC. Roger is also a member of the DB2 Certification Exam development team and the author of a regular column ("Distributed DBA") in IBM Database Magazine.

DB2 SQL Functions Page 38 of 38

© Copyright IBM Corporation 2008. All rights reserved.