DB2 Universal Database (DB2 UDB) user-defined functions (UDFs) allow you to extend the built-in functions of DB2 by adding your own function definitions. They allow you to customize DB2 for your system by writing your own functions for transformations, calculations, and so on, to meet your business needs.
UDFs encourage code reuse, therefore increasing productivity of application developers. If you have many programs that implement the same set of logic, a UDF will allow you to standardize the logic and have all the programs using the same implementation. Once you define a UDF to DB2, you can use it in SQL statements just like you would use a built-in DB2 function. If you write the function in an application program instead of a UDF, then users of SQL query tools such as CLP will not be able to access your function. A UDF allows any front end, including a Java™ program, to access the standard logic.
Invoking a UDF directly from the database engine instead of from application code can also have considerable performance advantages, especially when it is used to filter data before sending it back to the application for further processing.
This article is not intended to be a complete guide to user defined functions. Rather, it is a primer about how to create UDFs in WebSphere Studio Application Developer (Application Developer). Refer to your DB2 documentation for details about UDFs and the SQL Procedural Language in DB2. DB2 also comes with some good samples in your DB2 install directory in the samples\sqlproc directory. Also, please note that UDFs can be written in Java as well, and other articles describe how to write Java UDFs.
Why create your UDFs in WebSphere Studio Application Developer?
You can use the DB2 Development Center to create UDFs. You can even manually create UDFs using any text editor. However, creating your UDFs in Application Developer gives you several advantages. Having the functions grouped in projects, possibly with other Java functions and code, allows a team to keep all of their code organized in one place. The ability to save the code for the SQL functions in a version control repository such as CVS or Clear Case is important to keep revisions, keep change history, and share the code with other team members.
The examples in this article use the following versions of DB2 UDB and Application Developer:
- WebSphere Studio Application Developer V5.1.2
- DB2 UDB V8.1
Create a new UDF
There are two types of UDFs in DB2: scalar and table functions. A scalar function returns a single value each time it is called. A table function returns a table with many rows and columns. Table functions enable you to efficiently use relational operations and the power of SQL on data that is not in an actual table. It is like using a view, but since a UDF can take input parameters, it is like creating the view "on the fly."
We will now go through an example of how to create a new scalar DB2 function.
First, create a new Java project in Application Developer.
Figure 1. New Project
Figure 2. New Project example
Give the new project a name and click the Finish button.
Now open the Data perspective.
Figure 3. Data Perspective
Now right-click on the DB Servers pane in the lower-left corner and select New Connection.
Figure 4. New Connection window
The new database connection window will open, as shown above.
The Connection Name can be whatever you want, but it is usually good to name it the same as your database name. Use your correct database name, user ID and password, hostname, and port number. Make sure the Class location of the db2java.zip file is correct. Click Finish.
If your information is correct, it will load your database schema.
Figure 5. Connection completed
Now right-click on the connection name and select Import to Folder. Browse to the project name that you just created earlier, and click Finish.
Figure 6. Import to Folder
Now when you expand your project (in the upper pane) you will see all of your current database information.
Figure 7. Expanded database information
Right-click on User defined functions and select New, then SQL User Defined Function. Fill out the new user-defined function information, as follows. Give the new function a name:
Figure 8. New UDF window
Click Next. Click the Change button and type in an SQL statement that is close to what you want to return when you are finished. Your Return Type can either be Scalar or Table. The return type of “Table” should be used if you want to return more than one row or value. For the purposes of this article, we will choose Scalar, which means that the function will only be returning one value. Click Next.
Figure 9. New UDF window 2
Since the Scalar return type has been selected, a data type for the returned value will now be required. Choose TIMESTAMP and click Next.
Figure 10. New UDF window 3
The next window will ask for any input parameters that are needed by the function. Use the Add, Change, and Delete buttons to set up all the input parameters that are needed and click Next.
Figure 11. New UDF window 4
The next window will ask for the Specific Name. It should usually be the same as the function name, but you can use this if you need to have “overloaded” functions that have the same name and different parameters.
Figure 12. New UDF window 5
The last window will summarize the selections you have made so far. Click Finish when everything is correct.
Figure 13. New UDF window Summary
Now your new function will show in the main window. Modify the content of the function as needed and save it.
Figure 14. Modify the function code
Figure 15. Modified function code
When you are finished modifying the function code, right-click the function name and select Build.
Figure 16. Build the function
Check for error messages in the lower-right corner. If the build is successful, you can now right-click the function name and select Run. This will give you a chance to type in your input parameters.
Figure 17. Setting input parameters
Look for your results in the lower-right corner.
Figure 18. Results
Congratulations! Now you have an Application Developer project for DB2 user defined functions. You can check your new project in to a version control system of your choice, and share your project with other developers.
How to call your new function from a DB2 CLP window:
- Scalar functions are called as follows:
select SSE.ADD_TO_DATE(3) from sysibm.sysdummy1
- Table functions are called like this:
select * from TABLE( schemaName.functionName(parameters) ) AS T
To call your function from a Java program prepared statement, you will need to use a CAST for each input parameter in your prepared statement like this:
- Select sse.add_to_date( CAST(? AS INTEGER ) ) from sysibm.sysdummy1;
- Select 1 from sysibm.sysdummy where schemaName.functionName( CAST(? AS VARCHAR(35)) ) = 'Y' ";
If you have to change the input parameter list of your function, you will need to drop it yourself because Application Developer will not be able to drop and re-create it when you do the Build. You can right-click the function name in the DB Servers pane in the lower-left corner of Application Developer, and select Drop. (If you need functions with the same name and different input parameters, remember to use a different Specific Name).
- DB2 SQL Procedural Language for Linux, UNIX, and Windows (IBM Press, 2002) is a great guide to SQL PL.