Start of change

HTTP functions overview

These HTTP functions are used to make HTTP requests that use web services. These functions allow the SQL programmer to use Representational State Transfer (RESTful) via SQL, including Embedded SQL. They provide the same capabilities as the SYSTOOLS HTTP functions without the overhead of creating a JVM. The functions use the HTTP Transport APIs that are part of the Integrated Web Services (IWS) client for ILE support.

To use the HTTP functions, 5770SS1 option 3 (Extended Base Directory Support) and 5770SS1 option 34 (Digital Certificate Manager) must be installed on the system.

These HTTP functions exists in QSYS2 and have lower overhead than the SYSTOOLS HTTP functions. Additional benefits of the QSYS2 HTTP functions are HTTP authentication, proxy support, configurable redirection attempts, and configurable SSL options.

The URL parameter supports http: and https: URLs. The https: URL indicates that network communication should take place over a secure communication channel. An https request uses TLS (Transport Layer Security) to create the secure channel. This secure channel encrypts any transmitted data and also prevents man-in-the-middle attacks. Any communication that contains secure information should use https instead of http. Because of the sensitive nature of userids and passwords, HTTP authentication is not allowed for http URLs.

The HTTP services are implemented using the QSYS/QSQAXISC service program. The system administrator can disable access to the HTTP services by modifying authority on the QSYS/QSQAXISC service program. The authority associated with the QSYS/QSQAXISC service program applies to all the HTTP functions.

Foundational HTTP functions

The foundational functions are named according to the two 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 indicates whether the verbose version of the function should be used. The non-verbose functions are scalar functions that return the response as a CLOB. The verbose functions are table functions that return a single row, which includes the return header information that is sent from the HTTP server. The header information is formatted as JSON.

The names of the functions reflect these dimensions. For example, HTTP_GET_VERBOSE uses the GET operation from the first dimension and the VERBOSE setting from the second dimension. All the functions return CLOB data.

The following table summarizes the functions.
Table 1. Foundational HTTP functions
Function Description Documentation link
HTTP_DELETE Make an HTTP DELETE request and return CLOB data. HTTP_DELETE scalar function
HTTP_DELETE_VERBOSE Make an HTTP DELETE request and return CLOB data and response header. HTTP_DELETE_VERBOSE table function
HTTP_GET Make an HTTP GET request and return CLOB data. HTTP_GET scalar function
HTTP_GET_VERBOSE Make an HTTP GET request and return CLOB data and response header. HTTP_GET_VERBOSE table function
HTTP_POST Make an HTTP POST request and return CLOB data. HTTP_POST scalar function
HTTP_POST_VERBOSE Make an HTTP POST request and return CLOB data and response header. HTTP_POST_VERBOSE table function
HTTP_PUT Make an HTTP PUT request and return CLOB data. HTTP_PUT scalar function
HTTP_PUT_VERBOSE Make an HTTP PUT request and return CLOB data and response header. HTTP_PUT_VERBOSE table function
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 parameters are the same for each HTTP function.
  • The first parameter is the URL used to access the server.
  • The second parameter is a string which indicates the options to be used on the request. These options include the setting of the HTTP headers. The string is a JSON object with the following format:
    {"option":"option-setting","option":"option-setting"}

The options are described here: HTTP options.

Utility HTTP functions

Some additional HTTP functions are utility functions for working with HTTP requests.
Table 2. HTTP utility functions
Function Description Documentation link
BASE64_DECODE Returns a bit data string that has been Base64 decoded. BASE64_DECODE scalar function
BASE64_ENCODE Returns the Base64 encoded version of a character string. BASE64_ENCODE scalar function
URL_DECODE Decodes a URL encoded string. URL_DECODE scalar function
URL_ENCODE Encodes a string using URL encoding. URL_ENCODE scalar function

SSL considerations

By default, the HTTP functions use the system default certificate store: /QIBM/USERDATA/ICSS/CERT/SERVER/DEFAULT.KDB. By default, this certificate store does not exist. The Digital Certificate Manager (DCM) can be used to create this certificate store and add certificates to this certificate store.

If your application chooses to trust the certificates used by the JVM, the following SQL statements can be used to create a certificate store (/home/javaTrustStore/fromJava.KDB) that can be used by the HTTP functions. This process uses the /QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/lib/security/cacerts java trust store included when the 64-bit Java 8 is installed. This script may be changed to use a more recent Java trust store. The script also uses Qshell, which requires 5770SS1 Option 30.

