Quick Start DB2 Viper

Create database objects

Let us first create a separate DB2 Unicode database. In DB2 Viper, only Unicode database can also store XML documents and SQL data, more traditional formats, such as integer, date / time, variable-length strings, and so on. You will then create the objects in the 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, issue the statement to specify the Unicode code set and support areas, such as Listing 1:

Listing 1. To create a database for storing 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 order or take any further steps to make to its own DB2 to store XML data in hierarchical format, for your DB2 system is ready.

Create a sample table

To store XML data, create XML column that contains one or more tables. These tables serve as the logical document collection containers; in the background, DB2 actually uses a different storage options to store XML and non XML data. However, the use table as the management of a variety of data formats supported by the logic of an object, simplifying the management and application development issues, especially when you need a separate query in the integration of different data formats.

You can define the DB2 table to include only the XML column that contains only the traditional SQL types of columns or include both. In this paper, the latter was modeled. List 2 examples of connection to the "test" database, and create the two tables. The first is the "items" list to track the sale of goods and customer evaluation information on the goods. The second track is on the table "customer" information, including contact information on the data. Note "comments" and "contactinfo" is based on the new DB2 XML data type, while all other columns are based on traditional SQL data types.

Listing 2. 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 examples of the table definition, you will notice that "comments" and "contactinfo" column are not carried out the definition of the internal structure of XML documents. This is an important feature of DB2. Users do not need to store data on a predefined XML data structure (or, more precisely, an XML mode). In fact, DB2 can be stored in a separate column of any well-formed XML document, which means that different modes of XML documents - or not associated with any registration of a document model - can be stored in the same DB2 column . When we discuss how to store data in DB2, this article will discuss in detail this feature.

Create view

You are free to include XML data in the table to create the view, as you can include only the traditional SQL data types to create the view as a table. Listing 3 example creates a "Gold" status of a customer's view:

Listing 3. To create a view that contains XML data

create view goldview as

select id, name, contactinfo

from clients where status =''Gold'';

A Note on the index

Finally, there is no need to create a dedicated XML column indexes to improve query speed data. Because this is an introductory article, but very little sample data, so that this does not cover the topic. However, in a production environment, the definition of an appropriate index for the achievement of optimal performance is critical. View this article at the end of the "reference" to help understand new DB2 technology index

XML data storage

After the table is created, and now you can fill them up with data. You can directly issue SQL INSERT statement to finish the job, or by calling the DB2 IMPORT tools INSERT statement issued in the background.

Using the INSERT statement

Using INSERT, you can fill directly to the DB2 in the original XML data. If you have already written an application in the XML data stored in the variable, then this may be the easiest way. But if you only just started using DB2 Viper, and do not want to write applications, then you can interactively send INSERT statement (I found using the DB2 Command Editor is a convenient, although you can also use the command line processor, if you prefer so to do).

To use the DB2 Command Editor, please start the DB2 Control Center. From the top drop-down menu "Tools" select Command Editor, will appear in a separate window, shown in Figure 1.

Figure 1. DB2 Command Editor






In the above pane, enter the following statement:

Listing 4. Interactively insert XML data

connect to test;

insert into clients values (77,''John Smith'',''Gold'',

xmlparse (document''111 Main St., Dallas, TX, 00112''

preserve whitespace)

)

Click the green arrow to the left of the implementation of the order.

Note that the online provision of XML data (shown in Listing 4) require you to call XMLPARSE function to convert the document into a character value from the XML type value. In this case the input document is quite simple. If the document is large or very complex, the XML data type to the list of 4 as shown in the INSERT statement is unrealistic. In most cases, you use host variables or parameter markers write an application to insert data. You will find this article with a brief write Java code examples. However, since this is an introductory tutorial, so we will not discuss in detail the application development topics. Instead, we will discuss DB2 XML column using the data to populate another option - using IMPORT tool.

Use the DB2 IMPORT

If you're in the file contains XML data, DB2 IMPORT tools provide you with a DB2 table using the data to populate a simple way. You do not need to write applications that only need to create a delimited ASCII file that contains the table you want to load the data. Parameter is stored in a file specified in the XML data in the appropriate file name.

