Install

The install process was faster than the download and largely trouble free. Like the DB2 Information Center install shown above, DB2 Express heads off a lot of problems by giving a detailed set of Installation Prerequisites and Release Notes - it is highly recommended that you consult both before doing an install. For example, I discovered that DB2 Express requires Windows XP Pro - and sure enough XP Home does not qualify.

The other nice thing about the install process, that went off without hitch, is that you can immediately test the database from the install dialog by loading a sample database or creating your own. This is quite useful because it allows a user to confirm/test the installation right from the installation dialog - and DB2 has all sorts of useful advisories and dialogs on what to do next(Where the logs are and what to look for etc). It is also reassuring to see your own database up and running. By the way the sample customer and data warehouse databases are both well worth installing - they consume little space but provide lots of answers to "how do you do this?" or "Will this work?" questions.

Creating Your Own Tables

The DB2 Express people have to stay on their toes because with the new PHP IBM Cloudscape partnership DB2Express will have the masters of simplifying, PHP and MySQL, as implied rivals. Fortunately, the DB2 Control Center is extremely rich with functionality - and works like a Russian egg - click a command icon or menu and a series of dialogs and wizards carry you through the task. Truly Control Center is exactly that.

So to create our own tables in the Sample database we expanded the treeview (leftmost pane in the screen shot). Then right click on the tables folder and choose the Create Table menuitem. This triggers the Create Table Wizard that takes the user through the 7 step process of creating a table. As it turns out this is wizard (see Create Table Wizard 2 screenshots below)is very helpful because some of the advanced features of DB2 such as Key assignments, Tablespace allotment, Clustering dimensions, and Table constraints (alternatives to triggers and stored procedures)are handled with clarity using this wizard.

But the first step is to define all of the columns in your table. Actually the first step is to have a good database design to which the DB2 Information Center Help has a solid section of advice and info on Designing tables (look for that major topic when you start up Information center).users also have the full array of datatypes 3 type of INTs, 4 types of CHAR including CLOB, 4 types of BINARY including BLOB, 4 types of GRAPHICS including DBCLOB, 3 types of WCHAR, DATE, TIME, TIMESTAMP, DECIMAL, NUMERIC, REAL, FLOAT, DOUBLE plus UDT-User Defined Types.

The Add Column dialog works best when users hit the Apply button rather than the OK - this saves popping up the dialog each time a new field is defined. Also users need to know that Default Values for all CHAR, DATE, TIME, and TIMESTAMP variables need to be enclosed in single quotes (not double quotes nor no-quotes). Also if you intend to use a Formula good luck finding out how to do so in the DB2 documentation. We have been at it off and on with the help of IBM DB2 Labs in Toronto's help for the past two weeks and and have yet to find the write stuff.

When you are finished defining all the columns in your table; the next step is to choose a Tablespace. This is an advanced feature for many users - and so the default option will work fine. But DBA's and performance tuners will use this option extensively to wring the best performance out of the database.

The next step is designation of keys. Again this is at the heart of database table design, normalization and referential integrity - and we would refer users to the Database books by Chris date or Jeffery Ullman to fill in the blanks. Suffice to say that the primary key definition is vital and users must not have checked the nullable option for a field if they want to use it as a primary key

The next step is to define Dimensions. Dimensions are associated with massive tens of gigabyte tables. Again to optimize performance users can force fields that are commonly searched on to be clustered together - say the MONTH and YEAR in a historical transactions table. Again, most users can leave this input empty.

Check Constraints maybe another thing. There is an active debate in the development community as to where business rules should be enforced. Centrally in the database server, even more centrally in a general business rules server, or individually in each application's validation step on data entry. For the sake of not pulling hairs I like to have Check Constraints on Primary Key and Foreign key fields in the database. Think of it as a last line of defense. A few extra CPU cycles are worth it for peace of mind. And most databases including DB2 Express have Load utilities that allow bulk loads with the option of turning off triggers and constraint if cleansed data can be provided. Again, as seen below the syntax is fairly straight forward but good luck finding it in the documentation.

