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:
- Built-in XML support
- 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:
- The IBM i Knowledge Center includes an SQL XML programming guide.
- There is a tutorial for XMLTABLE in the previously mentioned SQL/XML Programmer’s Guide, Using XMLTABLE to reference XML content as a relational table.
- There is a tutorial for XMLTABLE in the previously mentioned SQL/XML Programmer’s Guide, Using XMLTABLE to reference XML content as a relational table.
- See also my previous i Can blog post on XMLTABLE, i Can Use XML in a Relational World.
- RPG programmers may find the RPG examples in this article, Using RPG to exploit IBM Db2 XML support, helpful.
- A whitepaper, Replacing Db2 XML Extender with integrated IBM Db2 for i XML capabilities, was published that offers an extensive comparison between the built-in XML support and the XML Extender option of the DB2 Extender product (5770-DE1). The paper illustrates solutions for common problems using the built-in XML support and includes several RPG examples.
- I have posted a short summary of the advantages that an integrated XML solution offers on Mike Cain’s Db2 for i blog, Integrating XML – Past, Present, and Future.
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.