Start of change

HTTP function overview

The HTTP functions in SYSTOOLS are used to make HTTP requests to web services. These functions allow the SQL programmer to use Representational State Transfer (RESTful) via SQL, including Embedded SQL.

An alternate version of the HTTP functions are shipped in QSYS2. These are implemented using the HTTP Transport support provided by the AXISC APIs instead of Java™. See HTTP_DELETE, HTTP_GET, HTTP_POST, HTTP_PUT, HTTP_DELETE_VERBOSE, HTTP_GET_VERBOSE, HTTP_POST_VERBOSE, HTTP_PUT_VERBOSE

Environmental considerations

Because the HTTP functions use functionality provided by Java, a Java environment is created in the current job. This requires the following conditions.
  1. A JVM must not already exist in the job (with the exception of a JVM created by the Java stored procedures support).
  2. The job CCSID cannot be 65535.
  3. PASE must be installed and operational. The CHKPRDOPT PRDID(5770SS1) OPTION(33) CL command can be used to verify that PASE is installed.

Foundational HTTP functions

The foundational functions are named according to the three dimensions used when making HTTP requests.
  • The first dimension is the HTTP operation. There are 4 different HTTP operations: GET, PUT, POST, and DELETE.
  • The second dimension is the data type used for returning data. There are two alternatives: BLOB and CLOB.
  • The third dimension is if the verbose version of the function should be used. Non-verbose functions are scalar functions. The VERBOSE functions are table functions that return the return header information that is sent from the HTTP server.

The names of the functions reflect these three dimensions. For example, the HTTPGETCLOBVERBOSE uses the GET operation from the first dimension, the CLOB data type from the second dimension, and the VERBOSE setting from the third dimension.

The following table summarizes the 16 functions that are named according to the three dimensions.
Table 1. Foundational HTTP functions

make HTTP requests

Function
Description Documentation link
HTTPDELETEBLOB Makes an HTTP DELETE request and return BLOB data. HTTPDELETEBLOB and HTTPDELETECLOB scalar functions
HTTPDELETEBLOBVERBOSE Makes an HTTP DELETE request and return BLOB data and response header. HTTPDELETEBLOBVERBOSE and HTTPDELETECLOBVERBOSE table functions
HTTPDELETECLOB Makes an HTTP DELETE request and return CLOB data. HTTPDELETEBLOB and HTTPDELETECLOB scalar functions
HTTPDELETECLOBVERBOSE Makes an HTTP DELETE request and return CLOB data and response header. HTTPDELETEBLOBVERBOSE and HTTPDELETECLOBVERBOSE table functions
HTTPGETBLOB Makes an HTTP GET request and return BLOB data. HTTPGETBLOB and HTTPGETCLOB scalar functions
HTTPGETBLOBVERBOSE Makes an HTTP GET request and return BLOB data and response header. HTTPGETBLOBVERBOSE and HTTPGETCLOBVERBOSE table functions
HTTPGETCLOB Makes an HTTP GET request and return CLOB data. HTTPGETBLOB and HTTPGETCLOB scalar functions
HTTPGETCLOBVERBOSE Makes an HTTP GET request and return CLOB data and response header. HTTPGETBLOBVERBOSE and HTTPGETCLOBVERBOSE table functions
HTTPPOSTBLOB Makes an HTTP POST request and return BLOB data. HTTPPOSTBLOB and HTTPPOSTCLOB scalar functions
HTTPPOSTBLOBVERBOSE Makes an HTTP POST request and return BLOB data and response header. HTTPPOSTBLOBVERBOSE and HTTPPOSTCLOBVERBOSE table functions
HTTPPOSTCLOB Makes an HTTP POST request and return CLOB data. HTTPPOSTBLOB and HTTPPOSTCLOB scalar functions
HTTPPOSTCLOBVERBOSE Makes an HTTP POST request and return CLOB data and response header. HTTPPOSTBLOBVERBOSE and HTTPPOSTCLOBVERBOSE table functions
HTTPPUTBLOB Makes an HTTP PUT request and return BLOB data. HTTPPUTBLOB and HTTPPUTCLOB scalar functions
HTTPPUTBLOBVERBOSE Makes an HTTP PUT request and return BLOB data and response header. HTTPPUTBLOBVERBOSE and HTTPPUTCLOBVERBOSE table functions
HTTPPUTCLOB Makes an HTTP PUT request and return CLOB data. HTTPPUTBLOB and HTTPPUTCLOB scalar functions
HTTPPUTCLOBVERBOSE Makes an HTTP PUT request and return CLOB data and response header. HTTPPUTBLOBVERBOSE and HTTPPUTCLOBVERBOSE table functions

