DB2 9.5 SQL Procedure Developer Certification Examination 735 preparation, 3rd part

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.