XML and Beyond: Four New Features from IBM's Viper

With the release of DB2 v9, aka Viper, IBM has debuted what could be considered the world's first hybrid XML and relational database. In other words, if there is such a thing as native XML management, Viper has it. But it also has a lot more.

As a regular visitor to this site, you've no doubt read about a lot of Viper's new XML capabilities, especially its new query language targeting XML data, XQuery. Here, you'll get a quick rundown of some of that power by creating an XML-enabled database and importing a very large raw data set. Then you'll go beyond that with a look at some of its other new features.

Hands-on Look at Viper
But first, take a look at the database itself. IBM has made the download available. If you're not quite ready to download it, here are some demos to let you play around with the look and feel of Viper.

Quick Tip: to create the sample database you see in these walkthroughs, run the db2sampl utility (see Figure 1).

Figure 1. DB2Sampl.exe

Creating Your Shakespearean Database
You probably already known how to create a database, but this walkthrough will go over creation of a simple demo table, including a field with type "xml", which is new to Viper. Once the table is created, you have a couple of ways to import data into the field, including using the "Import..." option from the table context menu. This walkthrough will cover using the "import" command in the command editor.

1. Open the Control Center
2. Expand the Databases folder on the left. On the right, click "Create a new database..."
3. When specifying the name for the database, call it "SHAKES" and check "Enable database for XML".
4. For storage options, no need to specify any additional storage for this demo.
5. Don't specify a maintenance window right now. You can always do this later if you like, using the "Configure Automatic Maintenance" wizard in the Control Center (Tools -> Wizards...).
6. Skip specifying the SMTP server, as well.
7. After the database is created, expand it to the table level. Right-click on the Tables folder and select "Create".
8. Create a PLAYS table with the following columns:

   PLAYID: INTEGER (4), not nullable, Identity: initial value 1
   PLAYNAME: VARCHAR (50), not nullable
   PLAYTEXT: XML, nullable

   Figure 2. New Table in Compressed Format

9. Before going to the next screen, be sure to check "Store table in a compressed format" (see Figure 2). I'll go over this in more detail in a moment.
10. When specifying space for storing table data, just click Next for now. Viper uses Automatic Data Storage by default and this is where you can specify the components of a virtualized environment if you want to distribute table management across multiple resources.
11. Define PLAYID as the Primary Key. Accept all other defaults through the rest of the wizard.
12. On the last step, you can check the SQL code for your new table. It should look something like this, where "VIPER1" is your Schema name.

    CONNECT TO SHAKES;
    CREATE TABLE VIPER1.PLAYS 
    ( PLAYID INTEGER  NOT NULL  GENERATED ALWAYS AS IDENTITY (START 
    WITH 1, INCREMENT BY 1, NO CACHE ) , 
    PLAYNAME VARCHAR (50)  NOT NULL , 
    PLAYTEXT XML  , 
    CONSTRAINT CC1145679938359 PRIMARY KEY ( PLAYID) ) COMPRESS YES ;
    CONNECT RESET;

13. Before importing XML data, you need some XML data to import. Over the years, many students, technophiles, and lovers of art have committed the works of the Great Bard to XML. Here's one such site. Download a few your favorite plays. This example will use All's Well That End's Well (all_well.xml), Love's Labor's Lost (lll.xml), and Richard III (rich_iii.xml). Create a C:\BILLDEMO directory and save the files to it.
14. Create a text file with the following contents, substituting play and file names where appropriate:

    All's Well That End's Well,<XDS FIL='all_well.xml' />
    Love's Labor's Lost,<XDS FIL='lll.xml' />
    Richard III,<XDS FIL='rich_iii.xml' />

    Save the file to your root and name it "C:\BILLDEMO\plays.del".
15. If using the site above, you'll notice that each XML file references a .dtd spec. That can be downloaded from http://www.ibiblio.org/xml/examples/play.dtd. Include it in your C:\BILLDEMO directory and run the following SQL statement in your Command Editor (right-click the PLAYS table and choose Query…).

    REGISTER XSROBJECT 'play.dtd'
    FROM 'C:\BILLDEMO\play.dtd'
    AS VIPER1.SHAKESDTD DTD ;

16. To import the XML files, copy and paste the following into your command editor. Be sure to include the correct Schema name.

    IMPORT FROM 'C:\BILLDEMO\plays.del' OF DEL
    XML FROM 'C:\BILLDEMO'
    MODIFIED BY IDENTITYMISSING
    INSERT INTO VIPER1.PLAYS

17. You'll see that the XML has been imported to the PLAYTEXT field. But unlike relational data, it's not being stored as a BLOB or TEXT field. Nor has it been parsed out across multiple fields. Instead, it's live, indexable, searchable XML.

Self-Managing Memory
As virtualization picks up steam, enterprises are taking notice of products that can take advantage of a distributed architecture. DB2 v8.2 began stepping up with its Automatic Store Manager. Essentially, you could point the database to a set of drives and say "use that." DB2 would obediently distribute storage across what resources were available at the time.

With Viper, this is turned on by default. But now you have the next step: self-managing memory. Once you assign memory resources to your database, Viper has the intelligence to even the load. For more information, check out the IBM's InfoCenter on Memory Management.

Figure 3. Specifying Storage in a New Database

Label Based Security
Also new in Viper: Label Based Access Control (LBAC). Previous versions of DB2 relied on row-level security, already an advanced feature for some databases. Viper amps it up a notch by adding column-level security, as well. Here's how it works. You can define a label for a particular piece of data - as granular as a single value. Then assign permissions to that label to restrict access to it. This way, you can grant access down to specific data points.

Data Compression
Since V8, DB2 could compress data through techniques such as Value Compression for NULLs and zero-length values, and Compress System Default for default values. But alongside its XML functionality, Viper introduces a new data compression technique that works by suppressing repeat values.

This works primarily on relational data. For example, if you're creating a database of marsupials, you might have a lot of repeat data. There exists 93 species of opossum, 20 in the Genus Monodelphis alone, including the Southern Red-sided Opossum (Monodelphis sorex), the Southern Three-striped Opossum (Monodelphis theresa), and the Red Three-striped Opossum (Monodelphis umbristriata). To store this data more efficiently, leave the option "Store table in a compressed format" unchecked (Step 9 in the above walkthrough). Assuming you have Genus and Species broken out into separate fields, DB2 will be able to suppress the repeat values in the Genus field. If you compare it to an uncompressed database, you should see a marked difference.

Where to Go from Here
What you've seen here is a basic overview of DB2 Viper. In the coming weeks, you'll read more about the new features, including the DB2 Viper Workbench. In the meantime, download DB2 Viper now and start playing around with it.

Also, check out the following resources to learn more about Viper's XML capabilities, including XQuery code.

• An Introduction to XQuery