DB2 Universal Database: Building User-Defined Functions using the Data Definition View, Part 4

In Part 1 of this series, I discussed how to use the Database Explorer view in the IBM Rational Application Developer for WebSphereSoftware (Rational AD) product to create and work with IBM DB2 Universal Database (DB2 UDB) for Linux, UNIX, and Windows database connections. Part 2 introduced you to some of the many capabilities provided by the Data Definition view in the Rational AD integrated development environment (IDE). Part 3 taught you how to use Rational AD to build a stored procedure, deploy it, and test it. In this part, we will look at how to build a user-defined function (UDF).

Note: Although not a requirement, the steps in this article assume that you have read the first three parts in this series, and have built the associated schema objects outlined in each of those parts.

Building a User-Defined Function

The steps to build a UDF are very similar to the steps you used to build a stored procedure in Part 3. For this reason, I will provide a high-level overview of this process (pointing out differences where warranted). In the next section, I will include a more complex, detailed example of building a UDF that includes a call to a Web service.

The following steps show you how to build an SQL-based UDF, called STAFF20, that selects all of the employees that belong to Department 20 in the STAFF table in the SAMPLE database.

To build this UDF, perform the following steps:

1.  Select the schema in the database where you want to create the UDF within your project (I’m assuming you’re using the DATABASEJOURNALSERIES project, and that within this project resides the SAMPLE database), right-click, and select New->SQL User-Defined Function, as shown below:

Note that you can also use the Rational AD wizards to build a WebSphere MQSeries-based function. This type of function will expose message queues as “virtual tables”, allowing application developers to call functions to read passively, read destructively, and write to the message queue. This simplifies an application developer’s environments using WebSphere MQSeries technology as the enterprise service bus (ESB), because they are, for the most part, isolated from the complexities of the queuing technology and can rely on their knowledge to invoke functions and pass arguments to those functions.

2.  The New SQL User-Defined Function (UDF) wizard opens. Type a name for your UDF in the Name field (for this example, use STAFF20), select the Build check box so that Rational AD will automatically build the UDF when you complete the wizard, and then click Next.

3.  The SQL Statement page for this wizard opens. Type the SQL statement shown in the following figure (SELECT * FROM STAFF WHERE DEPT=20) in the Statement window (or build it using the SQL Assist button, as you did in Part 3 of this series), and click Next.

Note: Ensure that you type the correct schema name to qualify the STAFF table in the Statement field. For example, if the STAFF table resides in the PAULZ schema, you would type this SQL statement as: SELECT * FROM PAULZ.STAFF WHERE DEPT=20

You can use the Import button to import an existing SQL statement into the Statement window. (I will cover SQL statements and the Rational AD SQL Editor in the next part of this series.)

Additionally, you can parse your SQL statement to ensure that it is correct. Notice in the previous figure how the Statement window changes when Rational AD parses its contents. You will see that the Rational AD IDE fully qualifies the table and columns within the query and structures it for readability.

Note: The database manager must be running for Rational AD to properly parse the SQL statement you generated.

If Rational AD cannot figure out to what schema the table belongs, it may return the following error (certain client or default may end up avoiding this error):

You can see in the previous figure that Rational AD was unable to determine where the STAFF table resides, or which STAFF table the statement was referring too when it was parsed. Perhaps it would be nice if the tooling took a guess based on where the wizard was invoked (in my example, I invoked it under the PAULZ schema). If you recall from Part 3, I noted that fully qualifying objects is a best practice, and this example enforces that assertion.

If you encounter this error, simply prefix the table name (STAFF) with your schema name (in my case, PAULZ), as shown in the previous parsed Statement window. Note that you do not have to fully qualify the column names; Rational AD will know what table they belong to because the table is fully qualified.

In this step, you can also specify the type of result the UDF will return using the Result set drop-down box. You have two options: scalar (a single value will be returned) or table (multiple values will be returned in a table format). Since this UDF returns multiple values, you have to select the Table option in the Result Set drop-down box. If you don’t, when you go to build the UDF, you will receive the following message:

SQLSTATE 42823: Multiple columns are returned from a subquery that is allowed only one column error.

4.  The Return Data Type page for this wizard opens. This window lists the data types that will be returned by this UDF. Click Next.

Because this example builds a UDF that returns multiple results in a table format, you can see that Rational AD automatically generated the corresponding data types for the returned data. If you were building a scalar UDF, the Return Data Type page would ask you to define the scalar result data type, as shown below:

5.  The remaining pages (shown below) provide identical functions to their corresponding windows in the New Stored Procedure wizard detailed in Part 3. For this example, click Finish.

Remember that we specified that this UDF would build automatically at the completion of this wizard. You can tell if the UDF was built successfully by looking at the output in the DB Output view:

Now you can test the UDF in multiple ways, including the built-in test feature in the Rational AD IDE, as shown below:

Note that the results of this UDF include only those employees that belong to department 20 in the STAFF table. (See the highlighted DEPT column in the previous figure.)

Paul Zikopoulos
Paul Zikopoulos
Paul C. Zikopoulos, BA, MBA is the Program Director for the DB2 Evangelist team at IBM. He is an award-winning writer and speaker with more than 14 years of experience with DB2. Paul has written more than 230 magazine articles and 11 books on DB2 including, Information on Demand: Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand: Introduction to DB2 9 New Features, Off to the Races with Apache Derby, DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Clusters) and a DB2 Certified Solutions Expert (BI and DBA). In his spare time, he enjoys all sorts of sporting activities, including running with his dog Chachi, avoiding punches in his MMA training, and trying to figure out the world according to Chloë - his daughter.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles