![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Create a UDF using the command line
In the previous section, you generated a UDF to act as Web Service consumer. This was done using the GUI tool provided in WebSphere Application Developer. An alternative to this, is to create the statement to generate the UDF manually and to use the DB2 command line to build the UDF. The first step is to introduce the Web services consumer UDFs and explain their parameters. Then, you'll see how to obtain the correct values for those parameters by going through the WSDL for the 'delayed Stock Quote' Web service, a Web service provided by xmethods.
The
db2xml.soaphttp()is a DB2 UDF that composes a SOAP request, posts the request to the service endpoint, receives the
SOAP response, and returns the content of the SOAP body. The function is overloaded (either
VARCHAR()or
CLOB()
) depending
on the soap body.
db2xml.soaphttpv returns VARCHAR():
db2xml.soaphttpv ( endpoint_url VARCHAR(256),
soap_action VARCHAR(256),
soap_body VARCHAR(3072)) | CLOB(1M))
RETURNS VARCHAR(3072)
|
db2xml.soaphttpc returns CLOB():
db2xml.soaphttpc ( endpoint_url VARCHAR(256),
soapaction VARCHAR(256),
soap_body VARCHAR(3072) | CLOB(1M))
RETURNS CLOB(1M)
|
db2xml.soaphttpcl returns CLOB() as locator:
db2xml.soaphttpcl( endpoint_url VARCHAR(256),
soapaction VARCHAR(256),
soap_body varchar(3072))
RETURNS CLOB(1M) as locator
|
DB2 requires the following information to build a SOAP request and receive a SOAP response:
- Service endpoint, e.g. http://services.xmethods.net/soap/servlet/rpcrouter
- SOAP action URI reference (it is optional and may be null string i.e., '' )
- XML content of SOAP body, which are: name of operation with request namespace URI, encoding style, and input arguments
From WSDL to Web service consumer function
Part of the WSDL for 'Delayed Stock Quote Request' Web service is shown below. It describes the details of the web service interface. It also provides the description such as how to connect to the Web service and invoke the operation.
Below is an extract from the WSDL for the 'Delayed Stock Quote Request' Web service. Analyze the WSDL to find out how to map the elements of this WSDL to parameters of Web service consumer functions. The text in bold are the values used to define the UDF.
<![CDATA[<binding name='net.xmethods.services.stockquote.StockQuoteBinding'
type='tns:net.xmethods.services.stockquote.StockQuotePortType'>
<soap:binding style='rpc'
transport='http://schemas.xmlsoap.org/soap/http'/>
<operation name='getQuote'>
<soap:operation soapAction=]]>
'urn:xmethods-delayed-quotes#getQuote'
<![CDATA[/>
<input>
<soap:body use='encoded' namespace='urn:xmethods-delayed-quotes'
encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'/>
</input>
<output>
<soap:body use='encoded' namespace=]]>
'urn:xmethods-delayed-quotes'
encodingStyle=
'http://schemas.xmlsoap.org/soap/encoding/'
<![CDATA[/>
</output>
</operation>
</binding>
<service name='net.xmethods.services.stockquote.StockQuoteService'>
<documentation>
net.xmethods.services.stockquote.StockQuote web service
</documentation>
<port name='net.xmethods.services.stockquote.StockQuotePort'
binding='tns:net.xmethods.services.stockquote.StockQuoteBinding'>
<soap:address location=]]>
'http://66.28.98.121:9090/soap'
<![CDATA[/>
</port>
</service>
]]> |
Test the Web service consumer functions
Test the Web service consumer functions by accessing the stock quote for IBM:
- Open a DB2 command window.
- Connect to the database that you enabled for SOAP functions (example: SAMPLE).
- Create a file called getquote.sql and copy the following command into the file and save it.
<![CDATA[VALUES db2xml.soaphttpv ( ]]> 'http://66.28.98.121:9090/soap' <![CDATA[, ]]> 'urn:xmethods-delayed-quotes#getQuote' <![CDATA[, varchar ('<stock:getQuote xmlns:stock=]]> " urn:xmethods-delayed-quotes "<![CDATA[ SOAP-ENV:encodingStyle=]]> " http://schemas.xmlsoap.org/soap/encoding/ "<![CDATA[> <symbol xsi:type="xsd:string"> ]]> IBM <![CDATA[</symbol> </stock:getQuote> ' ) );]]>
Using the DB2 command window execute the UDF:
db2 -tvf getquote.sql |
The results should be similar to the following:
<![CDATA[<n:getQuoteResponse xmlns:n="urn:xmethods-delayed-quotes"> <Result xsi:type="xsd:float"> 97.33</Result> </n:getQuoteResponse> ]]> |
Create a wrapper UDF for a Web service
To improve the readability of code, wrap the call to a Web service in another UDF and invoke this UDF every time you want to invoke a Web service operation. In this case, instead of calling
db2xml.sopahttpv(...)every time you make a
stock quote request, create a wrapper UDF that takes only the stock symbol as a parameter and internally calls the
db2xml.soaphttpv(...).
Define the wrapper UDF getStockQuote which takes only one parameter, namely, symbol as a VARCHAR(256) and returns Result
as FLOAT. Below is the code for the getStockQuote UDF. The result of the call to
db2xml.soaphttpvis a CLOB. Hence we
extract the value contained in the Result element pointed to by the XPath
<![CDATA['/*/Result']]>
.
<![CDATA[CREATE FUNCTION ]]>
SchemaName
<![CDATA[.getStockQuote
(symbol VARCHAR(100))RETURNS DECIMAL(5,2) SPECIFIC xmethods_getQuote
LANGUAGE SQL CONTAINS SQL
EXTERNAL ACTION NOT DETERMINISTIC
RETURN
db2xml.extractREAL(
db2xml.xmlclob(
db2xml.soaphttpv(
]]>
'http://66.28.98.121:9090/soap'
<![CDATA[,
]]>
'urn:xmethods-delayed-quotes#getQuote'
<![CDATA[,
varchar('<m:getQuote xmlns:m="]]>
urn:xmethods-delayed-quotes
<![CDATA["
SOAP-ENV:encodingStyle="]]>
http://schemas.xmlsoap.org/soap/encoding/
<![CDATA[">
<symbol xsi:type="xsd:string">
' || ]]>
symbol
<![CDATA[ || '</symbol></m:getQuote>
'
)
)
), '/*/Result'
)]]>
|
This UDF can be invoked in the DB2 command line as follows:
DB2 VALUES
SchemaName.
getStockQuote('IBM')
|
Test the Web service consumer table functions
So far the consumer UDF for Web services returned only a single value. DB2 also allows the writing of UDF's that invoke Web services returning multiple results. In such cases a table with each row containing one result element is returned. For example, take the Web service Xignite News provided by Xignite.com. The WSDL for this is available at http://www.xignite.com/xnews.asmx?WSDL. One of the operations provided by this Web service is GetStockHeadlines. The following UDF shown below, can be used to access this Web service operation.
<![CDATA[CREATE FUNCTION ]]>
SchemaName
<![CDATA[.GetStockHeadlines
(Symbols VARCHAR(100), HeadlineCount INTEGER )
RETURNS TABLE (StockNews VARCHAR(3000))
LANGUAGE SQL CONTAINS SQL
EXTERNAL ACTION NOT DETERMINISTIC
RETURN
Select * from Table (db2xml.extractVarchars(
db2xml.xmlclob(
db2xml.soaphttpc(
'http://www.xignite.com/xnews.asmx',
'http://www.xignite.com/services/GetStockHeadlines',
'<m:GetStockHeadlines xmlns:m="http://www.xignite.com/services/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
'
|| '<m:Symbols>
' || Symbols || '</m:Symbols>
'
|| '<m:HeadlineCount>
' || rtrim(char(HeadlineCount) ) ||
'</m:HeadlineCount>
' || '</m:GetStockHeadlines>
'
)
), '//StockNews/Headline')
) as X ]]> |
In this wrapper UDF, only the Headline elements from the result of the Web service is extracted, a table is constructed from these elements and then the table is returned. You can invoke this UDF as follows:
select * from table (
SchemaName.
GetStockHeadlines('IBM', 10)) as x
By issuing the above query, you should see 10 or less news headlines related to IBM stock as can be seen in the results below.
<![CDATA[STOCKNEWS --------------------------------------------------------------------- IBM and ATB Financial Sign Largest IT Services Agreement of Its Kind in Western Canada [external] IBM Seen Struggling With Growth Problems SCO Escalates Legal Battle on Three Fronts Hewlett-Packard Unveils High-End Copy Machines [external] Oracle Needs to See Beyond PeopleSoft [external] NEWS: Online Extra: What It "Boils Down To" For Kodak Coverage initiated on IBM by Banc of America Sec [external] ONLINE ASIA: An Irresistable Offshore Tide for Jobs [external] Hewlett-Packard Set to Make Amends Sun gives key certification to two small software cos 10 record(s) selected.]]> |
