HTTPGETBLOB and HTTPGETCLOB

The HTTPGETBLOB and HTTPGETCLOB REST functions retrieve a binary or text-based resource from the specified URL through an HTTP GET request. HTTPGETBLOB returns the resource as BLOB(5M) data. HTTPGETCLOB returns the resource as CLOB(5M) data. The character set is converted into the database code page if necessary.

Tip: The sample HTTP user-defined functions are intended to be used within Db2 SQL applications to access remote non-Db2 REST-based services through SQL statements. Do not confuse them with Db2 native REST services, which supports using a REST-based interface to interact with Db2 data from web, mobile, and cloud applications.

The schema is DB2XML.

url

Specifies the URL of the resource being accessed. This argument is defined as a VARCHAR(2048) value.

httpHeader

Specifies an optional header XML document. This argument is defined as a CLOB(10K) value.

The XML header document can provide additional HTTP header values in the following format:

<httpHeader headerAttribute="headerAttributeValue">
        <header name="name" value="value" />
</httpHeader>

headerAttribute

Specify any of the following optional attributes:

connectTimeout

Specifies an integer value for the connection timeout threshold in milliseconds.

readTimeout

Specifies an integer value for the read timeout threshold in milliseconds.

followRedirects

Specifies whether redirects should be followed. This is a boolean value.

useCaches

Specifies whether caches should be used. This is a boolean value.

responseMsgFormat

Specifies the header attribute value errorTagged, to indicate that when the server returns an error message to the user-defined function, the user-defined function returns the following values to the caller:

SQLCODE 0

The error message from the server, in the following format:

<error httprc="{HTTP-status-code}">
  {error-message-returned-from-server}
</error>

headerAttributeValue

Specifies a value for the headerAttribute. Separate headerAttribute and headerAttributeValue combinations with single spaces.

name

The header name.

value

The header value.

Examples

The examples in this section assume that the following requirements have been met:

db2restudf.jar is defined in your CLASSPATH.
The REST user-defined function (UDF) has been created.
The server that is specified in the SQL statement is running.

In most cases, the results that are shown are truncated to include only the relevant parts of the output.

Example 1. The following SQL statement retrieves data from the www.ibm.com web page.

SELECT DB2XML.HTTPGETCLOB(                       
   CAST ('https://www.ibm.com' AS VARCHAR(2048)),
   CAST(NULL AS CLOB(1K)))                       
 FROM SYSIBM.SYSDUMMY1

The output is similar to the following:

<!doctype html>
<html lang="en-US">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width"/>
  <meta charSet="utf-8"/><title>IBM - United States</title>
  <link rel="canonical" href="https://www.ibm.com/us-en"/>
  <meta name="robots" content="index,follow"/>
  <meta name="description" content="For more than a century IBM has been dedicated ..."/>
  <meta name="keywords" content="IBM"/>
...
</html>

Example 2. The following SQL statement retrieves information that is in JSON format, about a book based on its ISBN identifier:

SELECT DB2XML.HTTPGETCLOB(
   CAST ('https://www.googleapis.com/books/v1/volumes?q=isbn:9781583478608' AS VARCHAR(255)),
   CAST(NULL AS CLOB(1K)))
 FROM SYSIBM.SYSDUMMY1

The output is similar to the following:

{
 "kind": "books#volumes",
 "totalItems": 1,
 "items": [
  {
   "kind": "books#volume",
   "id": "7qGQvgAACAAJ",
   "etag": "qov9fok2EyI",
   "selfLink": "https://www.googleapis.com/books/v1/volumes/7qGQvgAACAAJ",
   "volumeInfo": {
    "title": "DB2 12 for Z/OS--The #1 Enterprise Database",
    "subtitle": "SECURE, SEAMLESS INTEGRATION for an Analytics, Mobile and Cloud World",
    "authors": [
     "Surekha Parekh"
    ],
    "publisher": "MC Press",
    "publishedDate": "2016-11-01",
    ...
}

Example 3. The following SQL statement retrieves information from the www.geonames.org web site. The results are in XML format.

SELECT DB2XML.HTTPGETCLOB(                                      
         CAST('http://www.geonames.org/countryInfo?lang=' ||
          DB2XML.URLENCODE('en','') ||                      
          '&country=' ||                                    
          DB2XML.URLENCODE('us','') ||                      
          '&type=XML' AS VARCHAR(255)),                         
                CAST(NULL AS CLOB(1K)))                         
FROM SYSIBM.SYSDUMMY1

This statement returns output that is similar to the following:


<?xml version="1.0" encoding="UTF-8" standalone="no"?>
 <geonames>
  <country>
    <countryCode>US</countryCode>
    <countryName>United States</countryName>
    <isoNumeric>840</isoNumeric>
    <isoAlpha3>USA</isoAlpha3>
    <fipsCode>US</fipsCode>
    <continentName>North America</continentName>
    <capital>Washington</capital>
    <areaInSqKm>9629091.0</areaInSqKm>
    <population>327167434</population>
    <currencyCode>USD</currencyCode>
    <languages>en-US,es-US,haw,fr</languages>
    <geonameId>6252001</geonameId>
    <west>-124.733692</west>
    <north>49.384358</north>
    <east>-66.949607</east>
    <south>24.544093</south>
    <postalCodeFormat>#####-####</postalCodeFormat>
  </country>
 </geonames>