29
Fri, Nov
0 New Articles

TechTip: Check Out the New DB2 for i HTTP Functions

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Use these new functions to easily extend your applications to the web.

 

If you've been waiting for a DB2 for i feature that is unprecedented in power and simplicity, get ready to become "unhinged." Further, prepare to hammer on your IBM i admin to install the latest database fixes, because DB2 for i 7.1 Group PTF Level 22 introduces a suite of functions that allow developers to make all manner of HTTP requests. (Actually, Level 22 had a problem, so you should skip right to the latest level, which is currently 23.) Right out of the box and without fancy programming, your apps can easily speak to a cloud server or intranet application server to download a file, contact a web service, or retrieve the source of a web page, to name a few possibilities.

 

Although I'll try to include reference links where appropriate, this tip assumes the reader has some familiarity with HTTP requests and responses.

 

If you interrogate the SYSFUNCS catalog (or drill down through IBM Navigator for i's database node), you will find the following new HTTP functions in the SYSTOOLS schema:

 

HTTPBLOBNONXML

HTTPBLOBVERBOSENONXML

HTTPBLOBVERBOSEXML

HTTPBLOBXML

 

HTTPCLOBNONXML

HTTPCLOBVERBOSENONXML

HTTPCLOBVERBOSEXML

HTTPCLOBXML

 

HTTPDELETEBLOBNONXML

HTTPDELETEBLOBVERBOSENONXML

HTTPDELETEBLOBVERBOSEXML

HTTPDELETEBLOBXML

 

HTTPDELETECLOBNONXML

HTTPDELETECLOBVERBOSENONXML

HTTPDELETECLOBVERBOSEXML

HTTPDELETECLOBXML

 

HTTPGETBLOBNONXML

HTTPGETBLOBVERBOSENONXML

HTTPGETBLOBVERBOSEXML

HTTPGETBLOBXML

 

HTTPGETCLOBNONXML

HTTPGETCLOBVERBOSENONXML

HTTPGETCLOBVERBOSEXML

HTTPGETCLOBXML

 

HTTPHEADNONXML

HTTPHEADXML

 

HTTPPOSTBLOBNONXML

HTTPPOSTBLOBVERBOSENONXML

HTTPPOSTBLOBVERBOSEXML

HTTPPOSTBLOBXML

HTTPPOSTCLOBNONXML

HTTPPOSTCLOBVERBOSENONXML

HTTPPOSTCLOBVERBOSEXML

HTTPPOSTCLOBXML

 

HTTPPUTBLOBNONXML

HTTPPUTBLOBVERBOSENONXML

HTTPPUTBLOBVERBOSEXML

HTTPPUTBLOBXML

 

HTTPPUTCLOBNONXML

HTTPPUTCLOBVERBOSENONXML

HTTPPUTCLOBVERBOSEXML

HTTPPUTCLOBXML

 

From the list, you can see there are several function sets that can be used to send an HTTP request using any of the standard HTTP methods (also known as verbs), including GET, PUT, POST, DELETE, and HEAD. Quite often, the GET verb is used to retrieve data. The particular verb you'll need to use is dictated by the web application you're communicating with. For example, if you wanted to upload an IFS file programatically to a Microsoft SkyDrive account using the available REST API, you would use either the PUT or the POST verb.

Helper Functions

In addition to the HTTP functions, there are four companion functions that are often used with HTTP requests. I’m going to take a slight detour to discuss these first:

 

BASE64DECODE

BASE64ENCODE

URLDECODE

URLENCODE

 

The Base64 functions are for encoding and decoding binary content that has been encoded as text for safe transmission over the web. For example, email attachments and binary data from a web service (such as an image) are often Base64 encoded.

 

The URL encode and decode functions are used to make sure that pieces of a URL are constructed properly because certain characters in a URL have special meaninge.g., forward slash (/), question mark (?), ampersand (&), etc. These special characters need to be handled properly lest they be misinterpreted by the HTTP server.

 

As a quick example of why this is necessary, say I want to construct a URL to do a Google search on "A&W Root Beer." My URL may look like this:

 

https://www.google.com/search?q=A&;W Root beer&client=AS400

 

In this example, q and client are parameter names in the URL. However, because the ampersand (&) is in the search text and this special character is also used to signal a new parameter in the URL, the web server is going to think W is a new parameter that has been passed improperly. The way to fix this is to make sure that the URL is “safe” by encoding the variable content. The URLENCODE function will replace spaces with a plus sign (+) and replace special characters with a percent sign (%) followed by their hex value.

 

Here is an example of how the function ensures parameter values are safely passed to the HTTP server:

 

Select 'https://www.google.com/search?q='

|| SYSTOOLS.URLENCODE('A&W Root beer','UTF-8')

|| '&client=' || SYSTOOLS.URLENCODE('AS400','UTF-8')

AS SearchURL

From SYSIBM.SYSDUMMY1;

 

The result of the expression is this (note the ampersand becomes %26):

 

https://www.google.com/search?q=A%26W+Root+beer&client=AS400

 

Technically, we didn't need to encode the client value of "AS400" because there are no special characters, but generally all non-hardcoded parameter values in a URL should be run through the encoding function to be safe. The second function parameter specifies the encoding. UTF-8 (i.e., 8-bit unicode) is the default and recommended value, but other standard encoding values such as UTF-16, US-ASCII, and ISO-8859-1 are valid.

Getting Data from the Web

Back to the task at hand. There's a lot of ground to cover with all of these functions, but for now I'm just going to focus on the HTTPGETCLOB and HTTPGETBLOB scalar functions. These functions submit an HTTP "GET" request to a specified HTTP server and return the result as a BLOB or CLOB.

 

You can think of these gems as a non-GUI web browser in a genie's lamp (or should I say in a scalar function?).   For example, running this statement with our sample URL from above will return Google's HTML response in a single column:

 

Select SYSTOOLS.HTTPGETCLOB

('https://www.google.com/search?q=A%26W+Root+beer&;client=AS400','')

From SYSIBM.SYSDUMMY1;

 

These functions accept two parameters: URL and HTTPHeaders. Although left as an empty string here, the HTTPHeaders parameter is a CLOB that accepts an XML formatted string of request header fields and values such as User-Agent that can be passed to the server. In this case, no headers were sent.

 

The abridged results (with inserted line breaks for readability) look like this, pretty much the same thing you will see if you paste the same URL query in your favorite browser and then choose the option to view the page source:

 

<!doctype html>
<html itemscope="itemscope" itemtype="http://schema.org/WebPage">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta itemprop="image" content="/images/google_favicon_128.png">
<title>A&amp;W Root beer - Google Search</title>

 

It's cool that we can easily retrieve HTML. The only problem is that it can be a pain in the neck to parse. Therefore, let's move on to a better use for these functions: the ability to call a parameterized web service and retrieve a result.  

 

Note: Many organizations are publishing data via web services. Before you attempt to retrieve and parse an HTML web page, check whether there's a web service that can supply the same information. While it's possible to parse the Google HTML results from the sample URL, it's much easier to call the equivalent Google web service-based API to return just the search result data. That saves the headache of removing the HTML markup from the data returned by a web page.

 

Let's say you need a web service that accepts a U.S. zip code and returns the corresponding city and state. Searching the web, you find this free web service URL that can be incorporated into your app:

 

http://www.webservicex.net/uszip.asmx?op=GetInfoByZIP

 

When reviewing this URL in a browser, you see there are four means of invoking the service:

?       SOAP 1.1

?       SOAP 1.2

?       HTTP GET

?       HTTP POST

 

If you're not familiar with SOAP, it originally stood for Simple Object Access Protocol (and now it's just "SOAP"). And, contrary to the name, SOAP requests can get quite complicated. When using SOAP, you're required to create an XML envelope containing parameters, etc. Here's an example of what a browser or "client" will pass to the HTTP server for a SOAP request:

 

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
   <GetInfoByZIP xmlns="http://www.webserviceX.NET">
     <USZip>string</USZip>
   </GetInfoByZIP>
