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!
In part one of this two-part blog entry, the idea was introduced that DB2 for i could be integrated with a Web service, using the user-defined functions in the SYSTOOLS schema and the built-in SQL/XML support. Part one includes a number of links to information about the user-defined functions, and also to information on the built-in SQL/XML support. One important resource was a link to a whitepaper that provides the complete details for these functions.
The second part of this blog entry examines a specific scenario where the data from a Web service is converted into relational rows and columns.
Suppose that a delivery service stores its vehicles' fuel and maintenance costs in a DB2 for i database. In addition to the actual expenses incurred, the application's processing involves the weekly national average cost (per gallon) of fuel. In other words, an SQL query might reference the national average, or the average might be stored in the database for auditing purposes.
This problem has two parts to it.
- The database must retrieve an XML document that contains the current week's average fuel prices from a Web service (this example uses a Web service located at http://www.fueleconomy.gov).
- After retrieval, the XML document needs to be transformed into rows and columns.
Figure 1 is a diagram of this setup.
Figure 1 - Web service interaction with DB2 for i
Using the functions that have been provided in SYSTOOLS, we can use SQL to get the average fuel price for the current week as an XML document. This is accomplished using the HTTPGETBLOB function and is shown in Figure 2.
Figure 2 - Retrieve current fuel prices
VALUES
XMLPARSE(DOCUMENT
SYSTOOLS.HTTPGETBLOB(
--- URL ---
'http://www.fueleconomy.gov/ws/rest/fuelprices',
--- HTTP request header ---
'<httpHeader>
<header name="Accept"
value="application/xml"/>
</httpHeader>'
)
)
In Figure 2, the URL identifies the resource that is to be obtained, and the HTTP request header indicates that the data should be returned in an XML format. The XML document that is returned from the Web service is shown in Figure 3.
Figure 3 - Returned XML document
<?xml version="1.0" encoding="UTF-8"?>
<fuelPrices>
<cng>2.12</cng>
<diesel>4.16</diesel>
<e85>3.47</e85>
<electric>0.12</electric>
<lpg>2.56</lpg>
<midgrade>3.89</midgrade>
<premium>4.04</premium>
<regular>3.75</regular>
</fuelPrices>
The XML document shown in Figure 3 is not useful in SQL unless SQL has a way of understanding the relationships and data defined by the document. Fortunately, the XML support in DB2 for i 7.1 includes the built-in XMLTABLE table function. Figure 4 shows a query that uses the XMLTABLE function to convert the XML document in Figure 3 into a result set (rows and columns).
Figure 4 - Current fuel prices as rows and column
SELECT fuel_prices.*
FROM
XMLTABLE('fuelPrices' PASSING
XMLPARSE(DOCUMENT
SYSTOOLS.HTTPGETBLOB(
--- URL ---
'http://www.fueleconomy.gov/ws/rest/fuelprices',
--- HTTP Request Header ---
'<httpHeader>
<header name="Accept"
value="application/xml"/>
</httpHeader>'
)
)
COLUMNS
"regular" DOUBLE,
"midgrade" DOUBLE,
"premium" DOUBLE,
"diesel" DOUBLE) fuel_prices;
Figure 5 shows the result set that was created by the query in Figure 4.
Figure 5 - Result set
The query in Figure 4 is an SQL query, meaning that it can be easily incorporated into other SQL statements or queries that need to reference the data in Figure 5. An example of how to join data from a Web service with relational data is included in the whitepaper.
Although the HTTP GET request might be the most commonly utilized HTTP method, the SQL UDFs can also invoke the POST, PUT and DELETE HTTP methods. The additional HTTP methods can be invaluable for causing a Web service to take some action in response to an event that happens in a DB2 for i database. Some examples of these HTTP methods are included in the whitepaper.
The HTTP functions really make it easier to access Web services, allowing a software architect more time to figure out the most effective way to use the service. The built-in XML support allows developers and architects to spend their time utilizing the information in XML documents, rather than writing code to compose and decompose XML.
Recent Comments