These HTTP functions are passed parameters that indicate the HTTP server to access, the settings of the HTTP headers, and any data to be sent to the server. The first two parameters are the same for each HTTP function.

  • The first parameter is the URL used to access the server.
  • The second parameter is an XML document which indicates the HTTP header that is to be presented to the server by the request. This XML document can be provided as either a CLOB or XML value. The HTTP header XML document has the following format:
    <httpHeader headerAttribute="headerAttributeValue">
       <header name="name" value="value" />
       <header name="Accept" value="text/plain,application/xml,*/*" />
    </httpHeader>

    The XML header document consists of an httpHeader element with various attributes, described below. The httpHeader element then contains header elements that are used to set additional HTTP headers. This is done by specifying the name of the header and the value that corresponds to the name.

    The following attributes can be set in the httpHeader element.
    Table 2. httpHeader attributes
    Attribute name Type Default Description
    connectionTimeout Integer SystemDefault Timeout in milliseconds
    readTimeout Integer SystemDefault Timeout in milliseconds
    followRedirects Boolean true If true, then redirects are followed. Redirects will only be followed if the protocol does not change. For example, a redirect from "http:" to "https:" will not be followed.
    useCaches Boolean true If true, then caches are used.
    includeErrorMsg Boolean false If true, then HTTP verbose functions will return error information in the RESPONSEMSG column.

    The header elements allow various HTTP headers to be set. The following header names are case insensitive: "Content-Type", "Content-Length", "Content-Encoding","Accept-Encoding","Authorization","User-Agent". The remaining headers are sent as specified. To correctly use these headers, consult the appropriate Web server and RFC documentation on the use of HTTP request headers. Examples of RFCs are https://tools.ietf.org/html/rfc2616 and https://tools.ietf.org/html/rfc7231.

    For example, using the header
    <httpHeader><header name="User-Agent" value="IBM i HTTP function"/></httpHeader>
    will cause "User-Agent": "IBM i HTTP function" to be included in the HTTP header sent to the server.

Generic HTTP functions

The HTTPBLOB and HTTPCLOB functions can be used to perform any of the non-verbose HTTP functions by passing the operation to perform as an argument: GET, PUT, POST, or DELETE.
Table 3. HTTP generic functions
Function Description Documentation link
HTTPBLOB Makes an HTTP request and return BLOB data. HTTPBLOB and HTTPCLOB scalar functions
HTTPCLOB Makes an HTTP request and return CLOB data. HTTPBLOB and HTTPCLOB scalar functions

Utility HTTP functions

Some additional HTTP functions are utility functions for working with HTTP requests.
Table 4. HTTP utility functions
Function Description Documentation link
BASE64DECODE Returns a bit data string that has been Base64 decoded. BASE64DECODE scalar function
BASE64ENCODE Returns the Base64 encoded version of a character string. BASE64ENCODE scalar function
HTTPHEAD Verifies the HTTP header using an HTTP HEAD request. HTTPHEAD scalar function
URLDECODE Decodes a URL encoded string. URLDECODE scalar function
URLENCODE Encodes a string using URL encoding. URLENCODE scalar function

Common errors

