|IBM DB2 Express-Getting Started|
|Motivation: Tryout the major databases for ease of install and
Feature: IBM's DB2 Express provides a quick entry into DB2 database processing
IBM is pulling out all the stops to match the ease and low cost of trying out Oracle and MySQL databases - DB2 Express is available for ready download (but be forewarned, depending on all the bells and whistles even DB2 Express weighs in at over 450MB - so be prepared with DSL or a long download).
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.
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.
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
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:
Top of Page Home Tutorials SQL Overview DB2 Loading