Hi Team,

Good morning !

I am executing the below SQL query in IBM DB2 using program DSNTIAUL in JCL.

DB2 function used : DB2XML.HTTPOSTCLOB ( 'url' , 'header' , 'graphql query in request body' )
parameters passed in the function : DB2XML.HTTPOSTCLOB ( 'url' , 'header' , 'graphql query in request body' )
response is CLOB data.


DB2 Query executed:

SELECT DB2XML.HTTPCLOB                                      
('https://xxxxxxxxxx',                      
cAST ('<httpheader><header name="content-type" value='      
!!'"application/json"/><header name="cache-control" '       
!!'value="no-cache"/><header name="authorization" '         
!!'value="yyyy"/>'
!!'</httpheader>' AS CLOB(1K) ),                            
CAST ('{"query": "query Test{parts{name}}"}' as clob(1k)))  
FROM SYSIBM.SYSDUMMY1;      

Response error : 400- bad response.

0 DSNT493I SQL ERROR DURING SQL STATEMENT    FETCH   , TABLE TBLNAME
 DSNT408I SQLCODE = -4302, ERROR:  JAVA STORED PROCEDUE OR USER-DEFINED FUNCTION DB2XML.HTTPPOSTCLOB (SPECIFIC NAME
          DB2XML.HTTPPOSTCLOB) HAS EXITED WITH AN EXCEPTION java.io.IOException: Server returned HTTP response code: 400
          for URL: https:xxxxxxx
 DSNT418I SQLSTATE   = 38000 SQLSTATE RETURN CODE
 DSNT415I SQLERRP    = DSNX9CAC SQL PROCEDURE DETECTING ERROR

Action taken till now:
1. Cleared the certificate issue (SSL handshake) by adding the required certificate in trust store.
2. Now the connection is established successfully and request hitting my endpoints and endpoint returning java io exception with bad response 400.
3. On checking the endpoint server side, this query giving graphql parse error.
4. I had created one small graphql query and tested in postman to get successful response
5. I had tried to pass it in request body but still getting java io exception with bad response.

Further action  needed:
1. How to pass the graphql query in request body of the httppostclob method. -- need to analyse
2. How to received the CLOB response of the httppostclob method from the endpoint server.-- need to analyse

Shall you look this.

Thanks.                   

Hi Solairaj,

have you tried the new http sql function? 

Values Http_Post('https://httpbin.org/post', 'your post data', '{"headers":{"accept":"application/json"}}');

Faster, NO java, use DCM for CA cert validation.

https://www.ibm.com/support/pages/node/6486889

You need at least V7R3 at certains TR level.

Bye

Hi Team,

Good morning !

I am executing the below SQL query in IBM DB2 using program DSNTIAUL in JCL.

DB2 function used : DB2XML.HTTPOSTCLOB ( 'url' , 'header' , 'graphql query in request body' )
parameters passed in the function : DB2XML.HTTPOSTCLOB ( 'url' , 'header' , 'graphql query in request body' )
response is CLOB data.


DB2 Query executed:

SELECT DB2XML.HTTPCLOB                                      
('https://xxxxxxxxxx',                      
cAST ('<httpheader><header name="content-type" value='      
!!'"application/json"/><header name="cache-control" '       
!!'value="no-cache"/><header name="authorization" '         
!!'value="yyyy"/>'
!!'</httpheader>' AS CLOB(1K) ),                            
CAST ('{"query": "query Test{parts{name}}"}' as clob(1k)))  
FROM SYSIBM.SYSDUMMY1;      

Response error : 400- bad response.

0 DSNT493I SQL ERROR DURING SQL STATEMENT    FETCH   , TABLE TBLNAME
 DSNT408I SQLCODE = -4302, ERROR:  JAVA STORED PROCEDUE OR USER-DEFINED FUNCTION DB2XML.HTTPPOSTCLOB (SPECIFIC NAME
          DB2XML.HTTPPOSTCLOB) HAS EXITED WITH AN EXCEPTION java.io.IOException: Server returned HTTP response code: 400
          for URL: https:xxxxxxx
 DSNT418I SQLSTATE   = 38000 SQLSTATE RETURN CODE
 DSNT415I SQLERRP    = DSNX9CAC SQL PROCEDURE DETECTING ERROR

