Best practices for using InfoSphere Federation Server to integrate web service data sources

An introduction to the InfoSphere Federation Server Web services wrapper

This article introduces the overall architecture of the Web services wrapper of the IBM® InfoSphere® Federation Server. It explains how to integrate data from web service providers by web service nicknames step-by-step. This article also introduces some of the restrictions of the Web services wrapper.

Share:

Chen Wang (wangccdl@cn.ibm.com), Staff Software Engineer, IBM

  Chen WangChen Wang is a staff software engineer at the IBM China Software Development Lab in Beijing. He currently works for the development team on the IBM InfoSphere Federation Server product. He has more experience on the wrapper development and configuration. He has been with IBM for more than five years and has written for developerWorks previously.



Zan Zhou (zhouzan@cn.ibm.com), Staff Software Engineer, IBM

Zan ZhouZan Zhou is a staff software engineer at the IBM China Software Development Lab in Beijing. He currently works for the development team on the IBM InfoSphere Federation Server product. He has more experience on the wrapper development and configuration.



15 August 2013

Also available in Chinese

Introduction

With data virtualization, you can access information through a common interface that centralizes the control of data access. Using IBM InfoSphere Federation Server, you can virtualize your data and provide information in a unique form. IBM InfoSphere Federation Server provides an optimized and transparent data access and transformation layer with a single relational interface across all enterprise data. With a federated system, you can send distributed requests to multiple data sources within a single SQL statement. For example, you can join data from an IBM DB2® table, an Oracle table, and a web service in a single SQL statement.

This article introduces the overview objects of InfoSphere Federation Server and explains when you can use the Web services wrapper. It uses The North American Industry Classification System (NAICS) web service as an example to give you step-by-step instructions on integrating web services data to DB2 by using InfoSphere Federation Server. It also details some of the restrictions of the Web services wrapper.

Figure 1. Framework of InfoSphere Federation Server and the Web services wrapper
Overall framework of InfoSphere Federation Server and Web services wrapper

Key terminology

You should familiarize yourself with the key terminologies in InfoSphere Federation Server, shown in the hierarchy of federated objects:

Wrapper: Implemented as a set of library files. Wrappers are used by federated servers to define how to communicate with and retrieve data from remote data sources. The Web services wrapper uses SOAP 1.1 protocol to access the web service providers.

Server: Identifies a data source in the federated database. Web service server defines a method to access web services data sources like using a proxy server.

User mapping: The association between the authorization ID at the federated server and the authorization ID at the data source. User mappings are optional for the Web services wrapper and are only needed when the web service uses HTTPS as a transport protocol.

Nickname: Identifiers that reference an object that you want to access at a data source. For web service nicknames, it usually refers to an operation service that defines a WSDL document.


Integrate web service data provided by NAICS with the Web services wrapper

Figure 2 shows what you can retrieve from the NAICS web service with Federation Server:

Figure 2. Sample NAICS information retrieved by the Web services wrapper
Sample NAICS information retrieved by Web services wrapper

Listings 1 and 2 take the GetNAICSByID operation of the NAICS web service as an example to introduce how to integrate public web services data to DB2 by using the Federation Server Web services wrapper.

The Web services wrapper only supports the SOAP 1.1 protocol, so focus on the SOAP 1.1 message structure located here: GetNAICSByID WSDL description.

Listing 1. Request SOAP message sample
POST /GenericNAICS.asmx HTTP/1.1
Host: www.webservicex.net
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://www.webservicex.net/GetNAICSByID"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetNAICSByID xmlns="http://www.webservicex.net/">
      <NAICSCode>string</NAICSCode>
    </GetNAICSByID>
  </soap:Body>
</soap:Envelope>
Listing 2. Response SOAP message sample
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
               xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
               xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetNAICSByIDResponse xmlns="http://www.webservicex.net/">
      <GetNAICSByIDResult>boolean</GetNAICSByIDResult>
      <NAICSData>
        <Records>int</Records>
        <NAICSData>
          <NAICS>
            <NAICSCode>string</NAICSCode>
            <Title>string</Title>
            <Country>string</Country>
            <IndustryDescription>string</IndustryDescription>
          </NAICS>
          <NAICS>
            <NAICSCode>string</NAICSCode>
            <Title>string</Title>
            <Country>string</Country>
            <IndustryDescription>string</IndustryDescription>
          </NAICS>
        </NAICSData>
      </NAICSData>
    </GetNAICSByIDResponse>
  </soap:Body>
</soap:Envelope>

