Develop DB2 SQL user-defined functions using WebSphere Studio Application Developer

An IBM® DB2® Universal Database™ (UDB) user-defined function allows you to build functions that meet your business needs. Learn how to use WebSphere® Studio Application Developer to develop DB2 SQL user-defined functions.

Share:

Danna Nicholson, Advisory Software Engineer, IBM

Danna NicholsonDanna is currently the DBA for IBM Software and Technology Group's Solutions Enablement Web Services team. This team provides Web sites that allow IBM Business Partners to locate technical resources that will help them develop on IBM hardware platforms. Danna has over 17 years experience as a software developer and DBA, specializing in database design, administration, and data replication.



20 January 2005

Introduction

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.


Software versions

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
New Project window
Figure 2. New Project example
New Project window

Give the new project a name and click the Finish button.

Now open the Data perspective.

Figure 3. Data Perspective
Data Perspective

Now right-click on the DB Servers pane in the lower-left corner and select New Connection.

Figure 4. New Connection window
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
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
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
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
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
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
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
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
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
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
Modify the function code
Figure 15. Modified function code
Modified function code

When you are finished modifying the function code, right-click the function name and select Build.

Figure 16. Build the function
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
Setting input parameters

Look for your results in the lower-right corner.

Figure 18. Results
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).

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, WebSphere
ArticleID=33317
ArticleTitle=Develop DB2 SQL user-defined functions using WebSphere Studio Application Developer
publish-date=01202005