HTTP functions overview

The foundational functions are named according to the three dimensions used when making HTTP requests.

• The first dimension is the HTTP operation. There are 5 different HTTP operations: GET, PUT, POST, PATCH, and DELETE.
• The second dimension is the data type used for sending and returning data. There are two alternatives: BLOB and the implied value of CLOB.
• The third dimension indicates whether the verbose version of the function should be used. The non-verbose functions are scalar functions. 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 following table summarizes the 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 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"}

Some additional HTTP functions are utility functions for working with HTTP requests.

This procedure 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) CCSID 37;
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) CCSID 37; 
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.
-- This step assumes that the system has a connection to the internet and that
-- a network firewall is not interposing its own TLS certificate on the connection.
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;

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.