Generate Web services for DB2 9 pureXML

A proof of concept technique to generate bottom-up Web services for XML data

Web services are important building blocks to achieve a service-oriented architecture (SOA). As more and more applications move towards an SOA, often there is a need to expose application functionality as Web services. This article will show you how you can easily generate Web services using a simple Java™class to insert and retrieve XML data, into, and from DB2® 9 using the pureXML™ feature. Once the Web services are generated and deployed on the WebSphere® Application Server, you can test them using either the built-in Web services Explorer of Rational Application Developer (RAD) or XForms as a Web services client. The same services can be used by any Web services client that can make SOAP over HTTP Web service calls.

Share:

Vijay Bommireddipalli (vijayrb@us.ibm.com), Data Servers Solutions, IBM

V. Bommireddipalli photoVijay Bommireddipalli works in the Data Servers Solutions team at IBM's Silicon Valley Lab in San Jose, CA, where he assists customers and partners in developing solutions on DB2. Prior to joining this team, Vijay was a developer on the Warehouse Manager development team. He joined IBM in July 2000, after finishing his Masters degree in Electrical and Computer Engineering at University of Massachusetts - Dartmouth.



14 June 2007

Also available in Chinese Vietnamese

Background

Nowadays, Web services are an important component of an SOA and allow for a highly decoupled and flexible solution. Web services can be built using either a top-down or bottom-up approach. In a top-down approach, one would start with a service specification (for example, a WSDL file ) and then implement the underlying code to match that specification. In a bottom-up approach, one would start with a Java bean and expose the functionality as Web services.

IBM introduced the pureXML feature in DB2 9 that allows XML data to be stored natively in the database. It makes XML a first-class datatype in DB2, and a column of type XML can store any well-formed XML in its native hierarchical form within the database. Once the data is stored in the database as XML, the data can be queried very efficiently, as the XML is not parsed at runtime for query evaluation. You can then use XQuery, SQL, or any of the application development interfaces that DB2 offers, to access and manipulate the XML data.

XForms is an XML-based electronic forms technology that can submit form data as an XML data instance. It is a W3C recommendation and is platform and device independent. XForms separates the data model from the presentation, which allows the user data to be submitted as XML. The recipient of this data can then store and manipulate the user submitted data as an XML instance. There are many ways in which the data can be submitted using XForms. In this example, let's use Web services.

Using XForms for the end user form, Web services as a transport mechanism, and DB2 9 for storage, you can create a true end-to-end XML solution that is resilient to change and significantly reduces development time.

Check the Resources section for more detailed information related to DB2 9, XForms, and Web Services.

Prerequisites

The following prerequisites are necessary to better understand the information presented in this article:

  • Familiarity with DB2 9 and the pureXML feature. Check the Resources section of this article to become familiar with DB2 9's pureXML features.
  • Working knowledge of RAD and the ability to deploy and remove applications using the test WebSphere Application Server environment
  • Conceptual familiarity with Web services development

System requirements

Server side development

  1. DB2 9 with pureXML

    You can also use DB2 Express-C, which is free to use. DB2 Express-C can be run on up to two dual-core CPU servers, with up to 4 GB of memory, any storage system setup and with no restrictions on database size or any other artificial restrictions. Check the resources section for links to download Express-C.

  2. RAD Version: 7.0.0 (or later)

    Note: This example details the steps to test the outcome, using the built-in test WebSphere Application Server Version 6.1.0.2 that comes with RAD. You can choose to export the WAR file and deploy onto a standalone WebSphere Application Server (version 6.1.0.2 and later). The example will fail on lower levels of WebSphere Application Server.

Client side

  • Mozilla Firefox 1.5.x or later (preferably Firefox 2.0.x) with the XForms plugin.

    The XForms plugin can be installed from the Firefox Add-ons page. To install the XForms plugin, launch this url using Firefox 2.0.x, and select Install now.


Procedure

Summary

This example walks you through the generation of Web services for XML data using a bottom-up approach. The implementation of a Java bean (FormServices.java) is provided as a starting point. This Java bean contains data access and manipulation methods, for manipulating data in an XML column of the DB2 9 sample database. Check out the sidebar titled "FormServices.java explained" for a detailed explanation of the content of the Java bean. You will then expose these methods in the Java bean as Web services using the RAD tooling. Finally, you will test these Web services, using the Xforms provided.

Let's take it step-by-step

  1. Create sample database and xmlcustomer table
    1. Create a sample database of codepage UTF8 (if it does not already exist), for example:
       db2 create db sample using codeset UTF8 territory US

      DB2 9 uses the UTF8 database codepage in order to create XML datatype columns.

    2. At a DB2 command prompt, create the xmlcustomer table using the following commands:
      connect to sample
      CREATE TABLE XMLCUSTOMER( CID  INT NOT NULL PRIMARY KEY, INFO  XML )

      The XML column INFO can store any well-formed XML.

      You can try a test insert by typing the following at the DB2 command prompt :

      insert into xmlcustomer values ( 9999, '<test/>');
  2. Launch RAD and create a "Dynamic Web Project."
    1. Make sure you are in the Web perspective. (If not, select Window > Open Perspective > Web to get there.)
    2. Enter "XForm" as the name of the project (case sensitive).
    3. Use the defaults and click Finish.
