Featured Database Articles

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:

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.

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.

Trademarks

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.

Disclaimer

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.