Start of change

Creating REST user-defined functions

A set of sample user-defined functions can be installed with Db2 to allow applications to access REST-based services through SQL. The basic REST scalar functions provide access to web-hosted content. Additional helper functions are available to provide URL encoding and decoding and Base64 encoding and decoding

Before you begin

Ensure that you have the following prerequisites before you create the sample REST user-defined functions:

  • Java™ JDK 5.0 or later

    The REST user-defined functions are implemented in Java and require this minimum JDK level.

  • TCP/IP

    The REST user-defined functions require a functioning TCP/IP setup and access to the Internet.

  • A Java WLM environment

    You can use the WLM environment that is supplied with Db2 for Java routines, DSNWLM_JAVA, or any existing Java WLM environment. By default, the jar file for the Db2 REST functions, db2restudf.jar, is installed in the same class path as other Java routines that are supplied with Db2: /usr/lpp/db2b10/base. If you installed db2restudf.jar in a different path, verify that this path is specified in the Language Environment® variable file that is used by the WLM environment.

About this task

This task describes how to create the basic REST user-defined functions that are provided with Db2. It also describes how to optionally set up the REST user-defined functions to use SSL and how to enable HTTP proxy support for the REST user-defined functions.

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.

Procedure

To create and configure connections for the sample REST user-defined functions:

  1. Modify job DSNTIJRF or the createUDFzOS.sql script in the db2restudf.jar file to specify the name of the WLM environment that you set up for the REST user-defined functions.
  2. Run job DSNTIJRF or the createUDFzOS.sql script.
  3. Optional: Enable SSL connections to use a key database or to use a RACF keyring.
    • To enable SSL connections to use a key database, specify the following settings in the file that you specified for JVMPROPS in the Language Environment options data set:
      • -Djavax.net.ssl.keyStore=/wlmjvm/keystore.jks
      • -Djavax.net.ssl.trustStore=/wlmjvm/keystore.jks
      • -Djavax.net.ssl.trustStorePassword=password
      • -Djavax.net.ssl.keyStorePassword=password
    • To enable SSL connections to use a RACF key ring, follow these steps:
      1. Create a key ring for the RACF user ID that is associated with the stored procedure address space in which the REST user-defined functions run.

        To create a key ring named RESTKEYRING for user ID RESTUSR, issue this command:

        RACDCERT ADDRING(RESTKEYRING) ID(RESTUSR)
      2. Add certificates to the key ring.

        To add a certificate to RESTKEYRING, issue this command:

        RACDCERT ID(RESTUSR) CONNECT(ID(RESTUSR) LABEL('RESTCERT') RING(RESTKEYRING))
      3. In the file that you specified for JVMPROPS in the Language Environment options data set, specify the following settings:
        Property Value
        javax.net.ssl.trustStore Path to the key ring, in the format 
        safkeyring://racf-id/ring-id

        racf-id is the RACF user ID that is associated with the stored procedure address space. ring-id is the name of the RACF key ring.
        java.protocol.handler.pkgs com.ibm.crypto.provider
        javax.net.ssl.trustStoreType JCERACFKS

        To set up SSL connections that use the RESTKEYRING key ring, set these properties:

        • -Djavax.net.ssl.trustStore=safkeyring://RESTUSR/RESTKEYRING
        • -Djava.protocol.handler.pkgs=com.ibm.crypto.provider
        • -Djavax.net.ssl.trustStoreType=JCERACFKS
  4. Optional: Enable HTTP proxy support.

    Set the following properties in the file that you specified for JVMPROPS in the Language Environment options data set:

    • http.proxyHost
    • http.proxyPort
    • http.proxyUser
    • http.proxyPassword
    • http.nonProxyHosts

    In this properties file, specify -Dproperty=value for each configuration property.

    For example, the following settings can be used:
    • -Dhttp.proxyHost=myProxyHost.com
    • -Dhttp.proxyPort=8090
    • -Dhttp.proxyUser=user
    • -Dhttp.proxyPassword=secret
    • -Dhttp.nonProxyHosts=*.ibm.com|wikipedia.org|...
End of change