When an error occurs, to get additional information about the error consider using the VERBOSE form of the function with includeErrorMsg="true". This will return error information in the RESPONSEMSG column. For example:
select * from table(systools.httpgetclobverbose('http://www.w3.org/notfound.html',
                                                '<httpHeader includeErrorMsg="true"/>')) 
The following are some common errors that may be encountered when using the HTTP functions.
  • If the current system does not have internet access, the following error may be encountered.

    [SQL4302] Java stored procedure or user-defined function SYSTOOLS.HTTPGETCLOB, specific name HTTPG00005 aborted with an exception "java.net.ConnectException:A remote host refused an attempted connect operation. (Connection refused)".

  • If DNS is not configured on the system, the following error may be encountered.

    [SQL4302] Java stored procedure or user-defined function SYSTOOLS.HTTPGETCLOB, specific name HTTPG00005 aborted with an exception "java.net.UnknownHostException"

  • If the Job CCSID is 65535, the following error may be encountered.

    [SQL0332] Character conversion between CCSID 65535 and CCSID 1200 not valid.

  • If https is being used, then the connection uses SSL. In order to work properly, the server must use a certificated signed by a recognized certificate authority. Using a self signed certificate may result in the following error.

    [SQL4302] Java stored procedure or user-defined function SYSTOOLS.HTTPGETCLOB, specific name HTTPG00005 aborted with an exception "com.ibm.jsse2.util.h: PKIX path building failed: java.security.cert.CertPathBuilderException: PKIXCertPathBuilderImpl could not build a valid CertPath.

    If you must access a server that uses a self signed certificate, you must add a certificate to the trust store of the JVM being used. If the current JVM is the 32-bit JDK 6, then the following command may be used in QSH after downloading the certificate from the server. The trust store of the JVM may be refreshed when Java PTFs are applied, causing the loss of local changes. This step needs to be repeated after applying Java PTFs that refresh the trust store.
    keytool -import -trustcacerts 
      -keystore /QOpenSys/QIBM/ProdData/JavaVM/jdk60/32bit/jre/lib/security/cacerts  
      -storepass changeit -noprompt -alias myhost -file myhost.crt
  • Some websites require the use of specific SSL versions. In that case, an error like the following may be encountered.

    [SQL4302] Java stored procedure or user-defined function SYSTOOLS.HTTPGETCLOBVERBOSE, specific name HTTPG00007 aborted with an exception "Received fatal alert: handshake_failure".

    To force the use of a specific SSL version, the following may be done.
    1. Create a user defined function to set a JVM property. 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 calling the HTTP function in a job, use the following to set the default SSL protocol.
      VALUES SYSTOOLS.setproperty('com.ibm.jsse2.overrideDefaultProtocol','TLSv12')

Debugging considerations

Using STRSQL to invoke HTTP functions will not provide useful problem information. Instead, you should use ACS Run SQL Scripts to execute the HTTP functions in order to see error information.

Because the HTTP functions utilize the networking functions provided by Java, any networking problem detected by the Java code may not be exposed to the SQL level with enough detail to diagnose the problem. If the HTTP functions fail with an SQL4302 error and the information is not useful, a Java stored procedure trace may be used to see more information about the exception.

A Java stored procedure trace may be executed and viewed by using the following SQL statements.
  • Enable the trace.
    CALL QSYS2.QCMDEXC('ADDENVVAR ENVVAR(QIBM_COMPONENT_TRACE_LEVEL) 
                                 VALUE(''SQJAVA,VERBOSE'')')
    
  • Execute the HTTP function.
  • Dump the trace to a file in QTEMP.
    CALL QSYS2.QCMDEXC('DMPUSRTRC')
  • Copy the trace to the current library.
    CREATE TABLE MYTRACE AS 
      (SELECT TRIM(CAST(QAP0ZDMP AS VARCHAR(200) CCSID 37)) LINE FROM QTEMP.QAP0ZDMP ) 
      WITH DATA
  • View the Java exception in the trace.
    SELECT * FROM MYTRACE
       WHERE LINE LIKE '% at %' OR LINE LIKE '%Except%' 
End of change