DB2 XML Extender overview

DB2 XML Extender provides the ability to store and access XML documents, and also to compose XML documents from existing relational data or to decompose XML documents into relational data. It does so by providing a set of new data types, functions, and stored procedures for DB2 Universal Database. With the DB2 XML Extender, you can use SQL as the main access method for your XML documents, which makes your task much easier. The XML Extender also facilitates your administration tasks by providing you with a GUI-based wizard, a set of administrative stored procedures, and an Administration command.

Storage and retrieval

With DB2 XML Extender you can either store an entire XML document in DB2 as a user-defined data type, or map the XML content into columns in DB2 tables. DB2 XML Extender can also store the XML document as an external file in the file system, with a pointer to that file stored in the DB2 database, so that you can keep working with your legacy flat files but benefit from the advantages offered by DB2 at the same time. DB2 XML Extender provides powerful capabilities to search the XML elements and attributes which can also be combined with the structural and full text search capabilities of DB2 Text Extender (see http://www-3.ibm.com/software/data/db2/extenders/text/index.html).

Integrating XML documents in DB2

DB2 XML Extender provides two options you can use for integrating XML documents into DB2:

• XML Columns: This method allows you to store XML documents in DB2. The documents are inserted into columns which are enabled for XML, and can be updated, retrieved, and searched. Element and attribute data can be mapped to DB2 tables called side tables, which can then be indexed for fast searches.
• XML Collections: This method allows you to map XML document structures to DB2 tables, so that you can compose XML documents from existing DB2 data, or decompose XML documents into DB2 data.

Document type definition (DTD) repository

DB2 XML Extender provides an XML DTD repository. When a database is enabled for XML, a DTD reference table called DTD_REF is created. Each row in this table contains a DTD with additional metadata information about it. You can insert your own DTDs into this table. The DTDs in this table are used to validate the XML documents.

Document access definition

DB2 XML Extender also provides you with a mapping scheme called document access definition (DAD), which is a file used to map the XML document to relational data. The DAD is an XML formatted document which allows you to associate XML document structure to a DB2 database when using either XML Columns or XML Collections. The structure of DAD files is different when using an XML Column or an XML Collection. DAD files are managed using the XML_USAGE table that is created when the database is enabled.

Location path

A location path is a sequence of XML tags separated by a forward slash (/) that identifies an XML element or attribute. Location paths are used in the following situations within DB2 XML Extender and DB2 Text Extender: They are given as input to extracting UDFs to identify elements and attributes to be extracted. They are used to specify the mapping file between an XML element or attribute and a DB2 column when defining the indexing scheme in the DAD for XML Columns They are used by the Text Extender for structural-text search. Listing 1 shows location path syntax supported by DB2 XML Extender:

/
Represents the XML root element. 

/tag1
Represents the element tag1 under root. 

/tag1/tag2/..../tagn
Represents an element with the name tagn as the child of the descending chain 
from root, tag1, tag2, through tagn-1. 

//tagn
Represents any element with the name tagn, where double slashes(//) denote 
zero or more arbitrary tags. 

/tag1//tagn
Represents any element with the name tagn, a child of an element with the name
tag1 under root, where double slashes (//) denote zero or more arbitrary tags. 

/tag1/tag2/@attr1
Represents the attribute attr1 of an element with the name tag2, which is a 
child of element tag1 under root. 

/tag1/tag2[@attr1="5"]
Represents an element with the name tag2 whose attribute attr1 has the 
value 5. tag2 is a child of element with the name tag1 under root. 

/tag1/tag2[@attr1="5"]/.../tagn
Represents an element with the name tagn, which is a child of the descending 
chain from root, tag1, tag2, through tagn-1, where the attribute attr1 of tag2 
has the value 5. 

Install XML Extender

DB2 XML Extender Version V7.1 requires DB2 Universal Database V6.1 or higher and is available on AIX, Sun Solaris, Linux, Windows NT and Windows 2000. DB2 XML Extender contains the following components: XML Extender Server, XML Extender run-time client, XML Extender software developer's kit (SDK), samples and online documentation. To install DB2 XML Extender on Windows NT, follow these steps:

1. Load the DB2 XML Extender CD.
2. Read the readme file under D:\db2xml\yourPlatform, where D:\ is the drive used for the CD and your platform is either AIX, NT or Solaris, depending on your platform.
3. To install DB2 XML Extender on Windows NT, execute the setup.exe file located in the directory D:\db2xml\nt.
4. Choose the desired setup language.
5. Select the product you want to install: DB2 XML Extender Server or DB2 XML Extender Client. Select XML Extender Server if you are installing DB2 XML Extender on a server platform. This option also installs the XML Extender Client component. Select XML Extender Client if you are installing DB2 XML Extender in a client environment.
6. Select one of the three installation types: Typical, Compact, or Custom (Typical is recommended).
7. Leave the default destination location C:\dxx, or choose another folder to install DB2 XML Extender.
8. Leave the default Program Folder DB2 XML Extender or choose another one.
9. Shut down and restart the system once the selected components have been installed.

Verify installation

To check the installation, you must create a database, bind it to XML Extender and CLI, and run the installation check program.

1. Create a database. You can use the DB2 Control Center or use the CREATE DATABASE MYDB command in the Command Center or a Command Line Processor window, where MYDB is the name of the database you want to use for XML Extender.
2. After creating the database, bind it as follows:
   
   1. In a DB2 Command Window, connect to the database by entering: db2 connect to mydb.
   2. Change to the BND subdirectory in the directory where you installed XML Extender. For example, if you installed XML Extender in C:\DXX, change to C:\DXX\BND with the command: cd /d C:\DXX\BND.
   3. Bind the database to XML Extender by entering: db2 bind @dxxbind.lst.
   4. Change to the BND sub directory in the directory where you installed DB2 with the command: cd /d %DB2PATH%\bnd.
   5. Bind the database to CLI by entering: db2 bind @db2cli.lst.
   6. Disconnect from the database by entering: db2 terminate.
3. After binding the database, change back to the base directory where you installed XML Extender; for example, C:\DXX by typing: cd /d C:\DXX.
4. Run the installation check program, dxxinstallchk.cmd by entering: <![CDATA[ dxxinstallchk mydb > out 2>&1 ]]> where mydb is your database name and out is the name of an output file. The output should be similar to the content of the verification file, dxxinstallchk.vfy.

For instance, with the user xmlext and the database xmltest, we get the following output file:

DXXA002I Connecting to database xmltest.
DXXA005I Enabling database xmltest. Please wait.
DXXA006I The database xmltest was enabled successfully.
Database Connection Information
Database server = DB2/NT 7.1.0
SQL authorization ID = XMLEXT
Local database alias = XMLTEST
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DXXA002I Connecting to database xmltest.
DXXA000I Enabling column Order. Please Wait.
DXXA022I Column Order enabled.
DXXA002I Connecting to database xmltest.
DXXA008I Disabling column Order. Please Wait.
DXXA034I XML Extender has successfully disabled column Order.
DB20000I The SQL command completed successfully.
DXXA002I Connecting to database xmltest.
DXXA035I XML Extender is disabling database xmltest. Please wait.
DXXA036I XML Extender has successfully disabled database xmltest.
DB20000I The TERMINATE command completed successfully.

Enable the database for XML

The XML enablement of the database is the first step that is required to store XML information in a database. Enabling a database creates:

• The db2xml schema (also assigns the needed privileges)
• The user-defined types (UDT) XMLVARCHAR, XMLCLOB, XMLFILE, and many user-defined functions (UDF)
• The necessary control tables required by DB2 XML Extender, for example, DTD_REF and XML_USAGE.

You can enable the database for XML in one of the following ways:

• The Administration command dxxadm, which has various command options, depending on the action you want to execute. To enable a database, the command is: dxxadm enable_db mydb, where mydb is the name of the database you want to enable.
• The Administration Wizard. Click on Start -> Programs -> DB2 XML Extender -> XML Extender Administration Wizard.
• The Administration stored procedures invoke dxxEnableDB().

XML columns method

With the XML Columns method, once the document is stored, you have a whole range of possibilities for working with the XML document: You can extract and search XML elements and attributes in the XML document directly, or, for improved performance, you can store in side tables the elements and attributes you are likely to access more often. DB2 Text Extender can be used to extend your search capabilities to perform structural and full text search into the XML document. You have the option to update the content of an XML element or the value of an XML attribute.

The XML document can also by retrieved and used by your XML application. Using the XML Columns method allows you to store the entire XML document, as it is, in a column. Choosing the XML Columns method is best if one or more of the following criteria can be met:

• The XML documents already exist; for example, you want to archive documents such as newspaper articles, orders, and so on.
• The XML documents are read-often and update-rarely.
• The performance of the update is not critical.
• You want to store the XML documents intact.
• You want to keep the XML documents externally from DB2 on local file systems.
• You know what elements or attributes will be frequently searched. To perform efficient searches on these documents, you can decide to create indexes in side tables on the elements or attributes that you need to access more often.

XML columns method example

Let's look at an example showing how employee data could be manipulated.

<?xml version="1.0"?>
<!DOCTYPE person SYSTEM "c:\person.dtd">
	<person id="1">
           	<firstName>John</firstName>
           	<lastName>Smith</lastName>
            <country>US</country>
            <phone>
             <type>office</type>
             <number>12345678</number>
            </phone>
            <phone>
             <type>home</type>
             <number>34567890</number>
            </phone>
	</person>

<?xml version="1.0"?> 
<!ELEMENT person (firstName,lastName,country,phone*)> 
<!ATTLIST person id CDATA #REQUIRED > 
<!ELEMENT firstName (#PCDATA )> 
<!ELEMENT lastName (#PCDATA )> 
<!ELEMENT country (#PCDATA )> 
<!ELEMENT phone (type,number)> 
<!ELEMENT type (#PCDATA )> 
<!ELEMENT number (#PCDATA )>

Next you need an XML column to store the XML file. DB2 XML Extender provides you with three new user-defined types located in the db2xml schema to store your XML documents as column data:

• XMLVarchar: You can store an XML document in the database, with a maximum size of 3 KB.
• XMLCLOB: The XML document is also stored in the database, but its maximum size is 2 GB.
• XMLFILE: This UDT allows you to keep the document on the local file.

For instance, the following SQL statement would create a table, person, containing one XML Column of type XMLCLOB:

create table person(id integer not null primary key, 
 person db2xml.xmlclob notlogged);

Then register your DTD file if you plan on validating XML files being stored. You need to register your DTD within DB2 XML Extender. To do that, you must store your DTD file in the DTD_REF table created in DB2XML schema during the enablement of the database, and associate this file with an unique identifier: the DTD ID. You have two possibilities to insert a DTD in the DTD_REF table:

• Use the GUI XML Extender Administration Wizard
• Use an INSERT SQL statement to insert the DTD file as a XMLCLOB data type in the DTD_REF with the user-defined function XMLClobFromFile provided by DB2 XML Extender. For example, if you want to insert the DTD, person.dtd, located in the directory, C:\TEMP, and associate this DTD with the unique identifier: C:\TEMP\person.dtd, the SQL statement would be as follows:

insert into db2xml.dtd_ref(dtdid, content, usage_count, author, creator, updator) 
    values('C:\TEMP\person.dtd',db2xml.XMLClobFromFile
     ('C:\TEMP\person.dtd'),0, 'xmlext','xmlext','xmlext');

Create a Document Access Definition (DAD) file, itself an XML document, that specifies how the XML documents that you store in the database are to be handled. In the case of XML Columns, you need the DAD file only if you want to validate your XML documents before storing them, or if you want to index elements or attributes in side tables.

<?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd">
<DAD> 3
<dtdid>C:\TEMP\person.dtd</dtdid>
<validation>YES</validation>
<Xcolumn>
<table name="person_names">
	<column name="fname" type="varchar(50)" path="/person/firstName" 
	multi_occurrence="NO"/>
	<column name="lname" type="varchar(50)" path="/person/lastName" 
	multi_occurrence="NO"/>
</table>
<table name="person_phone_number">
	<column name="pnumber" type="varchar(20)" path="/person/phone/number" 
	multi_occurrence="YES"/>
</table>
<table name="person_phone_number">
	<column name="pnumber" type="varchar(20)" path="/person/phone/number" 
	multi_occurrence="YES"/>
</table>
<table name="person_phone_type"> 
	<column name="ptype" type="varchar(20)" path="/person/phone/type" 
	multi_occurrence="YES"/>
</table>
</Xcolumn>
</DAD>

For XML Columns, you need to enable the column if you want to have validation or side tables.

dxxadm enable_column xmltest person person c:\TEMP\personColumn.dad -r id

For XML Columns, here are sample SQL statements which access the data:

insert into person values(1,db2xml.xmlclobfromfile('c:\TEMP\person.xml'));

select pnumber from person_names t1, person_phone_number t2, 
  person_phone_type t3 where t1.id = t2.id and t1.id = t3.id 
  and t2.dxx_seqno = t3.dxx_seqno and t1.lname='Smith' and t3.ptype='office';

select lname, fname from person1 t1, person_names t2 where t1.id = t2.id 
  and db2xml.extractVarchar(person, '/person/country') = 'US';

update person set person = db2xml.XMLCLOBFromFile('c:\TEMP\personbis.xml');

select db2xml.Content(person, 'c:\temp\retrievedperson.xml') from person where id = 1;

XML Collections method

With the XML Collections method, you have these possibilities:

• You can populate the columns of one or more DB2 tables starting from an XML document. The tags in the XML document and the XML document itself are not stored in DB2; only the values of the elements and attributes you are interested in are stored in DB2. The mapping between elements and attributes in the XML document and the DB2 tables is given by the DAD file.
• You can create an XML document using the data in columns of one or more DB2 tables. DB2 XML Extender composes an XML document using the data stored in columns of DB2 tables. Here again, the mapping between data in the DB2 tables and the composed XML document is done using the DAD file.
• You can work with the data shredded (or decomposed) from the XML document as you would work with any relational data in DB2.

Using the XML Collections method allows you to map XML document structures to DB2 tables, so that you can compose XML documents from existing DB2 data or decompose XML documents into DB2 tables. Use XML Collections in the following situations:

• You have data in your existing relational tables and you want to compose XML documents based on a certain DTD.
• You have XML documents that need to be stored with collections of data that map well to relational tables.
• You want to create different views of your relational data using different mapping schemes.
• You have XML documents that come from other data sources. You are interested in the data but not the tags, and want to store pure data in your database. You want the flexibility to decide whether to store the data in some existing tables or in new tables.
• A small subset of your XML documents needs to be updated often, and update performance is critical.
• You need to store the data of entire incoming XML documents but often only want to retrieve a subset of them.
• Your XML documents exceed 2 gigabytes and you must decompose them.

XML Collections method example

Start with two tables and use the same DTD from the XML Columns example earlier:

    CREATE TABLE Customer(
    ID INTEGER NOT NULL PRIMARY KEY,
    Country VARCHAR(30),
    LName VARCHAR(30),
    FName VARCHAR(30))
    
    CREATE TABLE Customer_Details(
    ID INTEGER,
    Phone_Number VARCHAR(20),
    Phone_Type VARCHAR(20), 
    CONSTRAINT CUST_DETAIL_FK
    FOREIGN KEY (ID) REFERENCES Customer (ID))

Listing 14 shows an example of a DAD file that uses SQL Mapping. Note that we can only retrieve with SQL mapping:

<?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd">
<DAD>
<validation>NO</validation>
<Xcollection>
<SQL_stmt>
SELECT c.ID,COUNTRY,LNAME,FNAME,p.PHONE_ID,PHONE_NUMBER,PHONE_TYPE from
CUSTOMER C, table (SELECT substr(char(timestamp(generate_unique())),16) as
PHONE_ID,ID,PHONE_NUMBER,PHONE_TYPE from customer_details) P where P.ID=C.ID
order by ID, PHONE_ID</SQL_stmt>
<prolog>?xml version="1.0"?</prolog>
<doctype>!DOCTYPE person SYSTEM "C:\dxx\samples\dtd\person.dtd"</doctype>
<root_node>
<element_node name ="person">
<attribute_node name="id">
<column name="ID"/>
</attribute_node>
<element_node name="firstName">
<text_node>
<column name="FName"/>
</text_node>
</element_node>
<element_node name="lastName">
<text_node>
<column name="LName"/>
</text_node>
</element_node>
<element_node name="phone" multi_occurrence="YES">
<element_node name="type">
<text_node>
<column name="Phone_Type"/>
</text_node>
</element_node>
<element_node name="number">
<text_node>
<column name="Phone_Number"/>
</text_node>
</element_node>
</element_node>
</element_node>
</root_node>
</Xcollection>
</DAD>

Listing 15 shows an example of a DAD file that uses RDB Mapping :

<?xml version="1.0"?>
<!DOCTYPE DAD SYSTEM "c:\dxx\dtd\dad.dtd">
<DAD>
<validation>NO</validation>
<Xcollection>
<prolog>?xml version="1.0"?</prolog>
<doctype>!DOCTYPE person SYSTEM "C:\dxx\samples\dtd\person.dtd"</doctype>
<root_node>
<element_node name ="person">
<RDB_node>
<table name="customer"/>
<table name="customer_details"/>
<condition>
customer.id=customer_details.ID
</condition>
</RDB_node>
<attribute_node name="id">
<RDB_node>
<table name="customer"/>
<column name="id"/>
</RDB_node>
</attribute_node>
<element_node name="firstName">
<text_node>
<RDB_node>
<table name="customer"/>
<column name="FName"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="lastName">
<text_node>
<RDB_node>
<table name="customer"/>
<column name="LName"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="phone" multi_occurrence="YES">
<element_node name="type">
<text_node>
<RDB_node>
<table name="customer_details"/>
<column name="Phone_Type"/>
</RDB_node>
</text_node>
</element_node>
<element_node name="number">
<text_node>
<RDB_node>
<table name="customer_details"/>
<column name="Phone_Number"/>
</RDB_node>
</text_node>
</element_node>
</element_node>
</element_node>
</root_node>
</Xcollection>
</DAD>

Back to top

WebSphere Application Developer XML tooling

The XML tooling for WebSphere Application Developer includes XML perspectives and authoring and generation tools. Here is a list of those tools.

XML perspectives

Data perspective

The Data perspective contains various tools to import and export data as XML from all databases having a JDBC driver.

XML perspective

The XML perspective contains various tools to author, edit and transform XML related files (XML, XML Schema, DTD, XSL stylesheet, and so forth).

XSL Debug perspective

The XSL Debug perspective enables you to debug XSL transformations in a symbolic debugger.

Authoring and generation tools

XML editor

The XML editor is a tool for creating and viewing XML files. You can use it to create new XML files, either from scratch, existing DTDs, or existing XML schemas. You can also use it to edit XML files, associate them with DTDs or schemas, and validate them.

DTD editor

The DTD editor is a tool for creating and viewing DTDs. Using the DTD editor, you can create DTDs, generate XML schema files, and generate Java beans. You can also use the DTD editor to generate a defaultHTML form based on the DTDs you create.

XML Schema editor

The XML Schema editor is a tool for creating, viewing, and validating XML schemas. You can use the XML schema editor to perform tasks such as creating XML schema components, importing and viewing XML schemas, generating DTDs and relational table definitions from XML schemas, and generating Java beans for creating XML instances of an XML schema.

XSL editor

The XSL editor can be used to create new XSL files or to edit existing ones. You can use content assist and various wizards to help you create or edit the XSL file. Once you have finished editing your file, you can also validate it. As well, you can associate an XML instance file with the XSL source file you are editing and use that to provide guided editing when defining constructions such as an XPath expression.

XPath Expression wizard

You can use the XPath expression wizard to create XPath expressions. XPath expressions can be used to search through XML documents, extracting information from the nodes (such as an element or attribute).

XSL tools

You can use the XSL debugging and transformation tool to apply XSL files to XML files, transforming them into new XML, HTML, or text files. After the transformation has taken place, the XSL Debug perspective enables you to visually step through an XSL transformation script, highlighting the transformation rules as they are fired. You can use the views in the XSL Debug perspective to help you debug the XML or XSL files.

SQL Query wizard and SQL Builder

You can use either the SQL Query wizard or SQL Builder to create SQL statements for XML generation or for database applications.

User-defined function

You can build and deploy SQL user-defined functions that can be used in SQL statements.

Stored procedures

Application Developer includes the stored procedure builder in a fully integrated way. Java and SQL stored procedures can be built and deployed.

XML and SQL Query wizard

You can use the XML and SQL Query wizard to create an XML file from the results of an SQL query or take an XML file and store it in a relational table. When creating an XML file from an SQL query, you can optionally choose to create an XML schema or DTD file that describes the structure that the XML file has for use in other applications. Two Java class libraries SQLToXML (sqltoxml.jar) and XMLToSQL (xmltosql.jar) are included so you can use them in your applications at run time. These JAR files are located in: WSAD_HOME_DIRECTORY\wstools\eclipse\plugins\com.ibm.etools.sqltoxml\jars

XML to XML mapping editor

The XML to XML mapping editor is a tool used to map one or more source XML files to a single target XML file. You can add XPath expressions, groupings, Java methods or conversion functions to your mapping. Mappings can also be edited, deleted, or persisted for later use. After defining the mappings, you can generate an XSLT script. The generated script can then be used to combine and transform any XML files that conform to the source DTDs.

RDB to XML mapping editor

The RDB to XML mapping editor is a tool for defining the mapping between one or more relational tables and an XML file. After you have created the mapping, you can generate a document access definition (DAD) script which can be run by the DB2 XML Extender to either compose XML files from existing DB2 data, or decompose XML files into DB2 data.

Web Service wizard

Application Developer can create Web services from SQL statements and stored procedures. In a first step a document access definition extended (DADX) file is created from the SQL statement or stored procedure; in a second step a Web service is created from the DADX file.

Back to top

Create a WORF Web Service in WebSphere Studio Application Developer

WORF definition

WORF is an acronym for Web services object runtime framework. WORF is included in WebSphere Studio and is also available as a separate download in DB2. It will work also with WebSphere Application Server AE. It provides an environment to create XML-based Web services that access DB2. WORF uses SOAP and a Document Access Definition Extension file (DADX) that describes the set of SQL operations that can be invoked over the Internet.

WORF supports both HTTP GET and POST operations in addition to SOAP requests. On a service request, WORF will load the DADX file specified in the request, connect to DB2, run the SQL statement, and commit the database transaction. It will format the result into XML, converting data types as necessary, and return the response to the requester.

Create a Web project

Follow these steps to create a new J2EE Web project called TestWORF.

1. Click on File -> New -> Project -> Web -> Dynamic Web Project.
2. Click Next.
3. Enter in the project name as TestWORF.
4. Click Finish.

Switch to the Data Perspective

Switch to the Data perspective. Click on Window->Open Perspective->Data.

Import a database schema into Web project

Here are the steps to Import the database schema into the TestWORF project:

1. Right click in the DB Servers view and select New Connection.
2. Enter in a database name of SAMPLE and the correct User Id and Password and click Finish.
3. Expand the newly created connection. Right click on the schema and choose Import to Folder.
4. Choose the TestWORF project from the Browse button.
5. Click Finish and then let the databases folder be created.

Create an SQL statement

Create an SQL query statement to query the database and return all the employees in the company.

1. Turn to the Data Definition view at the top left.
2. Expand TestWORF -> WebContent -> WEB-INF -> databases -> SAMPLE -> Statements.
3. Right click on the Statements folder and choose New -> Select Statement.
4. Type in "Employees" as the statement name.
5. Click OK.
6. The SQL Builder should now open on the Employees statement. Add the ADMIN.EMPLOYEE table to the statement, by right clicking in the Tables view.
7. Save the query statement.
8. Execute the statement to verify that the contents match the following table. Click on the SQL menu on the main menu bar and choose Execute.

Create a DADX Group

We will now proceed to create a DADX web service utilizing our newly created statement. First create a new DADX group configuration. The DADX group contains connection (JNDI and JDBC) and other information that is shared amongst the DADX files in the group.

1. Click on File -> New -> Other -> Web Services -> Web Service DADX Group Configuration.
2. On the next page, highlight the TestWORF project and click on the Add Group button. Enter "EmployeeGroup" as a group name.
3. Expand the TestWORF folder and highlight the EmployeeGroup
4. Click on Group Properties button and update the DB URL to jdbc:db2:SAMPLE.
5. Click OK and then click Finish in the DADX group configuration wizard.

Create a DADX file

We will now create a DADX file using the SELECT statement we created earlier.

1. Click on File -> New -> Other -> Web Services -> DADX File.
2. Expand the TestWORF folder until you find the Employees statement.
3. Select the statement and click Next twice so you are on the DADX Generation Dialog.
4. Enter EmployeesList.dadx as the file name.
5. Ensure the output folder is /TestWORF/JavaSource/groups/EmplyeeGroup.
6. Click Finish.

Create a DADX Web Service

Next we will create a DADX Web Service.

1. Click on File -> New -> Other -> Web Services -> Web Service.
2. On the next page, choose a Web service type of DADX Web Service.
3. Ensure the Generate a Proxy checkbox is checked. A Java Proxy is the client proxy type and choose to Test the Generated Proxy. Click Next.
4. In the Service Deployment Configuration dialog make sure that the Service Web Project is TestWORF and the Client Web Project is TestWORFClient. Click Next.
5. Click Ignore All on the Web Services Warning dialog.
6. In the Web Service DADX File Selection dialog, make sure that the DADX file that you created is selected. In this case, its: /TestWORF/JavaSource/groups/EmplyeeGroup/EmployeesList.dadx. Click Next.
7. Verify the DB URL in the Web Service DADX Group properties Dialog is jdbc:db2:SAMPLE. Click Next.
8. In the Web Service Binding Proxy Generation dialog, check the Show mappings checkbox and click Next.
9. Select the Show and use default DOM Element mapping radio button.
10. Click Finish.

Test the DADX Web Service

At this point a DADX web service has been created and published in the WebSphere Test Environment. A TestClient.jsp test page should appear in a Web browser as follows:

1. Click on the Employees() Method link.
2. Click on the Invoke button.
3. The Result pane should now update and show XML output of the Employees query statement.

Output from DADX Web service

Here is the output from the SELECT query:

Back to top

Deploying DB2 Web services

Running DB2 Web services in a WebSphere Application Server requires the Web Services Object Runtime Facility (WORF) runtime. In short:

1. Locate dxxworf.zip in your DB2_HOME_DIRECTORY\samples\java\Websphere directory, where DB2_HOME_DIRECTORY is the directory where DB2 is installed.
2. Unzip the dxxworf.zip to any directory.
3. Copy worf.jar from the unzipped lib subdirectory to WAS_HOME_DIRECTORY\lib, where WAS_HOME_DIRECTORY is the directory where WebSphere Application Server is installed.
4. Export your EAR with the WAR containing the Web service and install in WAS.

Back to top

Summary

This article has covered the following topics:

• DB2 XML Extender provides a means to easily import and export XML data from a DB2 database.
• DB2 XML Extender has two methods to do this: XML Columns and XML Collections.
• WORF (Web Services Object Runtime Framework ) can be used to import and export XML data from a DB2 database via a Web Service. A WORF based Web Service (using a DADX file) can be used to execute a stored procedure as well as CRUD methods to invoke SQL statements directly to insert, update, delete, or query your data.
• DB2 XML Extender operations such as storeXML and retrieveXML can also be included in the WORF Web Service DADX file to store or retrieve XML documents to or from your DB2 database.

Back to top

Download

Information about download methods

Resources

• Download the source code used in this article.
  
  
• "Creating Web Services for DB2 UDB for OS/390 Stored Procedures Using WebSphere Studio Version 5" (developerWorks, April 2003)
  
  
• XML for DB2 Information Integration (IBM Redbook, July 2004)
  
  
• Integrating XML with DB2 XML Extender and DB2 Text Extender (IBM Redbook, December 2000)
  
  

About the author

Owen Cline is a member of the IBM Software Services for Websphere team based in San Diego, CA. He has over 20 years of experience in the software development field. He holds four software patents, has written IBM redbooks and has presented at multiple technical conferences. For the past five years, Owen has specialized in J2EE architecture, application development, and deployment, with a special emphasis on the WebSphere platform. In addition, he has also worked on many high profile Web sites over the past few years.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• DB2 XML Extender overview
• WebSphere Application Developer XML tooling
• Create a WORF Web Service in WebSphere Studio Application Developer
• Deploying DB2 Web services
• Summary
• Download
• Resources
• About the author
• 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