The Web services wrapper and web service provider are communicated by SOAP messages. The wrapper first translates the SQL from the user application to a request SOAP message. Then it sends the SOAP message embedded in an HTTP request to a web location associated with that service. The web service provider performs the request for that message and then returns the output data in the form of another SOAP message embedded in an HTTP response. Last, the wrapper resolves the SOAP message and returns the data as a uniform DB2 view to the user application. Figure 3 shows the overall process of the Web services wrapper:

Figure 3. Data flow of the Web services wrapper
Data flow of the Web services wrapper

Let's begin our tutorial.

Register the Web services wrapper and server

You must register a wrapper and a server to access web services data sources.

CREATE WRAPPER WS LIBRARY 'db2ws.dll';
CREATE SERVER MYWSSERVER WRAPPER WS;

The sample is based on Federation Server for Windows® Operating System; change db2ws.dll to another corresponding library name if you are on other platforms.

Register the web service nickname according to the key elements in WSDL

Creating the nickname manually is a bit complex. First, find useful parts from the WSDL document and sample SOAP messages for the operation GetNAICSByID. According to the request and response SOAP message samples, the input parameter is string type NAICSCode and output parameters are boolean type GetNAICSByIDResult and complex type NAICSData.

Listing 3 shows the nickname DDL:

Listing 3. Nickname DDL sample
CREATE NICKNAME LUNAR.GENERICNAICSSOAP_GETNAICSBYID_NN ( 
GETNAICSBYID_NAICSCODE VARCHAR(10) ---> input
  OPTIONS(TEMPLATE '<ns1:NAICSCode>&column</ns1:NAICSCode>'), 
GETNAICSBYIDRESULT VARCHAR(5) ---> output
  OPTIONS(XPATH './ns1:GetNAICSByIDResponse/ns1:GetNAICSByIDResult/text()'), 
PARAMETERS_NAICSDATA_RECORDS INTEGER   ---> output
  OPTIONS(XPATH './ns1:GetNAICSByIDResponse/ns1:NAICSData/ns1:Records/text()'),
PARAMETERS_NAICSDATA_NAICSDATA_CODE VARCHAR(10)  ---> output
  OPTIONS(XPATH './ns1:GetNAICSByIDResponse/ns1:NAICSData
          /ns1:NAICSData/ns1:NAICS/ns1:NAICSCode/text()'), 
PARAMETERS_NAICSDATA_NAICSDATA_TITLE VARCHAR(50) ---> output
  OPTIONS(XPATH './ns1:GetNAICSByIDResponse/ns1:NAICSData
          /ns1:NAICSData/ns1:NAICS/ns1:Title/text()'), 
PARAMETERS_NAICSDATA_NAICSDATA_COUNTRY VARCHAR(20) ---> output
  OPTIONS(XPATH './ns1:GetNAICSByIDResponse/ns1:NAICSData
          /ns1:NAICSData/ns1:NAICS/ns1:Country/text()'), 
PARAMETERS_NAICSDATA_NAICSDATA_INDUSTRYDESCRIPTION VARCHAR(500) ---> output
  OPTIONS(XPATH './ns1:GetNAICSByIDResponse/ns1:NAICSData
          /ns1:NAICSData/ns1:NAICS/ns1:IndustryDescription/text()') 
)  
FOR SERVER MYWSSERVER  
OPTIONS(
URL 'http://www.webservicex.net/GenericNAICS.asmx' , 
SOAPACTION 'http://www.webservicex.net/GetNAICSByID' , 
TEMPLATE '<soapenv:Envelope>
<soapenv:Body>
<ns1:GetNAICSByID>&GetNAICSByID_NAICSCode[0,1]</ns1:GetNAICSByID>
</soapenv:Body>
</soapenv:Envelope>' , 
XPATH '/soapenv:Envelope/soapenv:Body' , 
NAMESPACES ' ns1="http://www.webservicex.net/" ,
soapenv="http://schemas.xmlsoap.org/soap/envelope/" ');

Write this DDL according to the NAICS WSDL for this sample.

You can divide the DDL into three parts: nickname options, input columns, and output columns.

Nickname options

The SOAPACTION nickname option, shown in Listing 4, specifies the soapAction attribute of soap operation from the WSDL document:

Listing 4. SOAPACTION nickname option
SOAPACTION nickname option:
SOAPACTION 'http://www.webservicex.net/GetNAICSByID'

WSDL part:
<wsdl:binding name="GenericNAICSSoap" type="tns:GenericNAICSSoap">
<soap:binding transport="http://schemas.xmlsoap.org/soap/http"/>
<wsdl:operation name="GetNAICSByID"> 
<soap:operation style="document" 
soapAction="http://www.webservicex.net/GetNAICSByID"/>
<wsdl:input><soap:body use="literal"/></wsdl:input>
<wsdl:output><soap:body use="literal"/></wsdl:output> 
</wsdl:operation>
</wsdl:binding>

The XPATH nickname option specifies the XPATH expression that identifies the SOAP response elements that represent individual tuples. The XPATH expression is used as a context for evaluating the column value that the XPATH nickname column options identify. It usually uses "/soapenv:Envelope/soapenv:Body".

XPATH nickname option:
XPATH '/soapenv:Envelope/soapenv:Body'

The TEMPLATE nickname option, shown in Listing 5, contains the complete SOAP envelope that is specified for the web service. You can reference to the request sample soap message.

GetNAICSByID_NAICSCode is the name of input column in nickname DDL. There are two values [0,1] and [1,1] that can be appended to the column name. [0,1] means this column is optional, while [1,1] means it is a required column.

Listing 5. TEMPLATE nickname option
TEMPLATE nickname option:
TEMPLATE '<soapenv:Envelope>
<soapenv:Body>
<ns1:GetNAICSByID>&GetNAICSByID_NAICSCode[1,1]</ns1:GetNAICSByID>
</soapenv:Body>
</soapenv:Envelope>'

WSDL part:
<wsdl:portType name="GenericNAICSSoap">
<wsdl:operation name="GetNAICSByID">
<wsdl:documentation xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">
    Get NAICS details by NAICS code</wsdl:documentation>
<wsdl:input message="tns:GetNAICSByIDSoapIn"/>
<wsdl:output message="tns:GetNAICSByIDSoapOut"/>
</wsdl:operation>
</wsdl:portType>
... ...
<wsdl:message name="GetNAICSByIDSoapOut"> 
<wsdl:part name="parameters" element="tns:GetNAICSByIDResponse"/> 
</wsdl:message>

Sample request soap message:
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                  xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                  xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetNAICSByID xmlns="http://www.webservicex.net/">
      <NAICSCode>string</NAICSCode>
    </GetNAICSByID>
  </soap:Body>
</soap:Envelope>

The NAMESPACES nickname option, shown in Listing 6, is a comma-separated list of name-value pairs that specifies the namespaces that are associated with the namespace prefixes that are used in the XPATH and TEMPLATE options for each column:

Listing 6. NAMESPACES nickname option
NAMESPACES nickname option:
NAMESPACES ' ns1=http://www.webservicex.net/,
soapenv=http://schemas.xmlsoap.org/soap/envelope/ '

WSDL part:
<wsdl:definitions xmlns:wsdl=http://schemas.xmlsoap.org/wsdl/ 
    targetNamespace="http://www.webservicex.net/"  ... >
... ...
<wsdl:types> 
<s:schema targetNamespace="http://www.webservicex.net/" 
             elementFormDefault="qualified"> 
<s:element name="GetNAICSByID"> ... ...
<s:element name="GetNAICSByIDResponse"> ... ...

The URL nickname option, shown in Listing 7, specifies the URL for the web service endpoint. Supported protocols are HTTP and HTTPS. It usually specifies the location attribute of the soap address in the WSDL document.

Listing 7. URL nickname option
URL nickname option:
URL 'http://www.webservicex.net/GenericNAICS.asmx'

WSDL part:
<wsdl:service name="GenericNAICS"> 
... ...
<wsdl:port name="GenericNAICSSoap" binding="tns:GenericNAICSSoap"> 
<soap:address location="http://www.webservicex.net/GenericNAICS.asmx"/> 
</wsdl:port> 
</wsdl:service>

Input columns

The TEMPLATE column option, shown in Listing 8, specifies that a column is an input column. When the TEMPLATE nickname option contains a bracketed notation ([1,1]), the column is a required input column. If the input column is required, you must issue the query with an equality predicate. Column names must be part of TEMPLATE nickname option.

Listing 8. Input columns
Input columns:
GETNAICSBYID_NAICSCODE VARCHAR(10)  ---> input
OPTIONS(TEMPLATE '<ns1:NAICSCode>&column</ns1:NAICSCode>'),  

WSDL parts:
<s:element name="GetNAICSByID">
... ...
<s:element name="NAICSCode" type="s:string" maxOccurs="1" minOccurs="0"/> 
... ...

Sample request soap message:
... ...
<GetNAICSByID xmlns="http://www.webservicex.net/">
 <NAICSCode>string</NAICSCode>
</GetNAICSByID>

The request soap message is composed of all input column TEMPLATE values to the placeholders in the nickname TEMPLATE option.

If the nickname TEMPLATE option value is the following:

TEMPLATE '<soapenv:Envelope><soapenv:Body>
<ns1:GetNAICSByID>
<ns1:NAICSCode>&GetNAICSByID_NAICSCode[1,1]</ns1:NAICSCode>
</ns1:GetNAICSByID>
</soapenv:Body></soapenv:Envelope>'

The column TEMPLATE option value can be:

TEMPLATE '&column'

Output columns

The XPATH column option specifies that a column is an output column. The XPATH expressions are applied on the XML document that is returned from the web service. The Web services wrapper resolves the response soap message according to both XPATH expressions in the nickname option and column options.

Listing 9. Output columns
Output columns:
GETNAICSBYIDRESULT VARCHAR(5) 
OPTIONS(XPATH './ns1:GetNAICSByIDResponse/ns1:GetNAICSByIDResult/text()'),
PARAMETERS_NAICSDATA_RECORDS INTEGER   
OPTIONS(XPATH './ns1:GetNAICSByIDResponse/ns1:NAICSData/ns1:Records/text()'),

WSDL parts:
<s:element name="GetNAICSByIDResponse">
... ...
<s:element name="GetNAICSByIDResult" type="s:boolean" maxOccurs="1" minOccurs="1"/> 
<s:element name="NAICSData" type="tns:NAICSList" maxOccurs="1" minOccurs="1"/> 
... ...
<s:complexType name="NAICSList">
... ...
<s:element name="Records" type="s:int" maxOccurs="1" minOccurs="1"/> 
<s:element name="NAICSData" type="tns:ArrayOfNAICS" maxOccurs="1" minOccurs="0"/>
... ...

Sample response soap message:
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                  xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                  xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetNAICSByIDResponse xmlns="http://www.webservicex.net/">
      <GetNAICSByIDResult>boolean</GetNAICSByIDResult>
      <NAICSData>
        <Records>int</Records>
        <NAICSData>
... ...

Now, you have created the nickname successfully. Next, you use it to access the web service:

db2 => SELECT * FROM LUNAR.GENERICNAICSSOAP_GETNAICSBYID_NN 
          WHERE GETNAICSBYID_NAICSCODE  = '112120';
GETNAICSBYID_NAICSCODE GETNAICSBYIDRESULT PARAMETERS_NAICSDATA_RECORDS 
PARAMETERS_NAICSDATA_NAICSDATA_CODE PARAMETERS_NAICSDATA_NAICSDATA_TITLE               
PARAMETERS_NAICSDATA_NAICSDATA_COUNTRY 
PARAMETERS_NAICSDATA_NAICSDATA_INDUSTRYDESCRIPTION
---------------------- ------------------ ---------------------------- 
112120                 true                                          1 
112120                 Dairy Cattle and Milk Production                   
-                      
This industry comprises establishments primarily engaged in milking dairy cattle.
  1 row returned.

How to get multiple rows of data by the Web services wrapper

We get the expected result when specifying a precise NAICSCODE 112120, which means only one row returned. If the result contains multiple rows, the Web services wrapper needs child nicknames to resolve repeating elements.

The following shows the nickname hierarchies according to the type definition in the WSDL document:

<s:element name="GetNAICSByIDResponse">
<s:element name="GetNAICSByIDResult"
<s:element name="NAICSData" type="tns:NAICSList" maxOccurs="1" minOccurs="1"/> 
......
<s:complexType name="NAICSList">
<s:element name="Records" type="s:int" maxOccurs="1" minOccurs="1"/>
<s:element name="NAICSData" type="tns:ArrayOfNAICS" maxOccurs="1" minOccurs="0"/> 
......
<s:complexType name="ArrayOfNAICS">
<s:element name="NAICS" type="tns:NAICS" maxOccurs="unbounded" minOccurs="0"/> 
......
<s:complexType name="NAICS">
<s:element name="NAICSCode" type="s:string" maxOccurs="1" minOccurs="0"/>
<s:element name="Title" type="s:string" maxOccurs="1" minOccurs="0"/>
<s:element name="Country" type="s:string" maxOccurs="1" minOccurs="0"/> 
<s:element name="IndustryDescription" type="s:string" maxOccurs="1" minOccurs="0"/>
Figure 4. Parent -> Child -> nickname hierarchies according to the type definition in WSDL
Parent -> Child -> nickname hierarchies according to the type definition in WSDL
Listing 10. Create parent nickname - GENERICNAICSSOAP_GETNAICSBYID_NN
CREATE NICKNAME LUNAR.GENERICNAICSSOAP_GETNAICSBYID_NN (
GETNAICSBYID_NAICSCODE VARCHAR (10) 
OPTIONS(TEMPLATE '<ns1:NAICSCode>&column</ns1:NAICSCode>'),  ---> input
GETNAICSBYIDRESULT VARCHAR (5) 
OPTIONS(XPATH './ns1:GetNAICSByIDResponse/ns1:GetNAICSByIDResult/text()'),
PARAMETERS_NAICSDATA_RECORDS INTEGER 
OPTIONS(XPATH './ns1:GetNAICSByIDResponse/ns1:NAICSData/ns1:Records/text()'),
NN_PKEY VARCHAR (16) NOT NULL 
OPTIONS(PRIMARY_KEY 'YES')
)  
FOR SERVER MYWSSERVER  OPTIONS(
URL 'http://www.webservicex.net/GenericNAICS.asmx' , 
SOAPACTION 'http://www.webservicex.net/GetNAICSByID' , 
TEMPLATE '<soapenv:Envelope><soapenv:Body><ns1:GetNAICSByID>
&GetNAICSByID_NAICSCode[0,1]</ns1:GetNAICSByID></soapenv:Body></soapenv:Envelope>' , 
XPATH '/soapenv:Envelope/soapenv:Body' , 
NAMESPACES ' ns1="http://www.webservicex.net/" ,  
soapenv="http://schemas.xmlsoap.org/soap/envelope/" ');
Listing 11. Create child nickname - GENERICNAICSSOAP_GETNAICSBYID_NAICS_NN
CREATE NICKNAME LUNAR.GENERICNAICSSOAP_GETNAICSBYID_NAICS_NN ( 
NN_FKEY VARCHAR (16) NOT NULL 
OPTIONS(FOREIGN_KEY 'GENERICNAICSSOAP_GETNAICSBYID_NN'),
PARAMETERS_NAICS_NAICSCODE VARCHAR (10) 
  OPTIONS(XPATH './ns1:NAICSCode/text()'),
PARAMETERS_NAICS_TITLE VARCHAR (50) 
  OPTIONS(XPATH './ns1:Title/text()'),
PARAMETERS_NAICS_COUNTRY VARCHAR (20) 
  OPTIONS(XPATH './ns1:Country/text()'),
NAICS_INDUSTRYDESCRIPTION VARCHAR (10000) 
  OPTIONS(XPATH './ns1:IndustryDescription/text()'))  
FOR SERVER MYWSSERVER  OPTIONS(
XPATH './ns1:GetNAICSByIDResponse/ns1:NAICSData/ns1:NAICSData/ns1:NAICS' , 
NAMESPACES ' ns1="http://www.webservicex.net/" , 
soapenv="http://schemas.xmlsoap.org/soap/envelope/" ');

The PRIMARY_KEY and FOREIGN_KEY columns define the relationship between the parent and the child nicknames. Each parent nickname must have a primary key column option. You define the children of a parent nickname with the foreign key column option that references the primary key column of the parent nickname. A nickname can have more than one child nickname, but a child nickname can have only one parent nickname.

The following statement is a typical query that you might issue on the nicknames to access the GetNAICSByID web service. After you issue this statement, you can retrieve the information from the GetNAICSByID based on a specific identifier on which the primary and foreign keys of the child nickname NAICS data match. See the results in Figure 2.

db2 => SELECT PARAMETERS_NAICS_NAICSCODE, PARAMETERS_NAICS_TITLE, 
          PARAMETERS_NAICS_COUNTRY FROM GENERICNAICSSOAP_GETNAICSBYID_NAICS_NN, 
          LUNAR.GENERICNAICSSOAP_GETNAICSBYID_NN 
          WHERE GETNAICSBYID_NAICSCODE = '1121%' AND NN_PKEY = NN_FKEY;

Conclusion

This article explains how InfoSphere Federation Server integrates web service data into DB2. It gives you a feasible way to access data that is not directly accessible from the system, such as the mainframe. With the best practices in this article, you can easily utilize the data from public or private web services. For more information, refer to the InfoSphere Federation Server V10.1 Information Center (see Resources).

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=940426
ArticleTitle=Best practices for using InfoSphere Federation Server to integrate web service data sources
publish-date=08152013