IC5Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Topic
  • 4 replies
  • Latest Post - ‏2013-01-09T04:29:03Z by SystemAdmin
SystemAdmin
SystemAdmin
3105 Posts

Pinned topic SOAPHTTPNV on Db2 9.1 zos

‏2013-01-02T16:00:42Z |
I'm calling a webservice from Db2 9.1 on zOS using function SOAPHTTPNV.
The result comes back ok, but the webservice is called multiple times,
once for every row in the resulset (or occurrence of element ROW in the response).
Why is that ?

SELECT T.NR_KAT, T.PNR_F
FROM
XMLTABLE(
xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS
"soap",
'http://schemas/SERVICE/100921' AS "p"),
'$d/soap:Envelope/soap:Body/p:SERVICE_RESPONSE/p:document/p:result/p:ROW'
PASSING XMLPARSE(
DOCUMENT DB2XML.SOAPHTTPNV(
'http://serviceurl',
VARCHAR(''),
VARCHAR('<soap:Envelope
request_simplified
</soap:Envelope>'
))) AS "d"
COLUMNS
NR_KAT VARCHAR(2) PATH 'p:NR_KAT',
PNR_F VARCHAR(12) PATH 'p:PNR_F'
) AS T
;

The result of the SQL in SPUFI

NR_KAT PNR_F
---------+---------+---------+---------+------
09 194513051834
08 194515042978
19 194515300398

The xml response
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body>
<SERVICE_RESPONSE xmlns="http://schemas/SERVICE/100921">
<document>
<result>
<ROW>
<NR_KAT>09</NR_KAT>
<PNR_F>194513051834</PNR_F>
</ROW>
<ROW>
<NR_KAT>08</NR_KAT>
<PNR_F>194515042978</PNR_F>
</ROW>
<ROW>
<NR_KAT>19</NR_KAT>
<PNR_F>194515300398</PNR_F>
</ROW>

</result>
</document>
</SERVICE_RESPONSE>
</Body>
</Envelope>
Updated on 2013-01-09T04:29:03Z at 2013-01-09T04:29:03Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: SOAPHTTPNV on Db2 9.1 zos

    ‏2013-01-05T02:01:25Z  
    since this is a sql function, that is the way it is implemented
    if you want it to only be called once, you will need to have all your data in a single row, and that row will be passed to the service.
    you have several ways to approach this:
    1) write a stored procedure that creates that row
    2) write a user defined table function that return a single row table
    3) use recursive sql to generate that row
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: SOAPHTTPNV on Db2 9.1 zos

    ‏2013-01-05T02:01:26Z  
    since this is a sql function, that is the way it is implemented
    if you want it to only be called once, you will need to have all your data in a single row, and that row will be passed to the service.
    you have several ways to approach this:
    1) write a stored procedure that creates that row
    2) write a user defined table function that return a single row table
    3) use recursive sql to generate that row
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: SOAPHTTPNV on Db2 9.1 zos

    ‏2013-01-07T09:38:48Z  
    since this is a sql function, that is the way it is implemented
    if you want it to only be called once, you will need to have all your data in a single row, and that row will be passed to the service.
    you have several ways to approach this:
    1) write a stored procedure that creates that row
    2) write a user defined table function that return a single row table
    3) use recursive sql to generate that row
    Thanks, but when getting the result from the service as one row, on http-envelope level, strangely enough, the service is called twice. Is there a rational explanation for that ?

    SELECT T.RESULT
    FROM
    XMLTABLE(
    xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS
    "soap",
    'http://schemas/SERVICE/100921' AS "p"),
    '$d/soap:Envelope'
    PASSING XMLPARSE(
    DOCUMENT DB2XML.SOAPHTTPNV(
    'http://serviceurl',
    VARCHAR(''),
    VARCHAR('<soap:Envelope
    request_simplified
    </soap:Envelope>'
    ))) AS "d"
    COLUMNS
    RESULT XML PATH '.'
    ) AS T
    ;

    RESULT
    <?xml version="1.0" encoding="IBM278"?><Envelope xmlns="http://schemas.xmlsoap.o......
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: SOAPHTTPNV on Db2 9.1 zos

    ‏2013-01-09T04:29:03Z  
    Thanks, but when getting the result from the service as one row, on http-envelope level, strangely enough, the service is called twice. Is there a rational explanation for that ?

    SELECT T.RESULT
    FROM
    XMLTABLE(
    xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS
    "soap",
    'http://schemas/SERVICE/100921' AS "p"),
    '$d/soap:Envelope'
    PASSING XMLPARSE(
    DOCUMENT DB2XML.SOAPHTTPNV(
    'http://serviceurl',
    VARCHAR(''),
    VARCHAR('<soap:Envelope
    request_simplified
    </soap:Envelope>'
    ))) AS "d"
    COLUMNS
    RESULT XML PATH '.'
    ) AS T
    ;

    RESULT
    <?xml version="1.0" encoding="IBM278"?><Envelope xmlns="http://schemas.xmlsoap.o......
    i have no idea why the service will be called twice for a single row
    maybe you should contact IBM support