XML Everywhere: DB2 9 and Ajax, Part 1

Related Resources: Video: How DB2 Express-C Saved the World, Part 1 Download DB2 9 Express-C XML Everywhere: DB2 9 and Ajax, Part 2 Download the Listings for this Article Introducing IBM DB2 Viper Workbench, Featuring the XQuery Visual Builder

XML is nothing new. But thanks to new programming techniques like Ajax, its uses have grown tremendously over the recent years. But strangely enough, databases have had few, if any, native XML features—until now.

IBM's newest database, IBM DB2 9, boasts pureXML, a collection of native XML features that alleviate much of the stress of working with XML. From the new XML data type to the XQuery language, DB2 9 makes life easier for developers working with XML, which, at this point, is most people.

As much as DBAs would like it to be so, data doesn't always arrive at our doorstep neatly organized and labeled like memos in a mail bin at work. Sometimes you get the oddly shaped package that defies classification. For that, we create catch-all fields like "Notes" or "Comments" or "Misc." These are usually CLOB fields, large enough to hold any kind of random text that needs to be dumped in there.

Figure 1. XML Extender

You'll see an example of this in the following walkthrough—some information is standard enough to warrant its own data column, but other information is unique to each individual row. Herein lies the advantage of using an XML data field.

CLOB fields can hold anything—text, XML, HTML, customized descriptors. But to a database, it's all just text. That makes it hard to search, hard to parse, and very hard to format if and when you get the correct sub-data out of your CLOB fields.

XML fields, however, treat their content as XML, not as text that happens to have brackets in it. As a result, the data you store in an XML field remains highly usable. It's like having a mini-data table within your data table. The XML data can be searched using SQL, XMLSQL, XQuery, or some combination of all three. You can retrieve specific data nodes rather than simply the surrounding text, which may or may not be relevant. You can even associate an XML document, which is unique to each row, with strict formatting such as an XSD or DTD.

Figure 2. Launch Add-Ins Installation

And most importantly for your purposes here, XML data types work brilliantly with Ajax, or any other application that requires working directly with XML. Simply put, it takes the headache out of building XML-based applications.

This walkthrough shows you, step-by-step, how to use some of these new features to your advantage. You'll begin by downloading DB2 9 Express-C, which is distributed free. You'll install it and the new DB2 9 Visual Studio Add-ins, then use both to create a new Superhero database. After populating the database with sample data, including some pre-written XML documents, you'll create C# Web Services in Visual Studio 2005 that demonstrate the advantages of the XML data type. Lastly, you'll build out the HTML, JavaScript, and CSS using more pre-written code. By the time you finish, you'll have created a working app that lists your current Hero roster, triggers an asynchronous JavaScript call to your Web Service when you click on a Hero, retrieves data directly from an XML field, and dynamically generates new DOM objects based on that data.

Figure 3. Create Your Own Database

The technologies covered here include:

• DB2 Express-C
• ASP.NET 2.0 (C#)
• Web Services
• JavaScript (particularly Ajax techniques)
• Some CSS for styling the finished page

Your first step is to install and configure DB2 9 Express-C. Once you download it, begin the installation process by selecting "Install New" under "DB2 Express." When given the option, select a Custom installation type. When selecting features to install, be sure to include "XML Extender" under "Client Support" (see Figure 1).

As you finish the installation, you should see an option to continue with the install of the DB2 Add-ins for Visual Studio 2005, as in Figure 2. Select that—those tools will come in handy later in the walkthrough. If for some reason you miss it, you can re-run setup and choose either "DB2 Client," which also installs the add-ins, or "IBM Database add-ins for Visual Studio 2005."

Figure 4. Enable Database for XML

After installing Express-C, launch First Steps if it hasn't already launched for you (Start -> IBM DB2 -> DB2 (or the name you specified for this copy of DB2) -> Set-up Tools -> First Steps). For this walkthrough, you don't need to install the SAMPLE database, though it wouldn't hurt if you want to test your installation. In any case, select "Create your own database," as in Figure 3. The following section will walk you through creating the database and necessary tables, as well as populating them with sample data.

Name the database "AJAXDEMO." Check the option to "Enable database for XML." Depending on your build, you may see additional options, such as those included in Figure 4.

Once you've created the database, close First Steps and open up Visual Studio. Now's a good time to test out your new add-ins.

You're going to create two tables, one with an XML field and one without. You can do this many different ways, such as manually through the Control Center. But for demo purposes, start Visual Studio 2005 and create a new ASP.NET Web Site called "Superpals" (see Figures 5 and 6). Specify "Visual C#" as the language (Figure 6).

Figure 5. New Web Site

Figure 6. ASP.NET HTTP C# Web Site

Figure 7. Visual Studio Add-ins

To test your new Web app, type something between the <div> tags on your Default.aspx page and run it. If everything checks out, stop execution and go back to the IDE. Under Tools, look at "IBM DB2 Tools" (Figure 7). Basically, you have access to all of your DB2 products through this menu, just as you would the "Start -> Programs" menu. Select Tools -> IBM DB2 Tools -> Command Editor to open up a query window.

Copy the SQL code from Listing 1 below and paste it into your Command Editor window.

Listing 1: Table Creation Code

CONNECT TO AJAXDEMO;

------------------------------------------------
-- DDL Statements for table "NULLID  "."HEROES_NOXML"
------------------------------------------------
 

CREATE TABLE "NULLID  "."HEROES_NOXML"  (
		  "HEROID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (  
		    START WITH +1  
		    INCREMENT BY +1  
		    MINVALUE +1  
		    MAXVALUE +2147483647  
		    NO CYCLE  
		    NO CACHE  
		    NO ORDER ) , 
		  "HERONAME" VARCHAR(50) NOT NULL , 
		  "POWER" VARCHAR(255) , 
		  "WEAKNESS" VARCHAR(255) , 
		  "NOTES" CLOB(1048576) LOGGED NOT COMPACT )   
		 IN "USERSPACE1" ; 