In this example changeit is the default password for the cacerts file shipped with Java. A temporary password of xyz789 is used during the setup, which is immediately replaced by the final password. abc123 is the final password for the newly created certificate store.

This procedures requires that the user is authorized to create the /home/javaTrustStore directory and has permission to read the cacerts file.
-- The following SQL script generates a KDB trust store from a Java trust  
-- store using an intermediate JKS trust store.  The IFS directory and  
-- name of the KDB trust store are set in the NEW_TRUST_DIRECTORY and 
-- NEW_TRUST_STORE variables below. 
--  
-- Create an SQL schema to contain temporary variables. 
-- The user must have permission to create a schema.  
-- 
CREATE SCHEMA FROM_JAVA_TRUST_STORE;
SET SCHEMA FROM_JAVA_TRUST_STORE;
SET PATH CURRENT PATH, FROM_JAVA_TRUST_STORE;

--
-- Define global variables for parameters to the procedure for generating new trust store. 
-- 

-- Specify the IFS directory to use for the new trust store.
-- In this example, we use /home/javaTrustStore
-- The user must have *W authority to /home in order to create this  
-- directory.  
CREATE OR REPLACE VARIABLE   NEW_TRUST_DIRECTORY VARCHAR(80) CCSID 37;
SET NEW_TRUST_DIRECTORY='/home/javaTrustStore'; 

-- Specify the name of the new trust store name
-- In this example, the name is fromJava.KDB
CREATE OR REPLACE VARIABLE   NEW_TRUST_STORE VARCHAR(80) CCSID 37;
SET NEW_TRUST_STORE = NEW_TRUST_DIRECTORY CONCAT '/fromJava.KDB'; 

-- Specify the password for the trust store.  This
-- should be changed to keep the new trust store secure. 
CREATE OR REPLACE VARIABLE   NEW_TRUST_STORE_PASSWORD VARCHAR(80) CCSID 37;
SET NEW_TRUST_STORE_PASSWORD= 'abc123';

-- Specify the Java trust store to use. 
CREATE OR REPLACE VARIABLE   JAVA_TRUST_STORE VARCHAR(80);
SET JAVA_TRUST_STORE='/QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/lib/security/cacerts';

-- Specify the Java trust store password.  The default password is changeit.
-- If the password has been changed on the system, the correct value will need to be used. 
CREATE OR REPLACE VARIABLE   JAVA_TRUST_STORE_PASSWORD VARCHAR(80) CCSID 37; 
SET JAVA_TRUST_STORE_PASSWORD = 'changeit';

-- Specify the name of a temporary JKS format TRUST STORE.  
-- In this example, jksExport is used.
CREATE OR REPLACE VARIABLE JKS_TRUST_STORE VARCHAR(80) CCSID 37; 
SET JKS_TRUST_STORE = NEW_TRUST_DIRECTORY CONCAT 'jksExport';

-- Specify the password of the temporary JKS format TRUST STORE. 
-- In this example, xyz789 is used as the password.
CREATE OR REPLACE VARIABLE JKS_TRUST_STORE_PASSWORD VARCHAR(80); 
SET JKS_TRUST_STORE_PASSWORD = 'xyz789' ;


-- Step 1.  Use QCMDEXC and QSH and mkdir to create a directory in which 
--          to save the new store file
CALL QSYS2.QCMDEXC( 'QSH CMD(''mkdir ' CONCAT NEW_TRUST_DIRECTORY CONCAT ''')');


-- Step 2.  Use QCMDEXC and QSH to run the keytool command to export 
--          the default java certificate store in PKCS12 format
CALL QSYS2.QCMDEXC(
   'QSH CMD(''keytool -importkeystore ' CONCAT
   ' -srcstorepass ' CONCAT JAVA_TRUST_STORE_PASSWORD CONCAT
   ' -srckeystore ' CONCAT JAVA_TRUST_STORE CONCAT
   ' -destkeystore ' CONCAT JKS_TRUST_STORE CONCAT
   ' -srcstoretype JKS -deststoretype PKCS12 ' CONCAT 
   ' -deststorepass ' CONCAT JKS_TRUST_STORE_PASSWORD CONCAT ''')');


