Introduction

Universal Services are a simple but fixed set of database operations that allow the querying and modification of XML data, stored in a pureXML column of a DB2 database. These database operations include insert, update, delete, and query data exposed as Web Services through the Data Web Service mechanism.

Data Web Services allow database operations, such as insert or update, to be exposed as Web Services without coding. Data Web Services also allow stored procedures or user-defined functions to be exposed as Web Services. The development of Data Web Services is already covered in other articles and tutorials on developerWorks. A list of relevant tutorials and articles is provided in the Resources section.

Figure 1 provides an overview of the architecture for Universal Services.

The goal of the Universal Services is to enable any pureXML column in a DB2 database to be queried and modified through Web service operations, which are made available through straightforward configuration and deployment steps. These steps are described in the following sections of this article. If you need more details on these steps than provided here, refer to the readme file of the Universal Services download package for fully illustrated step-by-step instructions.

This article covers the configuration, deployment, and modification of the Universal Services on a sample database and is therefore split into the following three sections.

• Set up a sample database: Describes the sample database set up, which is then used in this article to demonstrate the configuration of the Universal Services
• Install the Universal Services: Describes the configuration and deployment of the Universal Services
• Modify the Universal Services: Describes how to change the Universal Services to suit your environment, if required

Details of where you can find the necessary downloads for each step are included in the appropriate sections of this article.

Prerequisites

In order to install and run the Universal Services, you need to have DB2 9.x, Java Runtime Environment 1.5.0, and a Web server (such as WebSphere® Application Server V6.x or Apache Tomcat V6.x), installed on your system. Note that the Java Runtime is part of the DB2 installation.

You also need to obtain two different downloads, which are the IRS e-File 1120 package, as part of the Industry Formats and Services online demonstration, and the Universal Services package itself. You can find the links to these downloads in the Download section of this article. These downloads are necessary to follow the steps provided in this article.

Back to top

Set up a sample database

The Universal Services can be set up on any pureXML column of a DB2 database. In order to demonstrate the configuration and installation of these services, the following section shows how to set up a sample database, which is used throughout the article. The sample database created here is based on the free and publicly available downloads as part of the DB2 pureXML online demonstration called Industry Formats and Services with pureXML.

Each of these downloads contain a set of scripts that enable users to easily set up a database designed for a specific industry, such as financial or health care. The script used in this article creates the sample database and a table containing a pureXML column, and then loads sample data into the database. The downloads include more scripts, such as XML schema registration, the validation of XML instance documents against registered XML schemas, and querying XML data using XQuery or SQL/XML, which may be of interest, but are not referred to in this article. If more information on these industry-specific downloads is needed, refer to the Resources section of this article.

Create the sample database

This article is based on the IRS e-File 1120 download, which is the electronic version of the IRS Tax Form 1120. This section assumes that you downloaded and extracted the IRS e-File 1120 package into a local, temporary, working directory. The local, temporary, working directory for the database scripts used throughout this article, is c:\temp\irs1120.

The name of the script that performs the set up of the sample database on your system is "start.bat" (Linux users: "start.sh"). In order to execute this script, open the DB2 command line processor, change the directory to your current working directory that contains the content of the file you downloaded. This directory also contains the file start.bat, which you are about to execute. Therefore, go ahead and execute the start.bat script.

Once the script is finished, you should see a message indicating that the setup of the database is done. If the message indicates anything different, consult the readme file included in the IRS e-File 1120 download. Assuming that the setup went well, you now have a DB2 sample database setup on your system that contains a pureXML column and sample data loaded into the pureXML column. To be more specific, the table in the database containing the pureXML column has the following structure:

Based on the sample database, it is now possible to show how to configure and install the Universal Services for a specific pureXML column, which is the subject of following sections.

Install the Universal Services

This section assumes that you downloaded and extracted the Universal Services package into a local, temporary, working directory. The local, temporary, working directory for the Universal Services used throughout this article is c:\temp\UniversalServices.

Once the necessary files and scripts are available on your system, you can continue with the installation of the Universal Services, which basically consists of three steps. These steps, described in more detail below, show how to:

• Configure the Universal Services to fit your local system setup
• Deploy the Universal Services on your application server
• Test the Universal Services using a built-in test page

