IBM DB2 XML Support
Available only in the Enterprise edition
The support for IBM DB2 Pure XML
database includes: multiple server connections, resource management, XML Schema
registration, XQuery and SQL execution, and table data editing.
About this Tutorial
This tutorial explains how to configure the IBM DB2 support in oXygen XML
Editor, browse and edit the database tables (and particularly the XML content of the
cells belonging to an XML type column), register, view or drop the XSD schema used to
validate the XML cell content, as well as running SQL/SQL/XML or XQuery
It assumes that you have a basic knowledge of databases, SQL, XML technologies,
and of oXygen XML Editor. The explanations and screenshots are given for the
standalone version of oXygen XML Editor. However, the same set of features are
available in the Eclipse plugin version of the product with minor interface
How to Configure the IBM DB2 Support in oXygen
There are two notions you need to understand in order to configure the DB2 support
A data source defines all that is needed in order to have a connection to the
database. oXygen uses JDBC as a way to connect, so a data source defines the DB2
JDBC driver details. If you want to connect to servers running different versions of
DB2 you need to configure a data source for each DB2 version so that it will properly
match the JDBC driver version.
oXygen currently supports version 9 of IBM DB2 database also known as pureXML.
The recommended way to connect is using the JDBC type 4 drivers.
The data source drivers can be configured in the Data Source
For specific instructions on how to configure the data source drivers, see the
following topic in our Users Manual: http://www.oxygenxml.com/doc/ug-editor/tasks/configure-db2-datasource.html#configure-db2-datasource.
Once you have created the data source you may proceed further by defining one or
more connections. The connections can be configured in the same Data
Source preferences page.
For specific instructions on how to configure the connections, see the following
topic in our Users Manual: http://www.oxygenxml.com/doc/ug-editor/tasks/configure-db2-connection.html#configure-db2-connection.
Sometimes you need to work with two database servers (for example, a development
sever and a production server) so it makes sense to define a connection on each
database server to be further used when executing SQL/XQuery or browsing/editing the
database content. If the two database servers have the same version, you only need to
configure a data source and add two connections for it.
Data Source Explorer View
Once you finish the configuration of the connection, you can browse the database
content using the Data Source Explorer view from the Database perspective.
Drag-and-drop support between tree nodes and an opened SQL editor is also
One of the interesting operations available on table nodes is the "Export to XML"
action. That allows the database table content to be exported as an XML structure.
Please note that oXygen is able create a basic XSD schema for the table you need
oXygen also features a tool that is able to generate an XSD schema based on a
set of database tables. The Convert DB structure to XML Schema tool
is available under the "Tools" menu. Basically, if you select a set of tables,
oXygen can generate an XSD schema that describes table data definitions and it
takes the key relationships into account.
Table Explorer View
The Table Explorer view from the Database Perspective is able to represent a
database table content or the result of an SQL interrogation. If you want to edit the
table content of a database, you can use the Edit operation from the contextual menu
of the Data Source Explorer view.
The view allows you to add, delete, or duplicate table rows. If the database
constraints are violated due to your changes, you will be presented with an error
message that will help you to correct the problem.
The table columns can be sorted by clicking on the table header.
Editing XML Content of the XML Type Columns
The true power of oXygen is realized when you need to edit content from the
XML type columns. The data in these columns can be opened directly in oXygen XML
Editor so you can benefit from all its editing features. Saving the edited data in the
database can be done by simply using the Save action. If the database rejects your
changes during this operation, you get an error message and the file status remains as
Other Operations Available for an XML Cell:
- Insert XML file (inserts the content of an XML file on the particular cell)
- Validate (validates the content of the current XML cell against a previously
registered XSD schema)
XML Schema Repository
Every DB2 database schema has an associated "XML Schema repository" where all the
XSD schemas available to validate XML content of the XML type columns are stored. The
"XML Schema repository" is available as a child node in the Database view for any
database schema node. You can expand the "XML Schema repository" node and perform
operations such as registering a new schema or dropping an existing one.
For more information, see: http://www.oxygenxml.com/doc/ug-editor/topics/db2-xml-schema-repository-level.html#db2-xml-schema-repository-level.
The XSD schema stored in the "XML Schema repository" can be viewed in the
oXygen XSD editor. However, you cannot modify them (since they are
SQL, SQL/XML Support
You can use oXygen to run SQL (including DDL) or SQL/XML interrogations. To do
this, you need to open an SQL document (an SQL template is available from the File/New
menu) and write your query content. The SQL editor has an associated scenario where
you need to specify the previously created DB2 connection as the transformation
If you want to re-use the SQL queries, you can specify parameter markers (?) in
the SQL content and add their corresponding mapping in the Configure
Parameters dialog box for the associated scenario. For example, suppose you
need to write an SQL interrogation to get a report with all employees from one
department and their last evaluation dates. For this SQL query, you could use a
parameter marker (the ID of the department) configured in the Transformation Scenario
so you can easily re-use the query for another department.
DB2 pureXML supports XQuery interrogations when working with the XML content of
the XML column types. For example, if you want to generate an XQuery to measure the
employee satisfaction levels regarding the company rules, you can open an XQuery
template (New/XQuery), configure the transformation scenario to match the DB2
connection for the transformer field, write the XQuery, and then execute it.