</soap:Body>
</soap:Envelope>

 

SOAP requires quite a bit of XML, even for the simplest of web service (WS) invocations, so I usually avoid calling a WS via SOAP if something easier is available. This is not to say SOAP is bad, but for many web services with simple parameter data, it can be overkill.

 

In this case, the HTTP GET and HTTP POST options indicate the WS provider also offers a representational state transfer (i.e., "REST") option to invoke the service. Invoking REST services is often simpler than SOAP because:

 

  1. 1.Parameters are passed in the URL instead of XML.
  2. 2.You can fiddle with them interactively and see what they do using just about any web browser. For example, just slap this URL into your web browser: http://www.webservicex.net/uszip.asmx/GetInfoByZIP?USZip=49525. You'll see the XML response. (REST services do not always return XML; they can also be plain text, binary, etc.)
  3. 3.An XML result from a REST service is generally easier to parse.

 

So, using these cool new DB2 for i HTTP functions, it's easy to let DB2 get the WS data. Run this SQL statement and watch it pull data from the WS:

 

SELECT data FROM (VALUES(SYSTOOLS.HTTPGETCLOB('http://www.webservicex.net/uszip.asmx/GetInfoByZIP?USZip=49525',''))) WS(data);

 

Here's the XML result (the same thing you'll see if you paste the URL in a browser):

 

<?xml version="1.0" encoding="utf-8"?>

<NewDataSet>

<Table>

      <CITY>Grand Rapids</CITY>

      <STATE>MI</STATE>

      <ZIP>49525</ZIP>

      <AREA_CODE>616</AREA_CODE>

      <TIME_ZONE>E</TIME_ZONE>

</Table>

</NewDataSet>

 

Note: If you're using SQL in the STRSQL green-screen utility, you'll need to CAST the result to VARCHAR (<32K) in order to see the results.

 

The new XMLTABLE table function can be used to dynamically extract the values from the XML result:

 

SELECT *

