Start of change

HTTP_GET

The HTTP_GET scalar function retrieves a text-based resource from the specified URL through an HTTP GET request.

Read syntax diagramSkip visual syntax diagramHTTP_GET(url ,options)
url
An expression that returns a built-in character string or graphic string data type that specifies the URL of the resource being accessed.
The only supported protocols in the URL are http:// and https://. HTTP basic authentication in the form https://userid:password@ cannot be specified in the URL. Instead, the basicAuth option must be used to specify the userid and password for basic authentication.
options
An expression that returns a built-in character string or graphic string data type. This string must be formatted as a JSON object containing the options, including headers, for the request. See HTTP options for the list of options.
If no options are provided, the default options are used.

The result of the function is a CLOB(2G) CCSID 1208 containing the response message.

Notes

For more information about using the HTTP functions see HTTP functions overview.

To return the header information from the HTTP request, use the HTTP_GET_VERBOSE table function: HTTP_GET_VERBOSE

Example

  • Retrieve service information from the IBM PSP website using a certificate store created from a Java certificate store. See SSL considerations for more information.
    VALUES QSYS2.HTTP_GET(
                 'https://www.ibm.com/support/pages/sites/default/files/inline-files/xmldoc.xml',
                 '{"sslCertificateStoreFile":"/home/javaTrustStore/fromJava.KDB"}');
    

HTTP options

Each HTTP function has a parameter for passing options to be used on the request. These options include the setting of the HTTP headers. This string must be a JSON object in the following format:
{"option":"option-setting","option":"option-setting"}
For example, using the option string
{"header":"User-Agent,IBM i HTTP function"}
will cause
"User-Agent": "IBM i HTTP function"
to be included in the HTTP header sent to the server.

The following options can be set in the JSON object:

Table 1. HTTP options
Option Possible settings Default setting Description
basicAuth userid,password   Sets the userid and password that will be used for HTTP basic authentication.

To prevent exposure of the userid and password, this option is only allowed when the URL begins with https:.

connectTimeout 0-2000000 Dependent on TCP/IP system settings Sets the connect timeout value. If timeout is greater than zero, the value will be used as the maximum time, in seconds, to wait for a connection to complete.
header headername,headervalue   Sets an HTTP header in the HTTP request with the specified headername and headervalue.

This option can be specified multiple times to set multiple headers.

A common headers that may be required is the Accept header. This header can be set in the following way: "header":"Accept,*"

By default, only the Host and User-Agent HTTP header are set for all requests. The Host is set using the host information in the URL. The User-Agent header defaults to curl/XX.YY where XX.YY are some version numbers. This value was chosen to provide anonymity when accessing web services.

If the content-type HTTP header is not set for a POST or PUT operation, the default content type that will be used is:

Content-Type: text/xml; charset=UTF-8

To correctly use these headers, consult the appropriate Web server and RFC documentation on the use of HTTP request headers. Examples of RFCs are RFC2616 and RFC7231.

ioTimeout 0-2000000 Dependent on TCP/IP system settings Sets how long to wait for a read request to complete. If timeout is greater than zero, the value will be used as the maximum time, in seconds, to wait for a read request to complete.
proxy host,port   Sets the HTTP proxy information.
proxyAuth userid,password   Sets the HTTP proxy authentication information.

To prevent exposure of the userid and password, this option is only allowed when the proxySsl option is true.

proxySsl
true
false
true Sets whether a secure (SSL) connection should be used when connecting to the proxy server. A value of true indicates that the transport will connect to the proxy server using a secure channel. A value of false indicates that the transport will connect to the proxy server using an unsecure channel.
redirect 0-2000000 0 Specifies the number of HTTP redirects that will be followed. If the number is greater than zero, HTTP redirects will be followed up to the number specified. If the value is less than one, HTTP redirects will not be followed.
signalErrors
true
false
false Indicates whether an HTTP error should be signaled as an SQLException. This will be seen as SQLSTATE 38501, SQLCODE -443.
sslCertificateStoreFile file
/QIBM/USERDATA/ICSS/
CERT/SERVER/DEFAULT.KDB
Specifies the name of the certificate store file to be used for the secure session or SSL environment. This parameter is ignored if the sslApplicationID option is set to a value.

If SSL communication is to be done by using a path to a keystore file, the user profile the application is running under must have authority to the file.

  • Execute (*X) data authority to each directory preceding the stream file being read and
  • Read (*R) data authority to the stream file

This option is only used when the URL begins with https:.