Configure the Universal Services

The configuration of the Universal Services is achieved through a script provided with the download called "configure.bat" (Linux users: "configure.sh"). However, before executing the script it needs to be adjusted to fit your local system setup, as listed in Table 2.

Note that the three parameters in Table 2, DBPRIMARY, DBCOMMENT, DBXML, are the same parameters in Table 1.

Edit the Universal Services configuration file to correspond to the information in Table 2. For example set DBNAME=SAMPLEDB needs to be modified to set DBNAME=IRS1120. Figure 4 shows the configuration file, after it was edited according the values provided in Table 2.

After editing the configure.bat (Linux users: configure.sh) it is now possible to execute the script that performs the actual configuration of the Universal Services, such as adjusting the column name. The script then creates a Web application archive (WAR) file, that contains all necessary files for the Universal Services Web application, and which can be deployed to your application server. Remember, that the script must be executed on the DB2 command line processor, otherwise the execution fails.

Deploy the Universal Services

Editing and executing the script configure.bat, as described in previous section, created a WAR file that can now be deployed to your application server. Prior to the deployment of the WAR file to the application server, it is necessary to configure a data source on the application server, which enables the Universal Services to access your DB2 database.

In order to set up the data source, follow the standard configuration process of your application server. The only requirement to be considered during setup, is the name of the data source, which must meet following pattern: jdbc/[database name]. The data source name for the example used in this article is jdbc/irs1120. If any further information is needed on how to set up a data source, refer to the manual of your application server or read the readme file included in the Universal Services download package.

The second step is to deploy the actual WAR file onto the application server. However, it needs to be stated that due to differences in the Web service artifacts required by either WebSphere Application Server V6.x and Apache Tomcat V6.x, the configuration script created two separate WAR files, one for each of the application servers listed.

The WAR files created can be found in the subdirectory war, located in the Universal Services working directory c:\temp\UniversalServices. In particular, the directory war contains two subdirectories: tomcat and was. The directory tomcat contains the WAR file to be installed on Apache Tomcat V6.x, whereas the directory was contains the WAR file to be installed on WebSphere Application Server V6.x. In order to install the WAR file on your application server, follow the standard procedure to do so. If more information is required, refer to the manual of your application server, or to the readme file included in the Universal Services download package.

It is now possible to test your set up with the Universal Services test page, which is already available through the deployment of the WAR file. To proceed with the test, continue with the next section.

Test the Universal Services

In order to ensure that the setup and configuration of the Universal Services on your local system was successful, these services need to be tested. The easiest way to do so is to use a simple HTML page, already available on your application server, as part of the deployment of the Universal Services.

The Universal Services test page can be accessed by redirecting your browser to the URL http://localhost:8080/UniversalServices/testServices.html. Note that, depending on your local system set up, hostname, or port may need to be adapted.

This simple test page allows you to insert, update, delete, and query XML data of your own database through the Universal Services. The Web services are invoked through the buttons provided on the test page. Figure 6 shows the Web service response. After the Web service operation, getPrimaryKeys was invoked through the button provided on the test page.

Modify the Universal Services

If you need to modify the Universal Services, for example to introduce a new parameter, you can import the Universal Services Data Development Project supplied with the Universal Services Data Project download into Data Studio. The following sections describe how to import the project into Data Studio and how to configure the imported project in Data Studio (for example, adjust the database connection or adjust the database schema names). In order to follow the steps outlined below, launch Data Studio.

Import Universal Services Data Development Project

Once Data Studio is launched, right-click in the white area within the Data Project Explorer, a submenu appears. In the submenu, choose the Import, as shown in Figure 7.

The Import Wizard will then open in a separate window, as shown in Figure 8. In the first step of the Import Wizard, select the Existing Projects into Workspace, which is found in the group called "General." Click Next to continue.

The next step is to identify the archive file that represents the Universal Services Data Development Project. To do so, select Select archive file. Then, click Browse and select the archive file. The archive file is called universal_services_data_studio_project.zip, and is found in the local, temporary working directory that you extracted the Universal Services package into. The local, temporary working directory used in this article is c:\temp\UniversalServices.

