Error consuming Web Service via Systools.HTTPPOSTCLOB

[gio.cot]

Hi all
I’m try to consume a web service with this statement:

SELECT *
FROM JSON_TABLE(
Systools.HTTPPOSTCLOB(
'https://wsurl/zzz/changexx',
'<httpHeader> <header name="content-type" value="application/json" /> </httpHeader> ' ,

'{"user":"MYUSER","psw":"mypass":"123","order_id ": "6","sts":"A","sub":"X"}'),
'$'
COLUMNS(
"status" VARCHAR(30) PATH 'lax $.status' DEFAULT '' ON EMPTY,
"err" VARCHAR(1024) PATH 'lax $.err' DEFAULT '' ON EMPTY
)
) AS x;

but i receive this error:

Message: [SQL4302] The Java stored procedure or user-defined function SYSTOOLS.HTTPPOSTCLOB and the specific name HTTPP00013 aborted with an error "com.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 expired at Sat May 30 12:48:38 CEST 2020; internal cause is:
java.security.cert.CertificateExpiredException: NotAfter: Sat May 30 12:48:38 CEST 2020". It causes . . . : The Java stored procedure or user-defined function was aborted with a Java exception. If the SQJAVA component trace is active, the component trace for the job contains a Java stack for the interrupted method. Correction. . : Debug the Java method to eliminate the error.

I’m running V7R2

Which could be the problem ?

Thanks in advance

[Scott Klement]

The error message says: "The certificate expired at Sat May 30 12:48:38 CEST 2020"

Do you have any reason to believe that's not the problem?

[gio.cot]

Hi Scott
Ok for your suggestion, but i'm not able to locate/find a certificate expired on 30-05-2020 .. also if i have other certificated expired in other date ...
do you suggest me to delete all the certifiate expired ?
thanks in advance
Gio

[paolinosal]

Hi Joe,

put a look here:

https://www.ibm.com/support/pages/configuring-ibm-i-java-client-applications-tlshttps-secure-communications

probably you have to update your cacerts, because you didn’t have some certificate authority’s in this

[Scott Klement]

Ok for your suggestion, but i'm not able to locate/find a certificate expired on 30-05-2020 .. also if i have other certificated expired in other date ...
do you suggest me to delete all the certifiate expired ?

I can't think of any reason to keep expired certificates. You can't use them for anything after they are expired. So, yes, delete them.

If you can't find them, it's probably because you aren't looking in the right place. Make sure you are looking in the Java environment, not the Digital Certificate Manager. I am not an expert in the Java environment -- I don't like Java, and I don't like the SYSTOOLS http functions. Since I don't use them, I can't tell you how to use them. I think Paolinosal's link will help, though.

Please consider updating your code to use the newer HTTP functions they added to SQL last year instead of these old SYSTOOLS ones. Or use a better tool like HTTPAPI or GETURI.

[gio.cot]

.... . Or use a better tool like HTTPAPI or GETURI.

hi Scott ... i'm considering to use HTTPAPI . i'm try to follow your example WATSONTR2R.rpgle, (as i'm running on V7R2 without data-gen PTF), but i have problem with yajl_copyBufStr : in which YAJL version i can find this procedure (yajl_copyBufStr) .. i'm not able to find it ... do you have another example more near to my situation . V7R2
Thanks in advance
Gio

[gio.cot]

Hi Scott
Ok, found the procedure yajl_copyBufStr ..
sorry

[Scott Klement]

You don't have to use YAJL just because you are using HTTPAPI. HTTPAPI can be used to communicate with the API, and SQL (or any other tool) can be used to process the JSON. Likewise, you can use SQL's HTTP routines, but process the JSON with YAJL.

[gio.cot]

Hi Scott
now with HTTPAPI it works ..

You don't have to use YAJL just because you are using HTTPAPI.

Ok, but for preparing / building Body to pass to the web service, i need use of YAJL .. No ?

Thanks

[Scott Klement]

]
Ok, but for preparing / building Body to pass to the web service, i need use of YAJL .. No ?

You can use YAJL. There are other ways as well, such as SQL's JSON support. Didn't I just say this?!