-- Step 3.  Create an SQL procedure that will call the QSYS/QYMKIMPK API to 
--          create a keystore from the PKCS12 keystore.  A SQL7909 warning from
--          this step can be ignored. 
CREATE OR REPLACE PROCEDURE ImportKeyStore(
     STOREPATH CHAR(100) CCSID 37, 
     STOREPATHLEN INT,  
     STOREFORMAT CHAR(9) CCSID 37, 
     STOREPASSWORD CHAR(100) CCSID 37,
     STOREPASSWORDLEN INT,   
     STOREPASSWORDCCSID INT,    
     IMPORTPATH CHAR(100) CCSID 37,     
     IMPORTPATHLEN INT,     
     IMPORTFORMAT CHAR(9) CCSID 37,  
     IMPORTVERSION CHAR(11) CCSID 37,   
     IMPORTPASSWORD CHAR(100) CCSID 37,   
     IMPORTPASSWORDLEN INT,     
     IMPORTPASSWORDCCSID INT,     
     ERRORCODE CHAR(100) FOR BIT DATA) 
    LANGUAGE C PARAMETER STYLE GENERAL EXTERNAL NAME 'QSYS/QYKMIMPK';

-- Step 4.  Call the SQL procedure to call the QSYS/QYKMIMPK API to 
--          create the new keystore to be used. 
CALL IMPORTKEYSTORE(
  STOREPATH => NEW_TRUST_STORE, 
  STOREPATHLEN => LENGTH(NEW_TRUST_STORE), 
  STOREFORMAT => 'OBJN0100',	            
  STOREPASSWORD => NEW_TRUST_STORE_PASSWORD, 
  STOREPASSWORDLEN => LENGTH(NEW_TRUST_STORE_PASSWORD), 
  STOREPASSWORDCCSID => 37, 
  IMPORTPATH => JKS_TRUST_STORE, 
  IMPORTPATHLEN => LENGTH(JKS_TRUST_STORE), 
  IMPORTFORMAT => 'OBJN0100', 
  IMPORTVERSION => '*PKCS12V3 ',
  IMPORTPASSWORD => JKS_TRUST_STORE_PASSWORD,
  IMPORTPASSWORDLEN => LENGTH(JKS_TRUST_STORE_PASSWORD), 
  IMPORTPASSWORDCCSID => 37, 
  ERRORCODE => X'00000000000000000000000000000000000000000000000000000000000000');

-- Use the new trust store to verify it works.
values http_get(
   URL => 'https://www.ibm.com/support/pages/sites/default/files/inline-files/xmldoc.xml',  
   OPTIONS => '{"sslCertificateStoreFile":"' CONCAT NEW_TRUST_STORE CONCAT '"}'); 

-- Cleanup the schema 
DROP SCHEMA FROM_JAVA_TRUST_STORE;

Common errors

When an error occurs, use the VERBOSE form of the function to get additional information about the error. These functions will return error information in the RESPONSE_MESSAGE column. For example, the following invocation will return information about a page not found error.
SELECT * FROM TABLE(QSYS2.HTTP_GET_VERBOSE('http://www.w3.org/notfound.html','')); 
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.
    AXISC ERROR  : HTTPTransportException: Channel I/O operation timed
      out. Failed to open connection to server at host 192.168.1.233 and port 80. 
      Error is 3447 - A remote host did not respond within the timeout period.
  • If DNS is not configured on the system, the following error may be encountered.
    AXISC ERROR  : AxisTransportException: Cannot open a channel to the
      remote end. Failed to open connection to server at host XXXXX and port 80.
      Error is 3401 - Permission denied 
  • If https is being used, the connection uses SSL. In order to work properly, the server must use a certificate signed by a certificate authority present in the certificate store specified by the sslCertificateStoreFile option.
    AXISC ERROR  : HTTPTransportException: Cannot initialize a channel
      to the remote end. Failed to establish SSL connection to server, the operation
      gsk_secure_soc_init() failed. GSKit Error is 6000 - Certificate is not signed 
      by a trusted certificate authority. 
  • http Authentication must be specified using the basicAuth option. An attempt to use basic authentication on the URL (for example https://userid:password@example.com/login) results in the following error.
     AXISC ERROR  : AxisTransportException: Cannot open a channel to the 
      remote end. Failed to open connection to server at host userid and port 0.
      Error is 3421 - Address not available.

Debugging considerations

You should use IBM i Access Client Solutions (ACS) Run SQL Scripts to execute the HTTP functions in order to see error information. Using STRSQL to invoke HTTP functions will not provide useful problem information.

Because the HTTP functions use the networking functions provided by AXISC and GSkit, any networking problem 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, an AXISC trace may provide more information.

Information about enabling the AXISC trace can be found at Enabling a Client Trace for a Web Services Client.

End of change