DB2 9 Viper Quick Start

DB2 databaseMany new versions have been released during the development process, which greatly improves the functionality.DB2 9 ViperIs One Of Them. Next we will introduce DB2 9 Viper in detail.

To help you quickly master the XML features of DB2, complete several common tasks, such:

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

Use the INSERT and IMPORT statements to fill the XML data in the database.

Verify your XML data. Use DB2 to develop and register your XML schema, and use the XMLVALIDATE option when importing data.

Other topics will be covered in subsequent articles, such as using SQL to query, update, and delete DB2 XML data, using XQuery to query DB2 XML data, and developing Java applications and Web components to access DB2 XML data.

Create database objects

Let's first create a separate DB2 Unicode database. In DB2 Viper, only Unicode databases can store more traditional formats of XML documents and SQL data simultaneously, such as integers, dates/times, and variable-length strings. Then, you will create an object in this database to manage XML and other types of data.

Create Test Database

To create a new DB2 Unicode "test" database, open the DB2 command window and issue a statement to specify the Unicode Collation and supported regions, as shown in Listing 1:

Listing 1. Creating a database for storing XML data

Create database test using codeset UTF-8 territory us

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

Create example table

To store XML data, create a table that contains one or more XML columns. These tables act as logical containers for document sets. DB2 uses different storage solutions behind the scenes to store XML and non-XML data. However, using tables as logical objects for managing various supported data formats simplifies management and application development, especially when you need to integrate different data formats in a separate query.

You can define a DB2 table so that it only contains XML columns, only columns of the traditional SQL type, or both. This article models the latter case. 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 goods and the customer's evaluation of goods. The second table traces the "customer" information, including the 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. Creating 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 carefully view the definition examples of these tables, you will notice that neither the "comments" nor the "contactinfo" column defines the internal structure of the XML document. This is an important feature of DB2. You do not need to predefine an XML data structure or, more accurately, an XML schema to store data ). In fact, DB2 can store any well-formed XML document in a separate column, this means that XML documents in different modes-or documents not associated with any registered modes-can be stored in the same DB2 column. This article will discuss this feature in depth when discussing how to store data in DB2.

Create 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 only contains traditional SQL data. The example in listing 3 creates a view of customers in the "Gold" status:

Listing 3. Creating a view containing XML data

Create view goldview

Select id, name, contactinfo

From clients where status = 'gold ';

A description of Indexes

Finally, there is no need to create a special index on the XML column to improve the data query speed. This is an introductory article with very little sample data, so this article will not cover that topic. However, defining an appropriate index in a production environment is critical to achieving optimal performance. Refer to the "references" at the end of this article to help you understand the new indexing technology of DB2.