Action taken till now:
1. Cleared the certificate issue (SSL handshake) by adding the required certificate in trust store.
2. Now the connection is established successfully and request hitting my endpoints and endpoint returning java io exception with bad response 400.
3. On checking the endpoint server side, this query giving graphql parse error.
4. I had created one small graphql query and tested in postman to get successful response
5. I had tried to pass it in request body but still getting java io exception with bad response.

Further action  needed:
1. How to pass the graphql query in request body of the httppostclob method. -- need to analyse
2. How to received the CLOB response of the httppostclob method from the endpoint server.-- need to analyse

Shall you look this.

Thanks.                   

Thanks Paolo Salvatore !

I have tried using the above functions for my request getting below error. 

SQL QUERY                                            MODIFIED  LINE    1    
                                                                            
VALUES QSYS2.HTTP_POST                                                      
('HTTPS://xxxxxxxxxxxxxxxxxxxxxxxxxx',                                      
'{"QUERY": "QUERY TEST{PARTS{NAME}}"}',                                     
'{"HEADER":"CONTENT-TYPE,APPLICATION/JSON"')                                
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
QUERY MESSAGES:                                                             
THE SQL STATEMENT IS NOT SUPPORTED.                                         
*** END ***                                                                 
                                                                            
1=Help       2=Run        3=End         4=Print     5=Chart       6=Draw    
7=Backward   8=Forward    9=Form       10=Insert   11=Delete     12=Report  

Since this REST api consuming process is new to me, shall you help here to clear this syntax issue.

What is the table name for above function and do we need to give any select statement. ?
I have tried verifying the same in link which you have given no luck.

Thanks.,

Hi Solairaj,

have you tried the new http sql function? 

Values Http_Post('https://httpbin.org/post', 'your post data', '{"headers":{"accept":"application/json"}}');

Faster, NO java, use DCM for CA cert validation.

https://www.ibm.com/support/pages/node/6486889

You need at least V7R3 at certains TR level.

Bye

Hi Team,

Good morning !

I am executing the below SQL query in IBM DB2 using program DSNTIAUL in JCL.

DB2 function used : DB2XML.HTTPOSTCLOB ( 'url' , 'header' , 'graphql query in request body' )
parameters passed in the function : DB2XML.HTTPOSTCLOB ( 'url' , 'header' , 'graphql query in request body' )
response is CLOB data.


DB2 Query executed:

SELECT DB2XML.HTTPCLOB                                      
('https://xxxxxxxxxx',                      
cAST ('<httpheader><header name="content-type" value='      
!!'"application/json"/><header name="cache-control" '       
!!'value="no-cache"/><header name="authorization" '         
!!'value="yyyy"/>'
!!'</httpheader>' AS CLOB(1K) ),                            
CAST ('{"query": "query Test{parts{name}}"}' as clob(1k)))  
FROM SYSIBM.SYSDUMMY1;      

Response error : 400- bad response.

0 DSNT493I SQL ERROR DURING SQL STATEMENT    FETCH   , TABLE TBLNAME
 DSNT408I SQLCODE = -4302, ERROR:  JAVA STORED PROCEDUE OR USER-DEFINED FUNCTION DB2XML.HTTPPOSTCLOB (SPECIFIC NAME
          DB2XML.HTTPPOSTCLOB) HAS EXITED WITH AN EXCEPTION java.io.IOException: Server returned HTTP response code: 400
          for URL: https:xxxxxxx
 DSNT418I SQLSTATE   = 38000 SQLSTATE RETURN CODE
 DSNT415I SQLERRP    = DSNX9CAC SQL PROCEDURE DETECTING ERROR

Action taken till now:
1. Cleared the certificate issue (SSL handshake) by adding the required certificate in trust store.
2. Now the connection is established successfully and request hitting my endpoints and endpoint returning java io exception with bad response 400.
3. On checking the endpoint server side, this query giving graphql parse error.
4. I had created one small graphql query and tested in postman to get successful response
5. I had tried to pass it in request body but still getting java io exception with bad response.

Further action  needed:
1. How to pass the graphql query in request body of the httppostclob method. -- need to analyse
2. How to received the CLOB response of the httppostclob method from the endpoint server.-- need to analyse

Shall you look this.

Thanks.                   

Based on the error, it seems like you don't have access to the new HTTP functions in QSYS2.
The HTTP_POST call looks like it is prepared correctly.
A quick working example of the QSYS2.HTTP_GET_VERBOSE function is as follows:

