Start of change

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: Start of changeThe 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.End of change
Read syntax diagramSkip visual syntax diagramHTTPGETBLOBHTTPGETCLOB( url,httpHeader)

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:
Start of changeconnectTimeoutEnd of change
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.
Start of changeresponseMsgFormatEnd of change
Start of changeSpecifies 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>
End of change
headerAttributeValue
Specifies a value for the headerAttribute. Separate headerAttribute and headerAttributeValue combinations with single spaces.
name
The header name.
value
The header value.
Start of change

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's 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 an IBM developer community. You can use this statement to verify your configuration.

SELECT DB2XML.HTTPGETCLOB(
   CAST ('https://www.ibm.com/developerworks/community/blogs/e429a8a2-b27f-48f3-aa73-ca13d5b69759/entry/Pattern' AS VARCHAR(255)),
   CAST(NULL AS CLOB(1K)))
 FROM SYSIBM.SYSDUMMY1

The results will contain output that's similar to this:

<!doctype html>
<html lang="en-US">
<head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <link rel="profile" href="http://gmpg.org/xfn/11">
        <title>Community &#8211; IBM Developer</title>
<link rel='dns-prefetch' href='//unpkg.com' />
<link rel='dns-prefetch' href='//1.www.s81c.com' />
<link rel='dns-prefetch' href='//s.w.org' />
<link rel="alternate" type="application/rss+xml" title="IBM Developer &raquo; Feed" href="https://developer.ibm.com/fee
  1 record(s) selected

Example 2. The following SQL statement retrieves information, 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 results will contain output that's similar to this:

{
 "kind": "books#volumes",
 "totalItems": 1,
 "items": [
  {
   "kind": "books#volume",
   "id": "7qGQvgAACAAJ",
   "etag": "IfzEprGDfNA",
   "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",
    "de
  1 record(s) selected

Example 3. The following SQL statement retrieves information from the British National Library, and the results are returned in XML:

SELECT DB2XML.HTTPGETCLOB(
   CAST ('http://bnb.data.bl.uk/doc/resource/007446989.xml' AS VARCHAR(255)),
   CAST(NULL AS CLOB(1K)))
 FROM SYSIBM.SYSDUMMY1

This statement returns results that are similar to this:

<?xml version="1.0" encoding="utf-8"?>
<result format="linked-data-api" version="0.2" href="http://bnb.data.bl.uk/doc/resource/007446989.xml">
<primaryTopic href="http://bnb.data.bl.uk/id/resource/007446989"><sameAs href="http://bnb.data.bl.uk/id/resource/GB9212849"/>
<label>On parade / Leonard Sanitt</label><type><item href="http://purl.org/dc/terms/BibliographicResource"/>
<item href="http://purl.org/ontology/bibo/Book"/></type><publication href="http://bnb.data.bl.uk/id/resource/007446989/public
  1 record(s) selected

Example 4. When you query a server that requires authentication, you can use the following syntax to specify a user ID and password:

WITH Authorization AS (
  SELECT DB2XML.BASE64ENCODE(CAST('<userid>:<password>' AS   VARCHAR(64) CCSID 1208)) AS Authorization_Identity
FROM SYSIBM.SYSDUMMY1
)
SELECT DB2XML.HTTPGETCLOB(                           
  CAST ('https://gateway.watsonplatform.net/tone-analyzer/api/v3/tone?version=2016-05-19&text=' || DB2XML.URLENCODE('some text here','') AS VARCHAR(255)),
  CAST('<httpHeader><header name="Authorization" value="Basic '||Authorization_Identity||'" /></httpHeader>' AS CLOB(1K)))
FROM Authorization
End of change
End of change