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.
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
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:
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. 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
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
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
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
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
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
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
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
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
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.
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
"?", leaving the associated parameter name to default
to an automatically generated name, such as
"p2". The second alternative is to use the parameter marker
":" and an associated character string
that identifies the parameter, such as
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,
"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,
"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
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.
|Universal Services package||UniversalServices.zip||8MB|
- 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.
- Participate in the discussion forum.
- Participate in developerWorks blogs and get involved in the developerWorks community.
- Code sample: IRS e-File 1120 (Available as part of the Industry Formats and Services with pureXML online demonstration — select Download now.)