At your service: REST with DB2 pureXML!

September 29, 2011

Undeniably, XML is the message format of choice for many service-oriented architectures and application integration efforts. Also, many SOA and web service implementaions use REST as the protocol for accessing URL-addressable resources and services. REST stands for Representational State Transfer and is built on top of HTTP, which acts as the underlying transport layer.

With a new set user-defined functions (UDFs) in DB2, it has become very simple to issue REST requests directly from your SQL statements or DB2 stored procedures. This enables DB2 to easily interact with REST-based services and integrate information from the web or URL-based resources into the database.

The new REST UDFs allow you to receive and provide information in binary format (BLOB) or textual format (CLOB), which includes but is not limited to XML. The basic REST UDFs are scalar functions that perform the simple HTTP operations GET, POST, PUT, and DELETE. These UDFs are:


Each of these functions take a URL and, optionally, an HTTP header as input paramaters. Additionally, the POST and PUT functions have a third parameter for the BLOB or CLOB data that you want to upload.

The REST UDFs are implemented in Java and support HTTP as well as HTTPS with SSL encryption.

When you use these UDFs in SQL statements to receive information, you can:

  • insert the information into DB2 tables
  • use the information as a parameter to search for related data in DB2
  • join the information with existing data in DB2
  • process the information in a stored procedure
  • or use the information in any other way that SQL provides

In particular, if the information is in XML format you can apply any DB2 pureXML functions to it, such as:

  • the XMLQUERY function to extract pieces from the XML message
  • the XMLTABLE function to split the XML into smaller XML pieces
  • the XMLTABLE function to shred the XML into relational tables
  • the XMLVALIDATE function to validate the XML against an XML Schema
  • etc.

If you are interested and want to try this for yourself, read the following article:
“Accessing HTTP and RESTful services from DB2: Introducing the REST user-defined functions for DB2”

This article describes the REST functions for DB2 in more detail and presents several concrete usage examples. The UDFs themselves are available for download at the bottom of this article.

Enjoy your REST !


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: