Before you start
About this series
These six DB2 SQL Procedure Developer Tutorials Discuss all the basic constructs and methods of SQL procedural Language, and explain how to use SQL procedural Language in stored procedures, UDF, and triggers. Includes error handling and deployment. Also discusses some of the DB2 9.5 advanced features, such as optimistic locks, hierarchical queries, and declared global temporary tables. This series discusses how to call stored procedures, UDF and triggers, and how to share data between procedures and functions. It introduces DB2 development tools, including IBM Data Studio. These tutorials provide a solid foundation for you to prepare each part of your exams. However, you should not just rely on these tutorials to prepare for exams.
About this tutorial
This tutorial delves into the UDF and focuses on SQL functions. This is the third tutorial in a six-part series that helps you prepare the IBM DB2 9.5 SQL Procedure Developer certification exam (exam 735).
Goal
After completing this tutorial, you should be able to:
Mastering the correct use of functions
Creating SQL functions using the CREATE FUNCTION statement
Mastering the correct structure of SQL function body
Returning values and tables from SQL functions
Call function
Prerequisite conditions
To participate in the DB2 9.5 SQL Procedure Developer exam, you must first pass the DB2 9 Foundation (730 exam). You can use the "DB2 9 Basics" Tutorial series to prepare for this exam.
This tutorial is written for elementary and intermediate DB2 programmers. You should have a basic understanding of how relational databases work and database and database programming constructs. In addition, you should be familiar with the DB2 Command line Processor (CLP) and understand the basics of SQL.
System Requirements
To run the examples in this tutorial, you need access to the sample database provided by the DB2 9.5 database server and DB2. (You can create a sample database by executing command db2sampl from DB2 command line Processor).
What is a function?
In DB2, a function is a set of encapsulated instructions used to perform a specific operation; You can use one or more input parameters to customize the operation, and you can use one or more output parameters to return the result. There are four types of functions:
Scalar functions
Aggregate functions
Table functions
Row function
DB2 provides a number of robust built-in functions that are defined in the SYSIBM pattern. The built-in functions available include scalar functions (such as UCase ()), aggregate functions (such as AVG ()), operator functions (such as "+"), and conversion functions (such as decimal ()). Functions are usually called in the select list and FROM clause of a query.
Scalar functions
A scalar function is a function that returns a scalar value. You can use scalar functions to perform simple tasks, or you can use values provided by function input parameters to perform complex mathematical computations in scalar functions. Examples of scalar functions include built-in functions length () and substr ().
Scalar functions can be referenced anywhere in an SQL statement that supports an expression. Scalar functions can improve overall performance when used in query predicates, because the logic of a function executes directly on the server as part of the SQL statement that references it. In addition, when a scalar function is applied to a set of candidate rows on the server, it can act as a filter, limiting the number of rows that must be returned to the client. However, scalar functions also have their limitations. For example, by design, a scalar function can return only one value and cannot return multiple values and result sets. In addition, transaction management is not supported in scalar functions. Therefore, the commit and rollback operations cannot be performed in scalar function bodies.