iCan Blog Archive

The existence of Web services creates the potential to unite the data from remote sources with the data that is stored locally in a relational database. For customers who have not yet upgraded to the latest technology updates of Db2 for i in 7.1, integrating a Web service with Db2 for i might be complicated. A combination of non-SQL and SQL code is usually needed to send the HTTP request, and also to construct, interpret and process the messages that are exchanged between the client and server. These infrastructure-related details are not what most developers want to spend their time on; the more desirable option is to focus on using the Web service as a tool to understand and process the business data.

Utilizing a Web service is a lot easier with Db2 for i in 7.1 because of two important features:

  1. Built-in XML support
  2. HTTP user-defined functions (UDFs) in the SYSTOOLS schema

Both of these features are available in the Db2 for i PTF group SF99701 level 23 or later. A portion of this support also exists in previous PTF group levels; however, IBM recommends that customers always load the most recent updates to get the latest enhancements and fixes. An additional requirement is that the HTTP UDFs require Java 1.6 or later (5761-JV1 Option 11, 12, 14, or 15) in order to run.

The built-in XML support is important because XML is commonly used for sending and receiving information between clients and Web services; thus, being able to process XML within SQL is extremely helpful. The integrated SQL/XML support that is available in Db2 for i in 7.1 is a significant improvement over the non-integrated XML solutions for Db2 for i that had to be used in previous releases.

The sample HTTP UDFs in SYSTOOLS allow developers to access Web services from SQL, without having to write their own special purpose code or tools. The functions are samples, meaning that they are not covered under the IBM service agreements. Some customers will use the functions in their final solution without change; other customers will design and implement their own tools that do exactly what those customers need. Customers who build their own solutions still get an advantage in that the IBM-supplied tools can be used during the development of the application—allowing the development process to be more agile.

Be on the lookout for a “Part Two” i Can blog entry that will demonstrate an SQL query that uses the new HTTP functions and the built-in SQL/XML functions to convert data from a Web service into rows and columns. I expect Part 2 will be published in a few weeks.

For those developers who need an in-depth explanation of the new technology, the Db2 for i development team has published a whitepaper, Accessing Web services using IBM DB2 for i HTTP UDFs and UDTFs, that describes the HTTP functions in detail. In addition to the functions themselves, this paper provides several examples for accessing resource-oriented (ROA/REST) Web services, and service-oriented (SOA) Web services that employ SOAP as a protocol.

In addition to the whitepaper, a large number of resources are available for developers who are interested in this technology.

HTTP functions in SYSTOOLS is where you can find the documentation.

For those who are familiar with Db2 for z/OS and Db2 for LUW, those platforms have delivered similar HTTP functions, and you’ll find information in the article, Accessing HTTP and RESTful services from Db2: Introducing the REST user-defined functions for Db2, on IBM Developer.

The Db2 for i development team has published an extensive amount of information on the SQL/XML support available in Db2 for i in 7.1:

XML is a complex topic. If you are looking for education or consulting, consider IBM Lab Services. The Db2 for i Center of Excellence team works closely with the Db2 for i development team and provides Db2 for i customers with exceptional training and consulting services.

This week’s blog article was written by Nick Lawrence. Nick works for IBM in Rochester, Minn., on Db2 for IBM i. His responsibilities include full-text search for Db2 and SQL/XML. Thanks, Nick!

This blog post was edited to fix broken links on March 17, 2020.

This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.