Figure 1. Create a Web project
Web Project settings
  1. Import the Java source code file provided and make the necessary changes
    1. Expand the XForm project created above until you see the Java Resources folder.
    2. Right-click and select New > package. Specify "com.ibm.test" as the name of the package and click Finish.
Figure 2. Create a package
How to create a Package
  1. Right-click on the package just created and select Import > File System and import FormServices.java (the Java source file provided).
Figure 3. Import the sample code FormServices.java
Importing the java source

FormServices.java explained:

The Java source code FormServices.java consists of two classes:

  • Class DB:

    This class handles database connectivity. Note that you have to make changes specific to your environment for this to run.

  • Methods:

    connect: Allows the application code to connect to the DB2 database. The connect method uses the type 4 Java Database Connectivity (JDBC) universal driver.

    disconnect: Disconnects the application from the database and releases JDBC resources.

  • Class FormServices:

    This class has methods to manipulate data in the database and retrieve data to be displayed in the form. These methods are exposed as Web services. There are three basic methods that allow manipulation of XML data. The same three methods are repeated to allow the XML data to be treated as strings. They are as follows:

  • Methods:

    insertFormData: This method takes the input XML data and stores it in the XML column of a DB2 database. Note that the XML input should be XML data (for example, not escaped strings like &lt;).

    retrieveFormData: This method retrieves the form data stored in an XML column based on a unique key.

    retrieveFormIDs: This method retrieves the form ids and returns them XML-suitable to the XForm. You can use this method to generate a drop-down menu within a form.

    The three methods *AsString() are essentially the same methods as above, but instead of transferring XML data on the wire, XML is converted into a string. So <hello/> becomes “&lt;hello/&gt;”. These convenience methods are provided so that you can use the built-in Web service explorer for testing it.

    genSOAPElement: This is a helper method used to transform the payload from a string to a SOAPElement.

    genStringfromSOAP: This is a helper method used to transform the payload from a SOAPElement to a string.

    1. Search for the following pattern //Change and make changes if necessary, per your environment. Comments in the code will help guide you through the changes.
    2. If you follow this example as presented, the only required change would be the userid and password in the Database class, which is also marked by the pattern //Change (as above).
    3. Make sure the code compiles cleanly and all the imports are resolved. This requires you to add db2jcc.jar to the Java Build Path of the XForm project.
    4. To add the external jar db2jcc.jar, right-click on the project and select Properties > Java Build Path > Add External Jars > and select db2jcc.jar from the DB2 installation (\sqllib\java).
    Expose the compiled Java code as a Web service
    1. Right-click on the FormServices.java, then click on Web Services > Create Web Service. If you do not see a Web Services menu, read on to see how to enable it.
    2. If you do not see a Web Services menu. You may have to enable the Web services capability of your workbench. To do that, click on Windows > Preferences > Workbench > Capabilities and check the Web Service Developer to see it is enabled.
Figure 4. Generate Web services from Java source
Generating Web services from java bean
  1. Verify that the settings are as shown below. Click OK on the warning issued.

    Note: When the services are generated, the tooling maps various Java datatypes to its equivalent XSD types as defined in the JAX-RPC specification. The IBM WebSphere SOAP engine maps the javax.xml.soap.SOAPElement datatype in the Java bean to the XML schema xsd:anyType in the WSDL. However, the JAX-RPC specification currently does not define the standard Java mapping for the xsd:anyType. A JAX-RPC implementation is not required to support the xsd:anyType. This example may not work on other SOAP engines and has not been tested on any other SOAP engine.

Figure 5. Review Web services settings
Review Web services settings
  1. Click Next until you see a menu to choose the services that need to be enabled. Make sure all the services are checked to be exposed as a Web service, and then click Finish.

    Note: Click OK to the warning issued (see the Note above for an explanation).
Figure 6. Select methods to expose as Web services
Select methods to expose as Web services
  1. Click Start Server if you are prompted to do so. The generated services will be published on the built-in WebSphere Application Server test server.
  1. Running XForms on the built-in test WebSphere Application Server
    1. Prior to testing the XForms application, ensure the required JAR files installed are the correct version for using the JDBC type 4 driver to connect to DB2.
      1. Go to your installed WebSphere Application Server directory and check that the following files are there: db2jcc.jar, db2jcc_license_cu.jar. (For instance, in this example case, I had to install these JAR files under "C:\Program Files\IBM\SDP70\runtimes\base_v61\lib'".)
      2. If the JAR files are not there, copy the JAR files over and make sure to restart the WebSphere Application Server system server.
    2. Prior to running XForms with the Web services created in the previous step, you need to install the SOAPForwarding servlet on the same WebSphere Application Server. This file (SOAPResolution.war) is provided as part of the download included in the Downloads section of this article.

      This step is merely a stop gap arrangement as Mozilla's XForms Add-on currently does not make valid SOAP calls. This step is not necessary once this defect is fixed in a later release of the Mozilla XForms Add-on. When this article was written, the Web service call using Firefox Xforms Version 0.7.0.1 was missing the SOAPAction header. The SOAPForwarding servlet just adds this SOAPAction header that is needed.

      1. Right-click on XformEAR and select Import > war file.
      2. Browse the file system to select the SOAPResolution.war file provided. The Web project name should auto-fill as shown below. Make sure that it is added to the existing XFormEAR project.