-- DDL Statements for primary key on Table "NULLID  "."HEROES_NOXML"

ALTER TABLE "NULLID  "."HEROES_NOXML" 
	ADD CONSTRAINT "CC1158547603783" PRIMARY KEY
		("HEROID");

------------------------------------------------
-- DDL Statements for table "NULLID  "."HEROES_XML"
------------------------------------------------
 

CREATE TABLE "NULLID  "."HEROES_XML"  (
		  "HEROID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (  
		    START WITH +1  
		    INCREMENT BY +1  
		    MINVALUE +1  
		    MAXVALUE +2147483647  
		    NO CYCLE  
		    NO CACHE  
		    NO ORDER ) , 
		  "HERONAME" VARCHAR(50) NOT NULL , 
		  "NOTES" XML )   
		 IN "USERSPACE1" ; 


-- DDL Statements for primary key on Table "NULLID  "."HEROES_XML"

ALTER TABLE "NULLID  "."HEROES_XML" 
	ADD CONSTRAINT "CC1158547736768" PRIMARY KEY
		("HEROID");

COMMIT WORK;

CONNECT RESET;

TERMINATE; 

This creates the following two tables:

HEROES_NOXML:

HEROID, INTEGER (4), IDENTITY, NOT NULLABLE
HERONAME, VARCHAR (50), NOT NULLABLE
POWER, VARCHAR (255), NULLABLE
WEAKNESS, VARCHAR (255), NULLABLE
NOTES, CLOB, NULLABLE

HEROES_XML:

HEROID, INTEGER (4), IDENTITY, NOT NULLABLE
HERONAME, VARCHAR (50), NOT NULLABLE
NOTES, XML, NULLABLE

Populate the first table by copying the SQL code from Listing 2 into your Command Editor.

Listing 2: HEROES_NOXML Data

INSERT INTO NULLID.HEROES_NOXML
  (HERONAME, POWER, NOTES)
  VALUES ('Mockingbird','Can mimic any sound she hears.','Alter ego: 
Mariah Carey');

INSERT INTO NULLID.HEROES_NOXML
  (HERONAME, POWER, WEAKNESS, NOTES)
  VALUES ('Indigo','Telepathy','Gets cranky if he stays up too late.','Current age: 8. 
  Additional weakness: lactose intolerant.');

INSERT INTO NULLID.HEROES_NOXML
  (HERONAME, POWER, WEAKNESS, NOTES)
  VALUES ('Indivisibull','Super strength; can reattach severed body 
parts.','Not too bright.','Secret origin: a somewhat clumsy man, 
Hank "Bullhead" Durham fell into a vat of fresh glue 
and was promptly struck by lightning. Since then, whenever he 
loses a limb, he has simply to hold it in place and the limb becomes reattached.');

INSERT INTO NULLID.HEROES_NOXML
  (HERONAME, POWER, WEAKNESS, NOTES)
  VALUES ('Dayglo','Glows under a black light.','Not having a black light around.','Usually 
relegated to sidekick, though once played a crucial role in a mission busting an 
underground pot growers cabal.');

INSERT INTO NULLID.HEROES_NOXML
  (HERONAME, POWER, WEAKNESS, NOTES)
  VALUES ('Thumbalina','Ability to text message at light speed.','Thumbsucking 
(a nervous habit in times of great stress).','Former codename: Twiddle.'); 

Populating an XML field requires some special scripting, so you'll get to the second table in a Part 2 .

Check out the following resources to download Express-C and learn more about XQuery and working with XML data types in DB2 9.

Download DB2 9 Express-C

Introducing IBM DB2 Viper Workbench, featuring the XQuery Visual Builder