SELECT RESPONSE_MESSAGE,
       RESPONSE_HTTP_HEADER
FROM TABLE(QSYS2.HTTP_GET_VERBOSE(
          'https://icanhazdadjoke.com/', --URL
          (VALUES JSON_OBJECT(
              'header'      value 'Accept,application/json'
            ))                             --Header values
          ));



If that query worked, then you should be able to perform the POST using the QSYS2 function.
If the query failed, then you may need to use the HTTP functions in the SYSTOOLS library.

Aside from that, I recommend using the VERBOSE versions of the HTTP functions so that error messages are received.
To receive the error message, you will need to set "includeErrorMsg"  to true in the API header.

-Mike Z

Thanks Paolo Salvatore !

I have tried using the above functions for my request getting below error. 

SQL QUERY                                            MODIFIED  LINE    1    
                                                                            
VALUES QSYS2.HTTP_POST                                                      
('HTTPS://xxxxxxxxxxxxxxxxxxxxxxxxxx',                                      
'{"QUERY": "QUERY TEST{PARTS{NAME}}"}',                                     
'{"HEADER":"CONTENT-TYPE,APPLICATION/JSON"')                                
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
QUERY MESSAGES:                                                             
THE SQL STATEMENT IS NOT SUPPORTED.                                         
*** END ***                                                                 
                                                                            
1=Help       2=Run        3=End         4=Print     5=Chart       6=Draw    
7=Backward   8=Forward    9=Form       10=Insert   11=Delete     12=Report  

Since this REST api consuming process is new to me, shall you help here to clear this syntax issue.

What is the table name for above function and do we need to give any select statement. ?
I have tried verifying the same in link which you have given no luck.

Thanks.,

Hi Solairaj,

have you tried the new http sql function? 

Values Http_Post('https://httpbin.org/post', 'your post data', '{"headers":{"accept":"application/json"}}');

Faster, NO java, use DCM for CA cert validation.

https://www.ibm.com/support/pages/node/6486889

You need at least V7R3 at certains TR level.

Bye

Hi Team,

Good morning !

I am executing the below SQL query in IBM DB2 using program DSNTIAUL in JCL.

DB2 function used : DB2XML.HTTPOSTCLOB ( 'url' , 'header' , 'graphql query in request body' )
parameters passed in the function : DB2XML.HTTPOSTCLOB ( 'url' , 'header' , 'graphql query in request body' )
response is CLOB data.


DB2 Query executed:

SELECT DB2XML.HTTPCLOB                                      
('https://xxxxxxxxxx',                      
cAST ('<httpheader><header name="content-type" value='      
!!'"application/json"/><header name="cache-control" '       
!!'value="no-cache"/><header name="authorization" '         
!!'value="yyyy"/>'
!!'</httpheader>' AS CLOB(1K) ),                            
CAST ('{"query": "query Test{parts{name}}"}' as clob(1k)))  
FROM SYSIBM.SYSDUMMY1;      

Response error : 400- bad response.

0 DSNT493I SQL ERROR DURING SQL STATEMENT    FETCH   , TABLE TBLNAME
 DSNT408I SQLCODE = -4302, ERROR:  JAVA STORED PROCEDUE OR USER-DEFINED FUNCTION DB2XML.HTTPPOSTCLOB (SPECIFIC NAME
          DB2XML.HTTPPOSTCLOB) HAS EXITED WITH AN EXCEPTION java.io.IOException: Server returned HTTP response code: 400
          for URL: https:xxxxxxx
 DSNT418I SQLSTATE   = 38000 SQLSTATE RETURN CODE
 DSNT415I SQLERRP    = DSNX9CAC SQL PROCEDURE DETECTING ERROR

Action taken till now:
1. Cleared the certificate issue (SSL handshake) by adding the required certificate in trust store.
2. Now the connection is established successfully and request hitting my endpoints and endpoint returning java io exception with bad response 400.
3. On checking the endpoint server side, this query giving graphql parse error.
4. I had created one small graphql query and tested in postman to get successful response
5. I had tried to pass it in request body but still getting java io exception with bad response.

Further action  needed:
1. How to pass the graphql query in request body of the httppostclob method. -- need to analyse
2. How to received the CLOB response of the httppostclob method from the endpoint server.-- need to analyse

Shall you look this.

Thanks.