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.
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
- 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 – 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 » 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