FROM XMLTABLE('$doc/NewDataSet/Table'

      PASSING

      XMLPARSE(DOCUMENT SYSTOOLS.HTTPGETCLOB(

'http://www.webservicex.net/uszip.asmx/GetInfoByZIP?USZip=49525','')) AS "doc"

COLUMNS

      City VARCHAR(128) PATH 'CITY',

      State VARCHAR(1024) PATH 'STATE',

      Zip VARCHAR(255) PATH 'ZIP',

      AreaCode VARCHAR(255) PATH 'AREA_CODE',

      TimeZone VARCHAR(255) PATH 'TIME_ZONE'

) AS WebServiceResult;

 

Of course, languages like C, RPG, or Java can also be used to parse the results of an XML result.

 

Because you can retrieve cloud data as a scalar or tabular result, you can now connect DB2 for i to the cloud and let data from the cloud participate in queries against your local database: the ultimate in heterogeneous queries!

Downloading File Data from the Web

For the final example, consider HTTPGETBLOB. HTTPGETBLOB has the same parameters as HTTPGETCLOB, and they pretty much work the same; the main difference is that the result returned from the server will remain unaltered. When retrieving binary data or text data that you don't want translated in any fashion (such as ASCII to EBCDIC), use HTTPGETBLOB.

 

Here's a sample RPG program that demonstrates how to download a PDF from the web directly to a file on the IFS in the /tmp folder using HTTGETPBLOB (don't forget to add error handling):

 

D PDF             S               SQLTYPE(BLOB_FILE)

D WebURL         S          255   Varying         

/Free

   WebURL='http://www.lionel.com/pdf/lionelyeb.pdf';  

   PDF_Name='/tmp/Lionel.pdf';

   PDF_NL=%Len(%TrimR(PDF_Name));

   PDF_FO=SQFOVR; // Overwrite

     

   Exec SQL

     SELECT SYSTOOLS.HTTPGETBLOB(:WebURL,'') PDF_DATA  

     INTO :PDF

     FROM SYSIBM.SYSDUMMY1;

 

     *InLR=*On;

/End-Free

 

The HTTPGETBLOB function is used to retrieve a Lionel trains PDF brochure from lionel.com. Once the PDF is returned as a BLOB, it's passed to the special BLOB_FILE SQL data type (RPG variable :PDF), which is configured to automatically dump its content to the specified IFS file.

 

In the past, I've implemented similar functionality to download a PDF from a web server using an RPG sockets program. This new method is trivial in comparison! It goes without saying, you'll normally want to implement a parameterized URL and IFS file name. Once parameterized, this program will allow the download and storage of any valid URL; the equivalent of right-clicking on a hyperlink in a browser and choosing "Save As."

Application Design Considerations

When retrieving data over the web, be careful about performance considerations. Obviously, there is the inherent latency in making a call over the Internet. Moreover, when returning large amounts of data in a table function like XMLTABLE, DB2 for i doesn't always choose the best join order for the tables (and in fact DB2 doesn't have any way to know how many rows will be returned) unless you create a wrapper table function and specify a cardinality.

 

Further, these functions require Java 1.6 to be installed, which means there's overhead when using the Java Virtual Machine (JVM). These functions have been relatively quick on the IBM i 7.1 machines I've been on, but during my many days working with customers on small boxes on V5R3 or V5R4, I have a history of thinking that Java takes too much overhead to use in a large number of interactive jobs. Hopefully, that's changing.

 

As an alternative to using these functions (or if you're not at IBM i 7.1 yet), you can always write your own RPG sockets program to act as an HTTP client. Other free options are IBM's Integrated Web Services for i tool, or Scott Klement's HTTP API library. You can write your high-level language (RPG, C, COBOL, etc.) code in such a way as to allow DB2 to tap into your code via an external function or external stored procedure call. So you can in fact still have the best of both worlds and avoid the Java overhead if needed.

 

Finally, make sure to carefully consider error handling. Whatever is on the web is outside of your control, so the application should know what to do if something isn't working right. Take extra care when using "free" web services because you never know how long they'll be available.

 

All in all, this suite of DB2 functions is spectacular! They allow you to easily exchange data with HTTP-enabled applications whether on the Internet or your intranet. They can be used to retrieve HTML from a web page, contact a web service, consume an RSS feed, push an IFS file to a server, or download a file (image, PDF, text, etc.) from a server. Best of all, since the functions can be run interactively in any SQL environment, testing, prototyping, and development are a snap.

 

Stick around. In the future, I'll demonstrate how to pass HTTP headers in a request, how to examine the response headers, and how to perform basic authentication when accessing data that requires a user name and password.

References


New HTTP functions added to SYSTOOLS

 

This reference is for DB2 LUW, but most of the concepts apply to IBM i. When trying the examples, remember to use the SYSTOOLS schema instead of the DB2XML schema. The examples include consuming an RSS feed and listing unread emails in a GMail inbox.

Accessing HTTP and RESTful services from DB2: Introducing the REST user-defined functions for DB2

 

                                                                                                                       

Accessing web services using IBM DB2 for i HTTP UDFs and UDTFs

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: