Level: Intermediate Roger E. Sanders (sanders_roger@emc.com), 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
user-defined functions and walks you through the basic steps used to construct user-defined functions. 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).
Objectives - 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.)
Formats html, pdf
Tutorial series overview
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. user-defined functions, 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 the stored procedure can be invoked and how you can
share data between procedures and functions. It introduces the DB2
development tool, 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.
| 