Figure 7. SOAPResolution WAR
Import SOAPForwarding Servlet
  1. Click Finish to import the servlet.
  1. Test the Web services using RAD's built-in Web Services explorer
    1. Now that you have deployed and started the application successfully, it's time to test the deployed services.
    2. Right-click on the generated FormServices.wsdl in RAD and choose Test with Web services explorer.
  1. Specify the endpoint, as shown, with the correct port number and try out any of the methods that end with *AsString. . In the sample, there are three methods which end with *AsString that return XML as escaped strings. Most browsers prefer XML as escaped string (&lt; instead of “<” ). However, XForms prefer real XML values. The other three methods (without *AsString ) are identical in function to those with *AsString, but they return real XML data.
Figure 8. Testing the *AsString services using the Web services explorer
Test the Web services using Web services Explorer
  1. Test the XML Web services using XForms
    1. Launch the XForms provided with the demo.

      Right-click on the xmlcustomer.xhtml file provided and select Open with Firefox. This step assumes that you have the XForms plugin installed as specified in the Prerequisites section of this article. You should see the XForm as shown in the screen capture below.

    2. Make sure you specify a unique "Id Val" and enter/modify the name, and so on as necessary.
    3. Using the xmlcustomer Form, specify a unique ID in the Id Val field and press Submit.

      The Id Val specified should be unique to the CID column of XMLCUSTOMER table in DB2.

    4. When you press Submit, the XForm makes a Web service call to the insertFormData service, to insert the user data of the form as an XML instance into DB2.

      If there is an error, or if nothing happens, see the "Troubleshooting tips" section for possible causes and suggestions.

Figure 9. Testing the insert service using an xform
Test the Web services using an Xform

Troubleshooting tips

XForms:

If you press "Submit" and nothing happens:

  • On Firefox, go to Tools > Java script console to see if there are any errors outside of stylesheet errors.
  • If you see the XForms Error (14): Security check failed! Trying to submit data to a different domain than document error:

    Mozilla's XForms extension does not allow cross domain submission by default. Check the following url for additional details. To enable cross domain submission, find a file called "hostperm.1" and add this line to the file "host xforms-xd 3 scheme:file". (You should see other lines similar to this.) My hostperm.1 was under the C:\Documents and Settings\Administrator\Application Data\Mozilla\Firefox\Profiles\m6g6ho0d.default directory, but your path might be different. Then, restart your browser.

WebSphere Application Server:

If the XForm.war application does not start, check the system logs. On Microsoft® Windows systems, the default location is C:\Program Files\IBM\SDP70\runtimes\base_v61\profiles\AppSrv01\logs\server1.

Advanced troubleshooting:

A good way to figure out whether you're dealing with a server problem or a client problem is to see what is being sent to the WebSphere Application Server from the xform over the wire. You can do this using a TCP monitor like https://tcpmon.dev.java.net/.

Redirect the XForms submit action to a new port number where the TCP monitor is running and have the TCP monitor direct the incoming message to the server.

For example:

Run the TCP monitor at localhost:8080 with the server pointing to localhost:9081. Now change all occurrences of localhost:9081 in the sample xform provided to localhost:8080. This way, all submissions will be sent through the TCP monitor, and the monitor will display what is being sent.

Make sure your firewall is not blocking any communication.

  1. If there is no error, you should see the retrieve form. Specify the same ID as specified on insert and press Retrieve.
  2. This time, the xform makes a Web service call to the retrieveFormData service, to retrieve the user data from DB2 as an XML instance and maps it to the xform.
Figure 10. Testing the retrieve service using an xform
Test the Web services using an Xform

Conclusion

You just saw how to generate bottom-up Web services for XML data in DB2 pureXML. These services can store and retrieve XML data into and from DB2 9 by way of SOAP over HTTP Web services. The services can insert and retrieve XML as escaped strings or as actual XML on the wire. You can modify the Java source to suit your specific needs, and any of those methods can be exposed as Web services. These Web services can serve as endpoints to any client that can make SOAP over HTTP calls to retrieve and store data to a database.


Acknowledgements

Thanks to Michael Schenker, Keith Wells, Jan Kratky, Cindy Saracco, Sal Ledezma, and Matthias Nicola for all their help with this article and their reviews.


Download

DescriptionNameSize
Sample java code and Xformjavaxformsample.zip10KB

Resources

Learn

Get products and technologies

Discuss

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, XML, SOA and web services, Rational
ArticleID=230912
ArticleTitle=Generate Web services for DB2 9 pureXML
publish-date=06142007