Universal Services for pureXML using Data Web Services

Easily enable your pureXML column to be accessed through Web service operations

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.

Susan Malaika (malaika@us.ibm.com), Senior Technical Staff Member, IBM

Susan MalaikaSusan 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, Data Server Solutions (Co-op), IBM

Christian PichlerChristian 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.



19 August 2008 (First published 01 May 2008)

Also available in Russian Vietnamese

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
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

Download DB2 Express-C for FREE

Go ahead and download and use a fully-functional relational and XML data server at no charge.

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
Content IRS e-File 1120 download

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 NameColumn TypeDescription
IDINTEGERRepresents a unique ID for each record inserted into the table.
COMMENTVARCHAR(1000)Used to store a comment for each record of the table.
DOCUMENTXMLThe 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
Content Universal Services Download Package

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 NameDescriptionValue (according to the sample database)
DB2INSTALLDIRPath to the directory of the DB2 installation.C:\Program Files\IBM\SQLLIB
DBSCHEMAName of the database schema that the table containing the XML column belongs to.IRS1120ADMIN
DBNAMEName of the database that the Universal Services are used with.IRS1120
DBTABLEName of the table that contains the XML column.IRS1120
DBPRIMARYName of the primary column of the table specified in DBTABLE. This column can either be of type INTEGER or VARCHAR.ID
DBCOMMENTName of the comment column of the table specified in DBTABLE.COMMENT
DBXMLName 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
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
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
Reponse 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. 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.

Figure 7. Click Import to load the Universal Services Data Development Project
Click Import to load the Universal Services Data Development Project

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.

Figure 8. Import Wizard -- Select import source
Import Wizard -- select source for import

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.

Figure 9. Import Wizard -- Select archive file
Import Wizard -- Select archive file

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.

Figure 10. Imported Data Development Project in the Data Project Explorer
Imported Data Development Project in the Data Project Explorer

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.

Figure 11. Access database connection details through Database Explorer
Access database connection details through Database Explorer

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).

Figure 12. Database connection details
Database connection details

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).

Figure 13. Access Data Development Project properties
Access Data Development Project properties

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.

Figure 14. Adjust database schema name
Adjust database schema name

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.

Figure 15. Content of Universal Services download package, extracted to local, temporary, working directory
Content of Universal Services download package, extracted to local, temporary, working directory

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:

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

DescriptionNameSize
Universal Services packageUniversalServices.zip8MB

Resources

Learn

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

More downloads

  • Code sample: IRS e-File 1120 (Available as part of the Industry Formats and Services with pureXML online demonstration — select Download now.)

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, SOA and web services, XML, Java technology
ArticleID=305877
ArticleTitle=Universal Services for pureXML using Data Web Services
publish-date=08192008