systools httpPostClob SQLCOD -4301

[mjhaston]

We've been seeing some inconsistent behavior with the use of httpPostClob yesterday and today. We have one main program which does all of the SOAP calls with the code below:

exec sql                                          
  select systools.httpPostClob(                   
    :url,                                         
    :header,                                      
    cast(:requestData as clob(20k) ccsid 1208))   
  into :returnData                                
  from sysibm.sysdummy1                           
;

This program has been called thousands of times and now we're having issues the past two days. We had system maintenance over the weekend that wasn't supposed to effect our partition at all, but I'll have to get on the phone with those guys.

We wondered if it might have something to do with user profile differences. I have *SECOFR and *AUDIT authorities and the call is successful for me. A fellow program is setup with *PGMR and no *AUDIT and the call is failing.

When I make the call from Run SQL Script here is the Verbose output:

<?xml version="1.0" encoding="UTF-8" ?>
<httpHeader responseCode="500">
    <responseMessage>Internal Server Error</responseMessage>
    <header name="X-Frame-Options" value="DENY"/>
    <header name="HTTP_RESPONSE_CODE" value="HTTP/1.1 500 Internal Server Error"/>
    <header name="Strict-Transport-Security" value="max-age=31536000"/>
    <header name="Server" value="Apache-Coyote/1.1"/>
    <header name="X-OPNET-Transaction-Trace" value="a2_fccd538d-b777-4553-bac4-0dfe6e2a7408"/>
    <header name="X-Content-Type-Options" value="nosniff"/>
    <header name="X-Cnection" value="close"/>
    <header name="Content-Length" value="773"/>
    <header name="X-XSS-Protection" value="1; mode=block"/>
    <header name="Date" value="Wed, 22 Jan 2020 15:31:29 GMT"/>
    <header name="Content-Type" value="text/xml"/>
    <header name="v-c-response-time" value="1579707089"/>
</httpHeader>

My job log shows:

Java Virtual Machine is IBM Technology for Java. PID(3853)
JVM properties were loaded from a properties file.
Open attributes not valid in a multithreaded job.
Error(s) occurred during running of QsnInzSsnD API.
Open attributes not valid in a multithreaded job.
Error(s) occurred during running of _________ API.
Error(s) occurred during running of QsnCrtEnv API.
Open attributes not valid in a multithreaded job.
Error(s) occurred during running of _________ API.


I'm just noticing the missing name of the API.

User-defined function error on member SYSDUMMY1.

                         Additional Message Information                        

 Message ID . . . . . . :   CPF503E       Severity . . . . . . . :   30        
 Message type . . . . . :   Sender copy                                        
 Date sent  . . . . . . :   01/22/20      Time sent  . . . . . . :   10:49:25  

 Message . . . . :   User-defined function error on member SYSDUMMY1.          
 Cause . . . . . :   An error occurred while invoking user-defined function    
   HTTPPOSTCLOB 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.httpPostClob, specific name HTTPP00013. 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 58004. The 
   text message returned from the program is: ██ .                             
     2 -- The external program failed before it completed.                     
     3 -- The database timed out waiting for the program to return. The timeout

Java interpreter startup or communication failed for reason code 1.

                        Additional Message Information                        

Message ID . . . . . . :   SQL4301       Severity . . . . . . . :   30        
Message type . . . . . :   Diagnostic                                         
Date sent  . . . . . . :   01/22/20      Time sent  . . . . . . :   10:49:25  

Message . . . . :   Java interpreter startup or communication failed for      
  reason code 1.                                                              
Cause . . . . . :   An error occurred while attempting to start or communicate
  with a Java interpreter. The reason codes and their meanings follow:        
    1 -- Java environment variables or Java database configuration parameters 
  are invalid.                                                                
    2 -- A Java Native Interface call to the Java interpreter failed.         
    4 -- The Java interpreter has terminated itself and cannot be restarted.  
Recovery  . . . :   Ensure that Java environment variables or Java database   
  configuration parameters are valid.  Ensure that a Java method called by the
  Java interpreter does not use System.out.  Ensure that internal DB2 classes 
  (com.ibm.db2) are not overridden by user classes.

