Db2

Db2

Where DBAs and data experts come together to stop operating and start innovating. Connect, share, and shape the AI era with us.


#Data


#Data
#Databases
#Operatingsystems
#Db2
#Databasesolutions
 View Only

  • 1.  DB2 Http function HTTPPOSTCLOB

    Posted 04/13/23 02:47 AM

    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.                   



    ------------------------------
    Solairaj Jayaraj
    ------------------------------


  • 2.  RE: DB2 Http function HTTPPOSTCLOB

    Posted 04/14/23 08:12 AM

    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



    ------------------------------
    Paolo Salvatore
    ------------------------------



  • 3.  RE: DB2 Http function HTTPPOSTCLOB

    Posted 04/17/23 11:04 AM

    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.,




    ------------------------------
    Solairaj Jayaraj
    ------------------------------

    Original Message


  • 4.  RE: DB2 Http function HTTPPOSTCLOB

    Posted 04/18/23 01:06 PM

    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



    ------------------------------
    Mike Zaringhalam
    ------------------------------

    Original Message


  • 5.  RE: DB2 Http function HTTPPOSTCLOB

    Posted 05/04/23 07:20 AM

    Thanks Mike for your response.

    I have tried the systools function and its working fine now.

    SELECT DB2XML.HTTPPOSTCLOB                             
    ('endpoint details',                
     CAST ('<httpHeader><header name="Content-Type" value='
     !!'"application/json"/>'                              
     !!'<header name="Authorization" value="'              
     !!'password"'             
     !!'/></httpHeader>' AS CLOB(1K)),                     
     CAST ('{"query": "query abc{parts{name}}"}'          
      AS CLOB(1K))                                         
    )                                                      
    FROM sysibm.sysdummy1;                                 



    ------------------------------
    Solairaj Jayaraj
    ------------------------------

    Original Message