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

Tuesday Apr 25th 2006 by Paul Zikopoulos

Part 3 of this series discussed 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).

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:

Click for larger image

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.)

Building a User-Defined Function that Calls a Web Service

In the previous section, I showed you how to build a simple SQL-based UDF. In this section, I will show you how to build a more complex UDF that invokes a Web service, which returns a single value that represents the current stock price of your favorite stock on the NYSE (a scalar result).

Note: The steps in this section assume you have the DB2 XML Extender installed (if your target database is a DB2 UDB Version 8 database) and have enabled your database for Web services support. For more information, refer to the DB2 UDB Information Center.

To build a UDF that calls a Web service, perform the following steps:

1.  Click File->New->Other and select the Web Service User-Defined Function project in the Data folder, as shown below:

Click for larger image

2.  The Web Service WSDL File Selection page opens. Type the URL (http://services.xmethods.net/soap/urn:xmethods-delayed-quotes.wsdl) that you see in the following figure in the WSDL file name or URL field, and click Next.

Click for larger image

You can specify a URL that points to a Web Services Description Language (WSDL) file on your machine or local network, or to a Web service's WSDL file that resides on the Web at a specific URL or UDDI location. You can also use Rational AD to generate WSDL for your Web services with mere clicks-of-a-button. (More on that in a future installment of this series.)

For this example, I have decided to use a public Web service that is already been written to take an input parameter that represents a stock ticker symbol and return the current trading price of that equity with a 20-minute delayed quote.

This may be a useful Web service if you were building a wealth management tracking application since a Web service exists to return the stock price of a particular equity. Why would you build one yourself?

You don't care what language this logic is written in, who wrote it, where it 'lives', and so on -- and that is the point of Web services. The WSDL will provide you with methods to the Web service and descriptions of input values.

The X Methods Web site is home to literally hundreds of Web services written in all sorts of languages from C, to C#, to Java, and more.

The Delayed Stock Quote Web service is located at: http://www.xmethods.com/ve2/ViewListing.po;jsessionid=B1JTryHLJ-9Zwbxzm922gSvK(QHyMHiRM)?key=uuid:889A05A5-5C03-AD9B-D456-0E54A527EDEE and includes a link to the WSDL that you should input into the previous figure's WSDL file name or URL, as shown below:

3.  The Select a Database Connection and Schema page of this wizard opens. Ensure that the Generate UDF model and deploy the UDF into your database radio button is selected, and use Browse to locate a schema where you want to create the UDF, which resides in a DB2 UDB database, and then click Next, as shown below:

4.  The Select the user-defined functions (UDFs) that you want to create page for this wizard opens. Ensure that the getQuote method is in the UDFs that will be created pane, and click Next.

You can see in the previous figure that the WSDL file you specified points to the Web service that this UDF will invoke. More specifically, the WSDL file contains a pointer to a method called getQuote. Some WSDL files contain multiple operations, but this one is simple. You can also see that the Rational AD product automatically moved the getQuote method to the UDFs that will be created pane because it was the only method exposed by this Web service.

5.  The Specify Options page opens. There are multiple options on multiple tabs that allow you to further customize the UDF, how it will invoke the Web service, the types of parameters passed to (and received from) the Web service, and more. The defaults are fine for this example, so click Next if you want to review the summary, or click Finish to build the UDF now.

Details of all of the options on this page are outside the scope of this article. However, if you click the Parameter tab, you can see the IN and OUT parameters that the Web service will accept and return, as shown below:

Once again, we can verify that our UDF built successfully by referring to the DB2 Output view:

Finally, we can test the UDF in the Rational AD IDE. For example, the following figure illustrates how to call our new UDF and ask for the current (20-minute delayed) quote for a favorite stock of mine - IBM:

You can see in the previous figure that the current trading price of IBM stock when I ran this UDF, which called a remote Web service, is $83.48.

Wrapping it all up

In this article, I showed you how to build a simple SQL-based UDF that returns results in the form of a table. I also showed you how to build a more complex scalar UDF that invokes a Web service.

So far in this series, you have learned how to work with database connections, create database schema objects and stored procedures, and how to generate UDFs. In the next part of this series, I will talk about how to generate SQL statements that can be used in your applications using the SQL editor that is built into the Rational AD IDE.

About the Author

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than ten years of experience with DB2 products and has written numerous magazine articles and books about it. Paul has co-authored the books: 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 Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: paulz_ibm@msn.com.


IBM, DB2, DB2 Universal Database, MQSeries, Rational, and WebSphere are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Java is a trademark of Sun Microsystems, Inc. in the United States, other countries, or both.

Windows is a trademark of Microsoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Linux is a trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2006. All rights reserved.


The opinions, solutions, and advice in this article are from the author's experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author's knowledge at the time of writing.

Mobile Site | Full Site