 | Level: Intermediate Susan Malaika (malaika@us.ibm.com), Senior Technical Staff Member, IBM
Christian Pichler (cpichle@us.ibm.com), Data Server Solutions (Co-op), IBM
01 May 2008 The Universal Services are a set of database operations, including insert, update, delete, and query,
exposed as Web service operations. These services enable someone who has a
pureXML™ column in a DB2® database to set up database Web service operations quickly, for example for prototyping. The services then allow users to query and modify XML data stored in the pureXML column through REST or SOAP, which can serve as a basis for further
development of various applications, such as forms or SOA applications. This article gets you started with configuring, testing, and modifying the Universal
Services. For even more details, see the step-by-step instructions in the readme file that accompanies the Universal Services
download.
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.
Figure
1. Overview of the Universal Services architecture
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.
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.
Figure 2. Content of IRS e-File 1120 download, extracted to local, temporary, working directory
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:
Table 1. Overview on the Sample Table IRS1120
| Column Name | Column Type | Description |
|---|
| ID | INTEGER | Represents a unique ID for each record inserted into the table. |
|---|
| COMMENT | VARCHAR(1000) | Used to store a comment for each record of the table. |
|---|
| DOCUMENT | XML | The third column of this table is the column of type XML, which is used to store XML data. These are in particular,
all IRS e-File 1120 XML documents. |
|---|
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.
Figure 3. Content of Universal Services download package, extracted to local, temporary, working directory
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.
Table 2. Overview of the parameters used in the
configuration script
| Parameter Name | Description | Value (according to the sample database) |
|---|
| DB2INSTALLDIR | Path to the directory of the DB2 installation. | C:\Program Files\IBM\SQLLIB |
|---|
| DBSCHEMA | Name of the database schema that the table containing the XML column belongs to. | IRS1120ADMIN |
|---|
| DBNAME | Name of the database that the Universal Services are used with. | IRS1120 |
|---|
| DBTABLE | Name of the table that contains the XML column. | IRS1120 |
|---|
| DBPRIMARY | Name of the primary column of the table specified in DBTABLE. This column can either be of type INTEGER or VARCHAR. | ID |
|---|
| DBCOMMENT | Name of the comment column of the table specified in DBTABLE. | COMMENT |
|---|
| DBXML | Name of the XML column of the table specified in DBTABLE. This is the column that contains XML data. | DOCUMENT |
|---|
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.
Figure 4. Edit
the configuration file configure.bat
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.
Figure 5. Screenshot of built-in Test Page
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.
Figure
6. Response after Web service operation getPrimaryKeys is invoked
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. To import the Data Development Project, simply select the Import
option of the File pull-down menu in Data Studio.
You can then deploy the modified Universal Services through Data Studio.
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
- Exposing DB2 pureXML using WebSphere Integration Developer
It is hoped that the Universal Services, and the information provided in this article, help you to kick-start your own DB2 pureXML project.
Download | Description | Name | Size | Download method |
|---|
| Universal Services package | UniversalServices.zip | 3.87MB | HTTP |
|---|
More downloads Note - 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.
-
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
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 healthcare. 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 healthcare. |
Rate this page
| |