You can choose the text editor to create a delimited ASCII file. (By convention, these files are usually. Del type). Each line in the file represents a row into a table of data. If the line contains the XML Data Specifier (XDS), IMPORT will be included in the reference to read the data in the XML file and to import data into DB2 in. For example, Figure 2, the first line contains the Ella Kimpton information, including her ID, name and customer status. Her contact information is also included in the Client3227.xml file.

Figure 2. Entered into the DB2 IMPORT delimited ASCII file example






Client3227.xml contents of the file shown in Figure 3. As you see, the file contains Ella Kimpton of address, telephone number, fax number and e-mail and other XML elements.

Figure 3. Sample customer XML document






If you do not want to insert all the rows have the XML file, you may be curious about the import data. This is very easy to do, ignore the input file can XDS information. For example, in Figure 4 items.del file ignored Item 3641 ("Dress to Impress" package) and XML file name. The results of this line of XML column does not contain any data.

Figure 4. Delimited ASCII file in the sample line of no XML Data Specifier






With XML files and ASCII delimited file, you can now use the DB2 IMPORT the. Listing 4 in the following this statement will be C: / XMLFILES directory clients.del content specified in the file into "clients" table.

Listing 4. To import the data to the "clients" table

import from clients.del of del xml from C: / XMLFILES insert into user1.clients;

Clients.del shown in Figure 2 contains six rows of data, including the six XML files. IMPORT command successful implementation of the results of the output in Figure 5.

Figure 5. DB2 IMPORT sample output






