Before you start
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.
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).
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
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.
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.)