DB2 9 Viper Quick Start

To help you quickly master the XML features of DB2 itself, do a few common tasks, such as:

Create database objects for managing XML data, including a test database, some sample tables, and views.

Use the INSERT and IMPORT statements to populate the database with XML data.

Validate your XML data. Use DB2 to develop and register your XML schema and use the Xmlvalidate option when importing data.

Subsequent articles will include other topics, such as using SQL queries, updating and deleting DB2 XML data, using XQuery queries to DB2 XML data, and developing Java applications and WEB components that access DB2 XML data.

To create a database object

Let's start by creating a separate DB2 Unicode database. In DB2 Viper, only a Unicode database can store more traditional formats for both XML documents and SQL data, such as integers, date/time, variable-length strings, and so on. You will then create objects in this database to manage XML and other types of data.

To create a test database

To create a new DB2 Unicode "test" database, open the DB2 command window, emit statements to specify the Unicode encoding collection and supported zones, as Listing 1:

Listing 1. Creating a database to store XML data

Create DATABASE test using CodeSet UTF-8 territory US

Once you create a Unicode database, you do not need to issue any specific commands or take any further steps to enable DB2 to store XML data in its own hierarchical format, as your DB2 system is ready.

To create a sample table

To store XML data, create a table that contains one or more XML columns. These tables act as logical containers for the collection of documents; behind the scenes, DB2 actually uses different storage scenarios to store both XML and non-XML data. However, using tables as logical objects to manage various supported data formats simplifies management and application development issues, especially when you need to integrate different data formats in a separate query.

You can define a DB2 table so that it contains only XML columns, columns that contain only traditional SQL types, or both. In this paper, the latter case is modeled. The example in Listing 2 connects to the test database and creates two tables. The first is the "Items" table, which tracks the sales of the goods and the customer's evaluation information about the goods. The second table tracks information about customers, including data about contact information. Note that "comments" and "ContactInfo" are based on the new DB2 XML data type, and all other columns are based on the traditional SQL data type.

Listing 2. To create a table for XML data

Connect to test;

CREATE TABLE items (

ID int PRIMARY key NOT NULL,

brandname varchar (30),

ItemName varchar (30),

SKU int,

SRP decimal (7,2),

Comments XML

);

CREATE TABLE Clients (

ID int PRIMARY key NOT NULL,

Name varchar (50),

Status varchar (10),

ContactInfo XML

);

If you look closely at these table definition examples, you will notice that neither the "comments" nor the "contactinfo" columns are defined for the internal structure of the XML document. This is an important feature of DB2. Users do not need to predefined an XML data structure (or, more precisely, an XML schema) to store data. In fact, DB2 can store any well-formed XML document in a separate column, which means that XML documents of different schemas-or documents that are not associated with any registered schema-can be stored in the same DB2 column. This article discusses this feature in depth when we discuss how to store data in DB2.

Create a View

You can create a view on a table that contains XML data at will, just as you can create a view on a table that contains only traditional SQL data types. The example in Listing 3 creates a view of a customer with a "Gold" status:

Listing 3. Create a view that contains XML data

CREATE View Goldview as

Select ID, name, contactinfo

From clients where status= ' Gold ';

A little explanation of the index

Finally, there is no need to create a specialized index on an XML column to improve the query speed of the data. Because this is an introductory article, and the sample data is very small, this article does not cover that topic. However, in a production environment, defining an appropriate index is critical to achieving optimal performance. See the "Resources" section at the end of this article to help you understand the new indexing technology for DB2.