MustGather: IBM i DB2 SYSTOOLS HTTP Functions (HTTPGETCLOB, etc.)

Job CCSID
Make sure the job CCSID executing the DB2 SYSTOOLS HTTP Function is not 65535 using the WRKJOB command.  Check the value of the system value, QCCSID (DSPSYSVAL QCCSID).  If this system value is set to 65535, this might be filtering into the job CCSID, which will cause the DB2 SYSTOOLS HTTP Function to fail.  These HTTP Functions require the job CCSID to be set to a valid EBCIDIC Language CCSID.  65535 is a Binary CCSID and is not allowed to be used.  IBM recommends the QCCSID system value be set to 37 for English language servers.  If you can't change the QCCSID system value, customize the specific job executing the HTTP Function to use a CCSID value of 37 or an appropriate EBCIDIC CCSID.

Gather 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 SYSDUMMY1.  (The primary exception is shown in the 2nd-level message text.)

Here is an example of the message for a failed HTTPGETCLOB SQL execution caused by the Java TrustStore not being defined in the JVM process and/or the TrustStore does not contain the required CA certificate(s).  Clients can follow the IBM document, Configuring IBM i DB2 SYSTOOLS HTTP User-Defined Functions for TLS/HTTPS Secure Communications, for detailed information on how to properly configure SSL/TLS for these HTTP UDFs and to resolve the exception you see below.

CPF503E    Diagnostic              30   11/21/19  12:09:48.588938  QQURB        QSYS        16C5     QQUDA       QSYS        03FB
                                     Thread  . . . . :   00000061
                                     Message . . . . :   User-defined function error on member SYSDUMMY1.
                                     Cause . . . . . :   An error occurred while invoking user-defined function
                                       HTTPGETCLOB in library SYSTOOLS. The error occurred while invoking the
                                       associated external program or service program B2RESTUDF: in library
                                       SYSTOOLS.D, program entry point or external name
                                       com.ibm.db2.rest.DB2UDFWrapper.httpGetClob, specific name HTTPG00005. The
                                       error occurred on member SYSDUMMY1 file SYSDUMMY1 in library SYSIBM. The
                                       error code is 1. The error codes and their meanings follow: 1 -- The
                                       external program or service program returned SQLSTATE 38000. The text
                                       message returned from the program is:
                                         SYSTOOLS.HTTPGETCLOB  HTTPG00005 Dcom.ibm.jsse2.util.h: PKIX path building
                                       failed: java.security.cert.CertPathBuilderException: PKIXCertPathBuilderImpl
                                       could not build a valid CertPath.; internal cause is:
                                         java.security.cert.CertPathValidatorException: The certificate issued by
                                       CN=rch730a.rchland.ibm.com_CERTIFICATE_AUTHORITY, O=IBM Web Administration
                                       for i, ST=Any, C=US is not trusted; internal cause is:
                                         java.security.cert.CertPathValidatorException: Certificate chaining error

SQJAVA Trace

A SQJava trace will provide detailed information on the DB2 SYSTOOLS HTTP Function call including Java exceptions, input properties, and detailed SQL execution information.

Instructions for gathering the SQJAVA trace of the Java stored procedure that is failing:
 

1) ADDENVVAR ENVVAR(QIBM_COMPONENT_TRACE_LEVEL) VALUE('SQJAVA,VERBOSE') LEVEL(*SYS)
2) Restart the job executing the HTTP SQL UDF.
3) Recreate the issue.
4) RMVENVVAR ENVVAR(QIBM_COMPONENT_TRACE_LEVEL) LEVEL(*SYS)
5) WRKOBJ OBJ(QUSRSYS/QP0Z*) OBJTYPE(*USRSPC) - Find the object that is for the job name, user and number to verify it is there.
6) DMPUSRTRC JOB(JobNumber/JobUser/JobName)
7) CPYF FROMFILE(QTEMP/QAP0ZDMP) TOFILE(*PRINT)
8) Locate the spool file(s) produced, convert them to plain text, and upload them for IBM's review.

How to download spool files - Small, Medium, Large - and in the correct ASCII format
 

When tracing is on, a user space is created in QUSRSYS to hold the trace information.  This user space has a name, QP0Znnnnnn, where nnnnnn is the number of the job.  If you turn on SQJAVA tracing, you'll periodically want to clean out the traces that will accumulate in QUSRSYS.  

A trace is displayed using the DMPUSRTRC command.  This command requires the complete job name and can dump the trace to a file or to the screen.

TRCCNN TCP/IP Trace