After the archive file is selected, the Import Wizard lists all available projects within the archive, as shown in Figure 9. Since the archive file provided in the download contains only the Data Development Project for the Universal Services, only one project is listed in the Projects section. Note that in case you already have an existing project in your workspace that has the same project name ("services") as the Universal Services, the Import Wizard does not list the project in the Projects section. Therefore, the Import Wizard does not allow you to import the Universal Services Data Development project.

After the archive file and projects are selected, click Finish to finalize the import process. After the import has finished, you can see the imported Universal Services Data Development project in the Data Project Explorer, as shown in Figure 10.

Configure database connection

After the Universal Services Data Development Project is imported into your workspace, it is necessary to configure the database connection. Figure 10 shows the project using a database connection named "Universal Services Database Connection." Moreover, you can see that the connection refers to a database named SAMPLEDB, on the localhost, on port 50000. In order to adjust the database connection details to fit your local configuration, right-click the database connection Universal Services Database Connection, which you can find in the Database Explorer, and select Edit Connection, as shown in Figure 11.

A separate window opens that allows you to adjust the database connection details, such as database name, hostname, port number, user information, and many more different parameters, as shown in Figure 12. Note that you can also test the configuration details on-the-fly by clicking Test Connection.

Linux users: If you import the Data Development Project into Data Studio on Linux, the parameter Class location:, which specifies the location of the DB2 database driver, needs to be adjusted. The database driver is located in the java directory of your DB2 installation directory (for example, /home/db2inst1/sqllib/java).

Configure database schema name

All SQL operations that are exposed as Web service operations, and which you previously imported into your workspace, use fully qualified SQL statements. The latter means that the database schema name is, for example, used to qualify table names (such as SELECT ID FROM IRS1120ADMIN.IRS1120).

An alternative approach is to not encode database schema names in SQL statements but use a default database schema name instead. One way to adjust the database schema name is to access and modify the properties for the Universal Services Data Development Project. To do so, right-click the Universal Services Data Development Project and choose Properties (as shown in Figure 13).

The Properties window for the Universal Services Data Development Project opens. After clicking Development in the menu located on the left side of the window, you can configure the database schema name that is used in SQL statements.

Further considerations

Once the imported project is adjusted to fit you local system configuration, you can then modify and deploy the modified Universal Services through Data Studio. Note one operation, namely runxqueryXML requires a user defined function to be registered. To do so, an SQL script named "createXQueryXMLUDF.sql" is provided in the Data Studio Development Project.

Further resources on how to create and expose new operations using the Data Studio can be found in the Resources section of this article.

Additional options

When customizing database operations to include input and output parameters, parameter markers are used. The Universal Services support two kinds of parameter markers. The first alternative is to use the parameter marker "?", leaving the associated parameter name to default to an automatically generated name, such as "p1" or "p2". The second alternative is to use the parameter marker ":" and an associated character string that identifies the parameter, such as ":id".

To better illustrate the two alternatives, the following example is provided. The example is based on a database operation of the Universal Services that deletes a record from the table. Both operations listed below have the same purpose but utilize the different kinds of parameter markers:

• Alternative 1: DELETE FROM DB2ADMIN.SAMPLETABLE WHERE ID = ?
• Alternative 2: DELETE FROM DB2ADMIN.SAMPLETABLE WHERE ID = :id

Based on the database operations, a Web Service Description Language (WSDL) file is generated, for example, through Data Studio. The WSDL file describes the Web service operations, including their parameters. The generated WSDL file depends on the kind of parameter markers used in the database operations. For Alternative 1, the parameter names in the Web service operations are the automatically generated parameter names, for example, "p1" or "p2" (see Listing 1).

Listing 1. Excerpt from WSDL that was generated based on Alternative 1

...
<element name="deleteDocument">
  <complexType>
    <sequence>
      <element name="p1" nillable="true" type="xsd:string"/>
    </sequence>
  </complexType>
</element>
<element name="deleteDocumentResponse">
  <complexType>
    <sequence>
      <element name="updateCount" type="xsd:int"/>
    </sequence>
  </complexType>
</element>
...

For Alternative 2, the parameter names in the Web service operations use the same character strings as specified in the database operations, for example, "id" or "document" (see Listing 2).

Listing 2. Excerpt from WSDL that was generated based on Alternative 2

