News
Abstract
HTTP is the preferred way for communicating in Resource oriented architecture (ROA) and Service oriented architecture (SOA) environments. Use these RESTful services to integrate information sources that can be addressed via a URL and accessed using HTTP.
Content
By default, the HTTP functions use the default SSL protocol of the JVM. If another SSL protocol is needed, the JVM must be configured to use that protocol. The following steps can be used to configure the JVM to use the TLSv1.2 protocol.
1. Create the following Java UDF. This only needs to be done once per system.
CREATE FUNCTION SYSTOOLS.setProperty(property varchar(80), value varchar(80)) returns varchar(80) language java parameter style java external name 'java.lang.System.setProperty'
2. Before a JVM is created in the database job and before running the HTTP function, execute the following SQL statement.
select SYSTOOLS.setproperty('com.ibm.jsse2.overrideDefaultProtocol','TLSv12') from sysibm.sysdummy1
This will set the default protocol to TLSv12.
Any HTTP functions run in the job will now use TLSV12 as the default protocol.
The Db2 for i HTTP functions are:
- Defined in the SYSTOOLS schema (home of Db2 for i supplied tools and examples )
- Ready for use, if you have the enabling Db2 PTF Group level installed
- Fast-Start to building your own applications
- Requires Java 1.6 (5761-JV1)
- Functions visible with IBM Navigator for i
CREATE OR REPLACE FUNCTION QGPL.RFC339_DATE_FORMAT(in_time TIMESTAMP)
RETURNS VARCHAR(26)
LANGUAGE SQL
RETURN CAST(DATE(in_time) AS CHAR(10)) || 'T' || CHAR(TIME(in_time), JIS)
Step 2: Use XML features on DB2 for i to query the blog content and return the blog posts for the last 6 months. (order the rows by reader responses)
-- Blog Posts for the last 6 months, order by reader responses
SELECT published, updated, author, title, responses, url, author_bio, html_content, url_atom
FROM
XMLTABLE(
XMLNAMESPACES(DEFAULT 'http://www.w3.org/2005/Atom',
'http://purl.org/syndication/thread/1.0' AS "thr"),
'feed/entry'
PASSING XMLPARSE(DOCUMENT
SYSTOOLS.HTTPGETBLOB(
-- URL --
'http://db2fori.blogspot.com/feeds/posts/default?published-min=' ||
SYSTOOLS.URLENCODE(QGPL.RFC339_DATE_FORMAT(CURRENT_TIMESTAMP - 6 MONTHS), 'UTF-8') ||
'&published-max=' || SYSTOOLS.URLENCODE(QGPL.RFC339_DATE_FORMAT(CURRENT_TIMESTAMP + 1 DAYS) , 'UTF-8') ,
-- header --
'<httpHeader> <header name="Accept" value="application/atom+xml"/> </httpHeader>'
) )
COLUMNS
published TIMESTAMP PATH 'published',
updated TIMESTAMP PATH 'updated',
author VARCHAR(15) CCSID 1208 PATH 'author/name',
title VARCHAR(100) CCSID 1208 PATH 'link[@rel="alternate" and @type="text/html"]/@title',
responses INTEGER PATH 'thr:total',
author_bio VARCHAR(4096) CCSID 1208 PATH 'author/uri',
url VARCHAR(4096) CCSID 1208 PATH 'link[@rel="alternate" and @type="text/html"]/@href',
url_atom VARCHAR(4096) CCSID 1208 PATH 'link[@rel="self" and @type="application/atom+xml"]/@href',
html_content CLOB CCSID 1208 PATH 'content[@type="html"]'
) RS
ORDER BY RESPONSES DESC
Step 3: Examine the output
- IBM i Knowledge center documentation: HTTP function overview
- Db2 for LUW and DB2 for z/OS support is described here: Accessing HTTP and RESTful services from Db2: Introducing the REST user-defined functions for DB2
Was this topic helpful?
Document Information
Modified date:
14 January 2020
UID
ibm11167328