Using the New SQL HTTP Functions (Part 1 of 2)
As I've mentioned in past ramblings, IBM’s investment in open source extends into some of the core components of the operating system. The rich catalog of IBM i Services (sometimes called "SQL Services") is a fantastic illustration of this concept. The data queue services, for instance, provide an integration point between database activity and Apache Camel. The JSON publishing and consumption functions allow for better interoperability with various technologies using the popular data interchange format.
Today's topic discusses some of the most recent additions to this toolbox: new and improved HTTP functions. To start things off, I'd like to welcome fellow Business Architect Scott Forstie to give us a summary of this new technology.
From Scott: Let's Explore the New HTTP Functions
Hi Jesse. Thanks for including me in this blog thread. We both have spent time showing how IBM i clients could easily open up their IBM i, for a purpose, using SQL and RESTful services. I fondly recall teaming up with you to show how easy it was to ask IBM Watson a question, and many other interesting scenarios.
So, what’s changed and why should I care?
In IBM i 7.4 with Db2 for i PTF Group SF99704 Level 15 and IBM i 7.3 with Db2 for i PTF Group SF99703 Level 26, I am delighted to note that a new and improved set of HTTP functions have been added to Db2 for i.
These new HTTP functions are located in the QSYS2 schema, whereas the prior art was found in SYSTOOLS and on the surface, they look very similar, but “rest” assured, they have some very noteworthy advantages.
The QSYS2 based HTTP functions use the HTTP transport APIs, that is part of the Integrated Web Services (IWS) client for ILE support. Unlike their predecessor, they do not use Java! This simple and important difference should resonate strongly with our community because it means that the programmers can expect an improved experience.
Improved performance, reduced footprint and having something that is easier to integrate into an overall application are some of the reasons to care about QSYS2 based HTTP functions.
The primary functions, as seen in Figure 1, are smartly named and easy to use. The names of the QSYS2-based functions differ from those in SYSTOOLS, so there’s no risk of accidental use.
Figure 1: QSYS2-based HTTP functions
The base functions (e.g. QSYS2.HTTP_GET()) are scalar functions that return a CLOB(2G) CCSID 1208 value that is the response message.
The verbose functions (e.g. QSYS2.HTTP_GET_VERBOSE()) are table functions that return two CLOB(2G) CCSID 1208 columns containing the response message and the response HTTP header.
The clever ability of passing parameters as part of the URL is easier to accomplish using the helper functions shown within Figure 2.
Figure 2: QSYS2 based HTTP helper functions
A simple side-by-side comparison highlights three items (see Figure 3):
- Note the similarity in the SQL statement text. To shift to the new QSYS2 functions might be as simple as changing “SYSTOOLS.HTTPGETCLOB” to “QSYS2.HTTP_GET”.
- Note the difference in query execution time
- Note the difference in thread count after the query execution. The additional threads in the SYSTOOLS example are related to the use of Java.
Figure 3. Contrasting SYSTOOLS versus QSYS2 HTTP Get functions
Not only has the Db2 for i team done an outstanding job of delivering the new QSYS2 HTTP functions, but we’ve also published some helpful documentation. Please give it a read, as it will certainly answer some of the questions you’re likely to have.
What About TLS?
Thanks, Scott, for that fantastic overview!
I'd also like to add that there’s a distinct difference between the "old" and "new" functions: handling of SSL certificates. Since the SYSTOOLS flavor used Java, many common web sites across the Internet were trusted "by default," since Java ships with a set of common certificate authorities (CAs) like Digicert, Entrust, etc. This provided a good out-of-box experience most of the time, but also injected a new obstacle when sites or APIs used a certificate not in that trusted set. In that case, getting it to work involved updating the Java installation's certificate store, and it's not a trivial task.
The QSYS2 functions use system support for TLS encryption. If your endpoint isn't trusted, you will get an SQL0443 error out of the system GSKit libraries (see Figure 4).
Figure 4. Error when HTTP destination isn’t trusted
The distinct benefit of the QSYS2 functions, however, is that certificates are managed by Digital Certificate Manager (DCM), so it's pretty simple to manage your certificates. Here are some ways to add trusted certificates for TLS:
- Refer to the steps in the documentation for adding SSL certificates
- Installing the latest DCM PTFs and using the "Populate with CAs" feature in the DCM interface. At the time of this writing, the latest PTFs are SI77131 and SI77132 for IBM i 7.4, or SI77118 and SI77121 for IBM i 7.3.
- Use the “dcmimport” tool from the DCM Tools open source project.
What's This Got to Do With Open Source?
After all, this is an open-source blog, right? Earlier, I hinted that these new functions were somehow good for users of open-source technologies. Next week, I will publish part two of this two-part series. In that discussion, we will take a deeper dive and explore how database applications can use these new capabilities to interface with open-source technologies!
Read "Using the New SQL HTTP Functions (Part 2)" here.