...
<element name="deleteDocument">
  <complexType>
    <sequence>
      <element name="id" nillable="true" type="xsd:string"/>
    </sequence>
  </complexType>
</element>
<element name="deleteDocumentResponse">
  <complexType>
    <sequence>
      <element name="updateCount" type="xsd:int"/>
    </sequence>
  </complexType>
</element>
...

The package used as a basis for this article utilizes Alternative 1. However, the download package accompanying this article also contains another Universal Services package and Data Development project that utilize Alternative 2. If you prefer to use Alternative 2, find the archive file called universal_services_alternative_2.zip, as shown in Figure 15, within the download accompanying this article. You can then use the content of the archive file as a basis to configure, deploy, test, and modify the Universal Services, as described in this article.

Conclusion and outlook

This article demonstrates the simplicity and ease of exposing a pureXML column of a DB2 database through Web service operations, using scripts without launching a separate tool. The installation of the Universal Services can be used to build your own prototypes or applications.

The following three examples already use the Universal Services as a basis for their work:

• Online, interactive, demonstration for Industry Formats and Services with pureXML
• "Using industry standard data formats with WebSphere ESB and DB2 Version 9 pureXML" (developerWorks, June 2007)
• "Exposing DB2 Version 9 pureXML using WebSphere Integration Developer" (developerWorks, September 2007)
• "XForms and DB2 pureXML" (developerWorks, May 2008)

It is hoped that the Universal Services, and the information provided in this article, help you to kick-start your own DB2 pureXML project.

Back to top

Download

Information about download methods

More downloads

• Code sample: IRS e-File 1120¹

Note

1. Available as part of the Industry Formats and Services with pureXML online demonstration — select Download now.

Resources

Learn

• Industry Formats and Services with pureXML: Download a great variety of examples, for free! Each example illustrates how to work with XML-based Industry Formats and pureXML. The examples show how to register an XML Schema, how to perform validation of XML instance documents, how to query XML data using XQuery or SQL/XML and much more.
  
  
• "IBM Data Studio: Get Started with Data Web Services" (developerWorks, November 2007): Provides a very detailed and simple introduction on how to develop your first Data Web Service.
  
  
• "Data Web Services: Build Web Services the new way to access IBM database servers" (developerWorks, December 2007): Create and customize a Data Web Service. Useful theoretical background on Data Web Services is provided, which includes an architectural overview on Data Web Services. The article addresses different aspects of Data Web Services, such as Security.
  
  
• "Exposing DB2 Version 9 pureXML using WebSphere Integration Developer" (developerWorks, September 2007): Build a client to access the Universal Services.
  
  
• "Using industry standard data formats with WebSphere ESB and DB2 Version 9 pureXML" (developerWorks, June 2007): Find a scenario that shows how using WebSphere Enterprise Service Bus and DB2 9 pureXML can help a pharmaceutical company satisfy legal obligations for exposing standardized data about its products.
  
  
• "XForms and DB2 pureXML" (developerWorks, May 2008): Learn how easy it is to create XForms and have them communicate with a DB2 database, where XML data can be stored, retrieved, or deleted. Also, see how to create the XForms that access the DB2 pureXML through Universal Services.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• IBM Data Studio: Download the development environment used to develop Data Web Services, for free.
  
  
• DB2 Express-C: Download the free version of DB2, which includes the core functionality as the other Data Servers, such as the pureXML technology. DB2 Express-C is free to develop, deploy and distribute.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

Susan Malaika is a senior technical staff member in IBM's Information Management Group (part of IBM Software Group). Her specialties include XML, the Web, and databases. She has developed standards that support data for grid environments at the Global Grid Forum. She has also co-authored a book on the Web and published articles on transaction processing and XML. She is a member of the IBM Academy of Technology.

Christian Pichler is a co-op from the Technical University of Vienna in Austria, where he is working on his thesis for a double Master's degree in Computer Engineering and Computer Science with a focus on health care. For IBM, Christian is working on technologies for storing XML in DB2, and accessing it through Web services, feeds, and XForms. He is specializing in XML standards for health care.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Prerequisites
• Set up a sample database
• Install the Universal Services
• Modify the Universal Services
• Additional options
• Conclusion and outlook
• Download
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers