The DB2 tutorial you are looking at is: DB2 9 (Viper) QuickStart.
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.
Storing XML data
Once you've created the tables, you can now populate them with data. You can do this by issuing a SQL INSERT statement directly, or by invoking the DB2 IMPORT tool to emit an INSERT statement in the background.
Use INSERT statement
With INSERT, you can populate the original XML data directly into the DB2. This is probably the easiest way to do this if you have written an application and stored XML data in a variable. But if you're just starting out with DB2 Viper, and you don't want to write an application, you can interactively emit an INSERT statement (I find it handy to use the DB2 command Editor, although you can also use a command-line processor if you prefer).
To use the DB2 Command Editor, start DB2 control Center. Select Command Editor from the top drop-down menu "Tools", and a separate window will appear, as shown in Figure 1.
Figure 1. DB2 Command Editor
Enter the following statement in the upper pane:
Listing 4. To insert XML data interactively
Connect to test;
INSERT INTO clients values (+, ' John Smith ', ' Gold ',
Xmlparse (document ' <addr>111 Main St., Dallas, TX, 00112</addr> '
Preserve whitespace)
)
Click the green arrow on the left to execute the command.
Note that providing XML data online (as shown in Listing 4) requires you to call the Xmlparse function to convert the document from character values to XML type values. The input document in this example is fairly straightforward. If the document is large or complex, it is impractical to type the XML data into the INSERT statement as shown in Listing 4. In most cases, you use host variables or parameter markers to write an application to insert data. You will find this article with a brief example of Java code writing. However, since this is an introductory tutorial, we will not discuss application development topics in detail. Instead, we'll discuss another option for populating DB2 XML columns with data-using the IMPORT tool.
using DB2 IMPORT
[1] [2] [3] Next article
