Troubleshooting
Problem
This document provides information on what data to gather when experiencing issues executing the IBM i DB2 QSYS2 HTTP Functions.
HTTP_GET
HTTP_GET_BLOB
HTTP_GET_VERBOSE
HTTP_GET_BLOB_VERBOSE
HTTP_POST
HTTP_POST_BLOB
HTTP_POST_VERBOSE
HTTP_POST_BLOB_VERBOSE
HTTP_PUT
HTTP_PUT_BLOB
HTTP_PUT_VERBOSE
HTTP_PUT_BLOB_VERBOSE
HTTP_DELETE
HTTP_DELETE_BLOB
HTTP_DELETE_VERBOSE
HTTP_DELETE_BLOB_VERBOSE
HTTP_PATCH
HTTP_PATCH_BLOB
HTTP_PATCH_VERBOSE
HTTP_PATCH_BLOB_VERBOSE
URL_ENCODE
URL_DECODE
BASE64_ENCODE
BASE64_DECODE
Environment
IBM i 7.3, 7.4, and 7.5 OS
Resolving The Problem
IBM i Job Log
Gather and upload the IBM i job log for the job executing the SQL statement. This could be a QZDASOINIT/QZDASSINIT (Database Host Server), QSQSRVR (Native CLI), QZRCSRVS (Remote Command Host Server), or an interactive 5250 device (i.e. QPADEVXXXX) job.
In the IBM i job log, you would see an error similar to the following:
CPF503E - User-defined function error on member QSQPTABL (The primary exception is shown in the 2nd-level message text.)
Here is an example of the message for a failed QSYS2.HTTP_GET SQL HTTPS URL execution caused by one or more missing Certificate Authority (CA) certificates in your DCM *SYSTEM certificate store. Clients can follow the IBM document, Setting Up a Client to Consume a Web Service Over an SSL (HTTPS) Connection & Digital Certificate Manager for i FAQ, for detailed information on how to properly configure SSL/TLS & DCM for these QSYS2 HTTP Functions and to resolve the exception you see below.
CMessage ID . . . . . . : CPF503E Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 12/28/23 Time sent . . . . . . : 17:20:47
Message . . . . : User-defined function error on member QSQPTABL.
Cause . . . . . : An error occurred while invoking user-defined function
HTTP_GET in library QSYS2. The error occurred while invoking the associated
external program or service program QSQAXISC in library QSYS, program entry
point or external name axiscGetClob, specific name HTTP_GET. The error
occurred on member QSQPTABL file QSQPTABL in library QSYS2. The error code
is 1. The error codes and their meanings follow:
1 -- The external program or service program returned SQLSTATE 38501. The
text message returned from the program is: 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
Message type . . . . . : Diagnostic
Date sent . . . . . . : 12/28/23 Time sent . . . . . . : 17:20:47
Message . . . . : User-defined function error on member QSQPTABL.
Cause . . . . . : An error occurred while invoking user-defined function
HTTP_GET in library QSYS2. The error occurred while invoking the associated
external program or service program QSQAXISC in library QSYS, program entry
point or external name axiscGetClob, specific name HTTP_GET. The error
occurred on member QSQPTABL file QSQPTABL in library QSYS2. The error code
is 1. The error codes and their meanings follow:
1 -- The external program or service program returned SQLSTATE 38501. The
text message returned from the program is: 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.
IBM recommends downloading and importing the remote HTTPS URL's CA certificates as the most secure HTTPS configuration option. However, a less secure option to resolve a "6000 - Certificate is not signed by a trusted certificate authority" error is to set the "sslTolerate=true" option. This will allow soft TLS validation errors to be tolerated and ignored, which includes certificate errors.
Example:
VALUES CAST(QSYS2.HTTP_GET('https://google.com','{"sslTolerate":"true"}') AS VARCHAR(2048) CCSID 37)
Additional TLS options can be found here: https://www.ibm.com/docs/en/i/7.5?topic=functions-http-get-http-get-blob#rbafzscahttpget__HTTP_options
IBM i Web Service Client Trace
https://www.ibm.com/support/pages/enabling-client-trace-web-services-client
The easiest way is to enable the trace globally by adding a new line with the "ClientLogPath:/tmp/axis.log" text to the /qibm/proddata/os/webservices/v1/client/etc/axiscpp.conf file. Ensure the IBM i user profile executing the QSYS2 HTTP Function has *RWX data authority to this directory path specified.
WRKLNK '/qibm/proddata/os/webservices/v1/client/etc/axiscpp.conf'
Option 2
Add a new line with the following text:
ClientLogPath:/tmp/axis.log
Press F3 twice to save and exit.
Execute the QSYS2 HTTP Function to generate a trace file.
Gather and upload the /tmp/axis.log file .
TRCCNN TCP/IP Trace
A TCP/IP trace can be gathered between the IBM i and the remote port specified in the URL passed into the DB2 QSYS2 HTTP Function. The TCP/IP trace will help identify any communication or TLS handshake issues.
1) TRCCNN SET(*ON) TRCTYPE(*IP) TRCTBL(IBM) SIZE(250000) TCPDTA(*TCP () (443))
NOTE: Port 443 is the remote port specified in the URL passed to the DB2 QSYS2 HTTP Function. Please verify this HTTP/HTTPS URL as well as the port value. If the remote port is not 443, replace the port value accordingly in the TRCCNN command above.
2) Recreate the issue.
3) TRCCNN SET(*OFF) TRCTBL(IBM) OUTPUT(*STMF) TOSTMF('/tmp/TRCCNN.cap' *YES)
1) TRCCNN SET(*ON) TRCTYPE(*IP) TRCTBL(IBM) SIZE(250000) TCPDTA(*TCP () (443))
NOTE: Port 443 is the remote port specified in the URL passed to the DB2 QSYS2 HTTP Function. Please verify this HTTP/HTTPS URL as well as the port value. If the remote port is not 443, replace the port value accordingly in the TRCCNN command above.
2) Recreate the issue.
3) TRCCNN SET(*OFF) TRCTBL(IBM) OUTPUT(*STMF) TOSTMF('/tmp/TRCCNN.cap' *YES)
Then, gather and upload the /tmp/TRCCNN.cap file.
DCM Certificate Store Information
Prompt the QMGTOOLS/PRTSTORE utility command with an F4, enter your DCM *SYSTEM certificate store password and press ENTER. Then, gather and upload the resulting files in /tmp/collectorscripts/data/DCM IFS directory.
Example:
PRTSTORE STORE_PWD(SystemStorePasswordGoesHere) EXP_DAYS(30) IFS_PATH('/tmp/collectorscripts/data/DCM')
Output:
STORE_LISTING.txt : Listing of all the Certificates in the *SYSTEM store
EXPIRE_LISTING.txt: Listing of all the certificates that are expired or set to expire within the number of days specified.
DCM_APP_LISTING.txt: Listing of all the server and client applications and what certificate they are assigned.
Upload Instructions
MustGather: Instructions for Sending Data to IBM i Support
Related Information
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHbAAM","label":"IBM i Db2-\u003EMustGather Database"},{"code":"a8m3p000000F98WAAS","label":"IBM i Db2-\u003EQSYS2 HTTP Functions"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.3.0;7.4.0;7.5.0"}]
Was this topic helpful?
Document Information
Modified date:
31 January 2024
UID
ibm17103335