So now that we have run through the table definition gauntlet it is worthwhile making some quick observations. MySQL, SQLite, Access, and other database users will ask why all the step. And the answer is simple - DB2 is rich and we still have not taken into account Spatial data and User Defined Types. As databases reach the gigabyte++ range users will appreciate having these control. So just relax, hit the Next button and watch the final step.

Now there are three very nice features to the Table Create Wizard. First, like in most wizards, you can backtrack all the way to the beginning and change any and all values. Very handy. Second, as you can see from the screenshot, the Wizard displays the necessary SQL to create the table and users can store it away for later re-use, templating or simple review. Finally, if you click Finish and then DB2 still issues a error condition - it happened on a simple table creation when I did not know that values had to be in single quotes, then the Wizard stays put and allows the user to step back and review the flagged settings and make error corrections. Nifty again - since I have seen other database Wizards desert me just when when the going gets rough.

Control Center Operation

Now that you have a table lets use the Control Center to inspect and add values to the table. But first a word about the Control Center itself. DB2 uses the Control Center to be exactly that - the central starting place for administration and control of the DB2 databases. Note the plural. All the DB2 instances can be tracked from the Control Center and users will find it to be a very rich utility for creating and modifying tables, doing complex queries and analysis, monitoring the status of the databases, for loading or exporting bulk data, etc. In general users will find that the Java-based Control center is a rich interface into DB2 which has a uniform look and feel across DB2's Linux, Windows, AIX and other platforms. Oracle is using a Web browser approach for 10g to apply a similar uniform look and feel.

Control Center can be started from the menu Start | Programs | IBM DB2 | General Administration Tools | Control Center, or by right clicking on the Db2 icon in the system tray in Windows and choosing the Control Center option. Then in the treeview of databases and their objects - expand out the database (HAPPIER in our case) and click on the Tables folder. After a few seconds a list of the tables in the HAPPIER database is shown in the upper right content pane. We choose our TESTDATES table and then all the information about that table appears in the lower right content pane.

Clicking on the Open hyperlink in the lower content pane brings up the Open Table dialog which displays the first 100 rows (or less) of the current table. But even more important, users can easily add new rows or change the values in existing rows. When testing or debugging report writing or data entry programs this is where I camp out to confirm that the programs are working properly.

However, as it turns out IBM has made data entry through the Open Table dialog a bit tricky. Date fields have to be entered according to the regional standard which varies by continent and country. Again the documentation is remiss and only through the IBM Toronto Software labs were we able to find out that for Canada the regional standard for dates is: yyyy-mm-dd with no quotes around. Control Center would really profit from much more relaxed date, time and timestamp data entry or the use of a date picker component. Likewise, entry of any large objects is

Summary

IBM has done an admirable job of making DB2 accessible to small and medium size businesses and time-constrained developers. The download is about half an hour on a DSL line but the install is simple and will likely be done in half the time of the download. IBM has gone to extra effort to make the sample data and the getting started exercises easily available from the Install dialog. Take advantage of these options.

IBM stubs its to on the documentation (see the yellow warning highlights above)and running of Control Center; but this is a very powerful and generally convenient to use admin console. DB2's is just as easy to use as MySQL's Administrator but Control center packs a much more powerful punch as clustering, managing of tablespaces, replication, and a whole host of autonomic features are available/controlled from Control Center:
- automatic database backup
-automatic statistics collection and profiling
-automatic reorganization and tuning advisories
-self tuning back-up and restore
-automatic log management
-database design advisor
-adaptive throttling utilities
-database health monitor
You are not in Open Source Kansas anymore, you can do stored procedures, triggers, views and materialized views which are beyond the reach of many Open Source databases. But also you have nearly the full functional power of a top of the line database at your finger tips for free development and testing purposes. Still skeptical ? See our next tutorial on loading in a football database table and then decide if DB2 Express is worth a test drive.