The current db2 tutorial is: DB2 9 (Viper) Quick Start.
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.
Store XML data
After creating tables, you can fill them with data. You can directly issue an SQL INSERT statement to do this, or call the DB2 IMPORT tool to issue an INSERT statement in the background.
INSERT statement
With INSERT, you can directly populate the original XML data with DB2. If you have compiled an application and stored XML data in the variables, this is probably the easiest way. However, if you just started using DB2 Viper and didn't want to write applications, you can issue INSERT statements interactively (I found it very convenient to use DB2 Command Editor, you can also use the command line processor, if you prefer ).
To use DB2 Command Editor, start DB2 Control Center. Select Command Editor from the drop-down menu "Tools" at the top. A separate window is displayed, as shown in figure 1.
Figure 1. DB2 Command Editor
In the preceding pane, enter the following statements:
Listing 4. Insert XML data interactively
Connect to test;
Insert into clients values (77, '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: To provide XML data online (as shown in Listing 4), you need to call the XMLPARSE function to convert the document from the character value to the XML type value. The input document in this example is quite simple. If the document is large or complex, it is impractical to type XML data into the INSERT statement shown in Listing 4. In most cases, you use host variables or parameter markers to write an application to insert data. This document provides a brief Java code compiling example. However, since this is an introductory tutorial, we will not discuss the topic of application development in detail. Instead, we will discuss another option to populate the DB2 XML column with data-using the IMPORT tool.
Use DB2 IMPORT
[1] [2] [3] Next