sslCertificateStorePassword password   Specifies the password for the certificate store file to be used for the secure session or SSL environment. If the option is not passed or is set to the null string, the internal stash file associated with the user profile that is being used to run the application is used as long as the user has authority to the certificate store file and the password has been used one time on the system. This option is ignored if the sslApplicationID option is set to a value.

This option is only used when the URL begins with https:.

sslCertificateLabel label   Specifies the label associated with the certificate in the certificate store to be used for the secure session or SSL environment. If the option is not passed or is set to the null string, the default certificate label in the specified certificate store file is used for the SSL environment. This option is ignored if the sslApplicationID option is set to a value.

This option is only used when the URL begins with https:.

sslTLS11
ENABLE
NONE
NONE Enable or disable the TLS Version 1.1 ciphers. A value of NONE will disable the ciphers; any other value will enable the ciphers.

This option is only used when the URL begins with https:.

sslTLS12
ENABLE
NONE
ENABLE Enable or disable the TLS Version 1.2 ciphers. A value of NONE will disable the ciphers; any other value will enable the ciphers.

This option is only used when the URL begins with https:.

sslTLS13
ENABLE
NONE
ENABLE Enable or disable the TLS Version 1.3 ciphers. A value of NONE will disable the ciphers; any other value will enable the ciphers.

This option is only used when the URL begins with https:.

sslTolerate
true
false
false Tolerate soft validation errors (expired certificate or certificate not in certificate store). Specify a value of true to tolerate soft validation errors, or false to not tolerate soft validation errors.

Warning: Enabling this option allows man-in-the-middle attacks to occur and is not recommended.

This option is only used when the URL begins with https:.

sslApplicationId id   Application ID to use for the SSL environment.

This option is only used when the URL begins with https:.

sslDomainName name Set using hostname present in https: URL Fully qualified domain name that will be used as Server Name Indication (SNI) as defined by RFC 6066.

This option is only used when the URL begins with https:.

sslSniCritical
true
false
false A value of true indicates that the SNI request is critical and thus the server must support the SNI extension. Otherwise, the secure connection will fail if server does not send the extension. A value of false indicates that the server does not need to support the SNI extension.

This option is only used when the URL begins with https:.

Some of the options can expose sensitive information such as passwords. A number of best practices can be use to prevent the sensitive information from being exposed in the SQL statement text, which may be visible using DBMON. Here are some examples of best practices to protect the password.
  1. Use a global variable and concatenate the global values when the SQL Statement is executed.
    CREATE VARIABLE MYLIB.HTTP_PW VARCHAR(30); 
    SET MYLIB.HTTP_PW = 'http_pwd';
    VALUES QSYS2.HTTP_GET( 'https://www.somesite.com/authorized_info.html', '{"basicAuth":"userid,' CONCAT MYLIB.HTTP_PW CONCAT '"}' );
    
  2. For interfaces that support a parameter marker, set the password using a parameter marker. Here is an QSHELL example using the JDBC client provided in jt400.jar.
    java -jar /qibm/proddata/os400/jt400/lib/java6/jt400.jar jdbc:db2:localhost
    !PREPARE select QSYS2.HTTP_GET( 'https://www.somesite.com/authorized_info.html', 
                                    '{"basicAuth":"userid,' CONCAT ? CONCAT '"}' ) from sysibm.sysdummy1
    !setParm 1,http_pwd
    !executeQuery
    
  3. Store passwords in a table, protected with a column mask.
    CREATE MYLIB.TABLE PASSWORDS (TYPE VARCHAR(20), PWD_VALUE VARCHAR(100) CCSID 37);
    
    CREATE MASK MYLIB.PASSWORD_MASK ON MYLIB.PASSWORDS
    FOR COLUMN PWD_VALUE RETURN
    CASE WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'<my user id>') = 1)
    THEN PWD_VALUE
    ELSE 'Sorry' END
    ENABLE;
    
    ALTER TABLE MYLIB.PASSWORDS
    ACTIVATE COLUMN ACCESS CONTROL;
    
    INSERT INTO MYLIB.PASSWORDS VALUES ('HTTP', 'My secure password');
    
    VALUES QSYS2.HTTP_GET( 'https://www.somesite.com/authorized_info.html', 
                           '{"basicAuth":"userid,' CONCAT 
                           (SELECT PWD_VALUE FROM MYLIB.PASSWORDS WHERE TYPE = 'HTTP') CONCAT 
                           '"}' );
    
End of change