Independent software vendors, such as Exegenix, provided to Word, PDF and other document formats into XML tools, in order to facilitate the import into DB2. (See Resources to obtain more information on Exegenix.

Validate XML data

INSERT and IMPORT only discussion of the sample to the table to write a good XML format data. They do not validate the data - that is, they do not verify whether a particular XML data model, and therefore stick to determine the structure. Control of DB2 to do that is possible, however, let us study another way to accomplish this task:

Step 1: Create XML mode

In order to validate XML data, you need to define an XML model, to specify an acceptable XML elements, their order and data types, and so on. W3C XML is an industry standard model and is written in XML. Although the model to explain the characteristics of XML beyond the scope of this article, but there are various tutorials on the Web for reference (see "Resources").

There are many ways to develop XML model from using your favorite text editor to manually create the model, and then to use the tools to generate graphical design or pattern. Independent software vendors, such as MDXSYS Limited, provide such XML tools, IBM is also through its Java? Integrated development environment provides support for XML schema generation.

For example, using IBM WebSphere? Studio, you can Client3227.xml file (Figure 3) imported into the Web project. Use the mouse right click and select Generate -> XML Schema. This will generate a specific input file a valid XML model, shown in Figure 6. You can then modify the file (if required) and register it to DB2.

Figure 6. To use WebSphere Studio XML file generated from an XML model






Let us assume that you need to make the XML model is quite flexible, so that you can collect different types of customer contact information. For example, some customers may provide you with multiple phone numbers or e-mail address and other customers do not have to provide so much.

Figure 7 shows the XML model from WebSphere Studio generated model to provide this flexibility. It includes elements of a given minimum and maximum allowed number of occurrences ("minOccurs" and "maxOccurs") for additional specifications. In this case, does not require customers to provide you with anything you want to collect contact information. However, if customers choose to your e-mail message, the model will enable the consistency of the document to contain up to five e-mail address (that is 5 "email" element value).

Figure 7. For the customer contact information of the sample XML model






As you have noted, XML model will also contain type information. Although the model shown in Figure 7 specifies only the basic elements of all are treated as strings, but most of the production of XML model will also use other data types, such as integer, decimal, date, and so on. If you INSERT or IMPORT for an operation as part of a given model to validate the XML document, then DB2 will automatically add to your XML document type comments.

Step 2: Register XML mode

Creates an appropriate XML model, you need to register the DB2 model. IBM offers a variety of ways to accomplish this task. DB2 Control Center you can run graphical wizard to guide you through the process, call the system stored procedure, or direct issue DB2 commands. Here we use the second method, because it can help you more easily understand the essence of your DB2 for that one end of the operation.

If your model is very large, you may need to register it before you try to increase the application heap size. For example, issue the following statement:

Listing 4. To increase the application heap size

connect to test;

update db cfg using applheapsz 10000;

Next, register your XML model. If your XML model does not refer to other XML model, you may need to register with a single command and to complete the process. Otherwise, you will need to issue a single command to register your initial XML model, adding other needs and complete the registration process models. When the models become very large file, usually it's content into multiple files to improve the maintainability, readability and reusability. This is similar to a complex application into multiple modules or components. Details on this topic, please refer to W3C "XML Schema primer" (see "Resources").

This article uses a simple, independent XML model. You can use this command to register the DB2:

Listing 5. Registered XML mode

register xmlschema''http://mysample.org''from''C: / XMLFiles / ClientInfo.xsd''as

user1.mysample complete;

In this case, ClientInfo.xsd is the XML model file name, it is located in C: / XMLFiles directory. The XML model will be registered to the SQL mode "user1" and the XML mode "mysample" DB2 under the internal repository. In this case is just a placeholder http://mysample.org parameter that specifies the XML instance document referenced by the Uniform Resource Identifier (URI); many XML documents use the name space, which is specified URI. Finally, "complete" XML clause will instruct DB2 to complete the registration process, so that patterns can be used to validate XML data.

It is noteworthy that model does not specify the mode of registration process will be applied to the table of which (s) listed. In other words, mode does not mean that SQL column constraint. A given model can be verified in a variety of different table XML column. However, the verification is not automatic. DB2 allows any well-formed XML documents stored in the XML column. If you want to store a registered prior to the model validation data, then you need to instruct DB2 to complete this task.

Step 3: Import XML data with validation

When you create an XML model, and it is fully registered to the DB2 in the following, you are now able to insert or import 灏?XML data in the time table, Rang DB2 on the data Yan Zheng. Now we have a model validation in mind, again recall the previous IMPORT scene.

If you have already filled the "clients" table, you can easily find the content to remove it, or delete and re-create the table. Only in the same as before when you plan to add to the table when the same data needs. Recall is defined as the customer ID column has a primary key of the "clients", The looming failure of repeated attempts to import.

In order to import XML data into "client" table to validate, use the DB2 IMPORT the XMLVALIDATE clause. Listing 6 below will instruct DB2 to use this statement you have previously registered XML mode (user1.mysample) as the default XDS (XML Data Specifier), in inserting them into "client" before the certification specified in the XML file clients.del file.

Listing 6. Importing XML data with validation

import from clients.del of del xml from C: / XMLFILES xmlvalidate using xds default

user1.mysample insert into user1.clients;

DB2 to determine if an XML document does not meet the specified pattern, then the document associated with the entire row will be rejected. Figure 8 illustrates an example of output from the IMPORT operation, the operation of six lines with a line of XML documents because it does not meet the specified pattern is rejected.

Figure 8. An example of output from the DB2 IMPORT, which his party was rejected






It is worth noting, XMLVALIDATE can also be used in conjunction with the INSERT statement to indicate the DB2 XML validation before inserting data. The syntax examples shown IMPORT similar to that specified when calling XMLVALIDATE clause, a registered (and completed) of the XML model. (See "A simple Java example," to obtain more information on this.)

Conclusion

DB2 Viper provides an important new capability to support XML, including a new XML data type and underlying engine-level components of an effective way to automatically store and process XML data. To help you quickly master these characteristics, this article describes how to create an XML document to store the test database and sample table. Also reviewed how to use XML data to populate the database. At last, the DB2 model for users to verify data and to provide a sample to show you how to entry.

Now that you have to learn how to use DB2's new "native" XML storage capacity of XML data. As for how to query the data, you will see in subsequent articles, follow-up article will introduce you to the new DB2 XQuery support, and its SQL extensions (sometimes called the "SQL / XML").







相关链接:



Top Calculators And Converters



Using Visual Basic's Timer control



Lg C2200 Review A Review Of The Lg C2200



Chinese way: do the best CAD China



JSP Experience Of Learning About The Steps And Timing



B2C transactions in China this year is expected to reach 11.1 billion U.S. dollars



Streaming Media Based On: What Is Streaming Media



3GPP to WMV



MOD to MPG



e-cology in the Pan Micro Series 68



E-cology in the Pan Micro Series 14



Material control manager, the day has come easy to you?



4 Promotion Practice Cheats



Shop Themes And Wallpaper



ASF to MPG