DB2 Students Group

I’m currently developing a Industry Format bundle based on XML standard – METS. To do such thing I had to learn some basic but still powerful features of DB2 9. At the moment my bundle only registers some XML schemas and use them to validate the insertion of some XML samples.

I’ll keep my promise to teach everything I learn, then I will teach three things:

1. register XML schemas

2. insert XML documents without validation

3. validate XML documents insertion

Registering XML schemas

If you dont know what schema registration is, you can understand it as “give my DBMS the ability to verify XML structure correctness against a specified XML schema”. By the way, you can easily create your own schemas to your XML documents with an IBM tool named WebSphere Studio. All you have to do is load the XML sample and select Generate -> XML Schema.

I’ll assume that you read the Express-C: Starting tutorial so that I can use the test.items as my table example.

Assume that you have a XML schema in schemapath named xschema.xsd and remember that XML schemas are only XML documents that dictate the sctructure other XML documents.

So, to registrate it you should run in the CLP:

register xmlschema ‘xschema.xsd’ from ‘schemapath/xschema.xsd’ test.xschmema complete;

…or from your favorite shell:

db2 “register xmlschema ‘xschema.xsd’ from ‘schemapath/xschema.xsd’ test.xschmema complete;”

This should work, however there is something you should know. When a XML schema becomes too large it’s good practice to separate it in several files. In those cases the schema registration becomes quite different.

Assume that xschema.xsd was divided in two XML schemas – xschema1.xsd and xschema2.xsd. The schema registration should be like this:

register xmlschema ‘xschema1.xsd’ from ‘schemapath/xschema1.xsd’ test.xschema
add xmlschema document test.xschema add ‘xschema2l.xsd’ from ‘schemas/xschema2.xsd’
complete xmlschema teste.xschema

Inserting XML documents

You can insert XML documents in a given table as you insert a string or an integer:

insert into items values (96, ‘João Moura’,’student ambassador‘)

However in most cases it’s kind of difficult to insert the XML document in one single line or one by one, so we use a simple script to specify a list of XML documents and eventually their schema. Here is an example saved in “script.del”:

1,”João Moura”,<xds fil=”joaoinfo.xml” sch=”xschema.xsd”>
2,”Quim Barreiros”,<xds fil=”quiminfo.xml” sch=”xschema.xsd”>
3,”Nuno Veloso”,<xds fil=”nunoinfo.xml”>
4,”João Ferreira”, </xds></xds></xds>

You can say the values to insert and possibly omit some. Although XDS (XML Data Specifier) tag syntax is self-explanatory its worth noting:

<XDS ../> – the tag itself
FIL=’ ‘ – the name of XML document
SCH=” – the XML schema describing the document structure

And then use it to import several entries in using only one line:

import from script.del of del xml from ‘dirofxmldocuments’
insert into test.items

All that it does is execute sequential insert‘s reading the output from script’s rows.

Validating XML documents

I’m tired of writing so i’ll be quick:

import from script.del of del xml from ‘dirofxmldocuments’
xmlvalidate using xds
insert into test.items

Conclusion

This is all I can give you for now but be aware of more tutorials and remember:

The more knowledge you share with others the more knowledge will be shared with you.

Hope you enjoy.

1. Introduction

I’m writing this to help newbies starting Express-C.

First of all you’ll need to install a free version of DB2. You can get an old but functional deb here or you can click here to get an up to date how-to installation guide.

To start using Express-C and DB2 in general you need to know something about it’s hierarchy: the master’s name is DAS and the databases admins are known as instances. As you can see in the figures below, you can have multiple instances with several databases inside. There is only and only one DAS per installation and one or multiple instances (database managers).

(you should click to enlarge them)

2. Starting

In UNIX installations DAS and instances are just ordinary UNIX users with some profile specifications. So, you will need to switch to one of those users and load the profile:

geko@hydra:~$ ls /home/
cesium  dasusr1  db2fenc1  db2inst1 geko
geko@hydra:~$ su db2inst1
Password:
hydra:~$ . sqllib/db2profile
hydra:~$

Note that the creation of instances is not covered in this tutorial, however if you have installed the deb one instance was created.

Before we can work in a valid instance, we need to start one. In the deb installation you can run:

hydra:~$ /opt/ibm/db2exc/V9.1/adm/db2start
SQL1063N  DB2START processing was successful.

…and to stop it:

hydra:~$ /opt/ibm/db2exc/V9.1/adm/db2stop
SQL1064N  DB2STOP processing was successful.

3. Just a tip

If you are a smart guy (ok, lazy) you can change where you shell looks for programs. You can do that editing instance’s profile. In our case:

hydra:~$ echo "PATH=$PATH:/home/db2inst1/sqllib/bin:/home/db2inst1/sqllib/adm" \
>> ~/sqllib/db2profile

4. DB2 interpreter

This is the situation: we are logged as a valid instance and we have started it. To use the db2 interpreter and start testing db2/sql commands you should type:

hydra:~$ db2
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 ADCL 9.1.2

-- omited output --

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 =>

Just as an introduction I’ll show you some valid commands.

4.1 Create database

In DB2 Express-C, only Unicode databases can store both XML documents and more traditional forms of SQL. Although this tutorial doesn’t cover XML stuff you should keep that in mind because that’s the exact reason why DB2 is so powerful. All commands are similar to SQL syntax. For instance:

create database test using codeset UTF-8 territory us

and

connect to test;

create table items (

  id          int primary key not null, 

  name   varchar(30),

  stuff    xml

  );

5. Conclusion

As I’m just learning, this is all i can give you. All i can promise you is that the more i learn the more i teach.

Get in touch for more advanced tutorials.