Thanks.
Mike

[mjhaston]

Adding a few log/dmp files. They're meaningless to me. There's also a 2gig dump I won't be able to upload.

[jtaylor___]

Does this help?

https://www.ibm.com/support/pages/limitations-calling-java-store-procedures-job-where-jvm-has-already-been-created

[mjhaston]

This could very well be our issue. This call using httpPostClob has been working for a while now and this first program to break it is using JVM to create a spreadsheet. So I think you're going to turn out to be correct.

Thank you.

[jtaylor___]

You're probably setting the CLASSPATH for your spreadsheet code and then activating the JVM. When it gets to the httpPostClob(), it needs a different CLASSPATH and crashes. On the flip side, if you run httpPostClob() first then your spreadsheet code would crash.

FYI, I submitted an RFE requesting that IBM resolve this restriction. Please vote for it.

http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=130541

[mjhaston]

Voted. Thank you. That was frustrating and nothing I found from searches eluded to the real problem.

[jtaylor___]

I beat on it a while before contacting IBM support. I think IBM might have written that support doc as a result of my PMR.

I really like the new IBM HTTP functions, but I had to stick with Scott Klement's HTTP API (https://www.scottklement.com/httpapi/httpapi_zip.html).

[mjhaston]

Did you have any issues when you would use Java in a previous program and then later in a session use httpPostClob? A co-worker just ran into an error again in a non spreadsheet program. He signed off and back on and ran it successfully. We're just hoping running a spreadsheet program and then later a program that uses httpPostClob would be an issue. I don't think we programmatically set the class path anywhere. It's just set for the system or job.

[jtaylor___]

For me, our Java and HttpPostClob() could never coexist in the same job. Whichever ran first worked and the second one failed, regardless of which was first.

Is your classpath the IBM default? I would assume that HttpPostClob() is compatible with the IBM default classpath.

[mjhaston]

Name . . . . . . . . . : CLASSPATH
Value . . . . . . . . . : '.:/JAVA:/JAVA/TOOLS:/JAVA/REPORTS:/JAVA/jt400.jar'

Do you ever run into problems between jobs? Job A uses httpPostClob() and then the next job uses JVM?

[jtaylor___]

Never between jobs.

I don't think that's the IBM default classpath. If httpPostClob() starts the JVM within an IBMi job, I don't think that JVM will have any of your /JAVA entries.

Try this, do a WRKJVMJOB on a job where your code started the JVM vs a job where httpPostClob() started the JVM. What are the two classpaths?

[mjhaston]

I'm not sure what I'm even looking at with the WRKJVMJOB, but I'll try to get you the classpaths. I did just test a spreadsheet job and then an httpPostClob job right afterwards and the httpPostClob job fails. If I do it in reverse the spreadsheet job fails.

Just reading your message a few back, I think that's what you were finding. I was calling a job a different program call, but it's all in the same session/job.

I'll look into some of the answers/workarounds in the link you provided "Limitations to calling java stored procedures".

[jtaylor___]

...
Just reading your message a few back, I think that's what you were finding. I was calling a job a different program call, but it's all in the same session/job.
...

Yes, that's exactly it. My workaround is not to use the IBM Java stored procedures.

[mjhaston]

Ugh! That's so not what I was hoping to hear!

Thanks for all of your help though.

[mjhaston]

jtaylor___ here I am again using httpPostClob( ) to print a Zebra label! I seem to be getting blocked occasionally! I get a string back from a UPS API which is the label. I turn around and call from our IBM i to a PC in our warehouse that has a small Nodejs express server running on it. That script then passes my label onto a print service running on the PC. We can't see anything getting blocked when we trace it and when I test, but our label is not printing at times. There is no pattern to it. All I see in the job log is that the connection was refused.

Have you pursued httpPostClob recently?

[Scott Klement]

"Connection Refused" means that there isn't an application running on the port you're trying to connect to.

Either you're somehow connecting to the wrong machine (and the one you're reaching has no application on that port), a firewall is blocking access to that port, or the server on the destination system is not running.

I can't see how this could possibly be a problem with httpPostClob. (That said, I certainly wouldn't recommend using httpPostClob!)