IBM has become legendary in the Linux community for its repeated announcements and reannouncements regarding Linux support. It seems every six months at some Linux show or even a general PC show, IBM pledges across-the-board Linux support. That is all very odd because since Big Blue's first altar conversion a couple of years ago, it has done a great deal for the Linux community (despite purported resistance from the AIX group), but by constantly reproclaiming its allegiance, it gives the impression that it never followed through on prior pledges.
The truth is that IBM's many contributions to Linux have been typical Big Blue: very practical and thus very boring. Jikes, IBM JDK, Apache patches, and a Linux port to the 300 series mainframes (someone was very bored) are not exactly the sorts of goodies that have a Linux pundit hopping up and down, but they provide real, solid bases for businesses looking at Linux for critical tasks. Not that IBM is incapable of splashy software: its remarkable Alphaworks project has contributed rather nifty stuff to the Java and XML communities (pretty much all of which runs on Linux).
Besides contributing to free Linux software, IBM has been supporting Linux with its popular and well respected commercial offerings ("nobody ever got fired for buying IBM"). IBM has made moves in that direction from both the hardware end, such as the Thinkpad, to the software end, such as the subject of this article: DB2.
IBM first ported its Universal Database to Linux in version 6.0, in the great 1998-1999 wave of commercial database ports that also starred Oracle, Informix, Borland, and Sybase. In fact, DB2 for Linux was originally to be available at no charge, and it only became payware after IBM noticed high demand from corporate customers. Big Blue has followed through with Linux versions of each subsequent release. Promising the full panolpy of enterprise database features, from a robust ANSI SQL-92 core to Object Database (if not yet SQL-99) extensions to administration, network, performance tuning, and replication tools, to extenders for geospacial systems, XML, hard-core financial number-crunching, et cetera, et cetera, et cetera.
I downloaded IBM DB2 personal edition from the source (see Resources). There was an option to order a CD, but the last time I ordered a DB2 CD from IBM (UDB 6.2), delivery took over two months, and I wanted to have a look this year. Besides, linuxpecmn.tar was a pretty modest-sized download (relative to its peers) at 75 MB. You might be wondering why IBM doesn't gzip it as well, but it turns out that most of the archive contents are RPMs, which already compress their contents.
Installation
My review machine is a Pentium II 500 with 256 MB RAM and 4 GB of disk space available for DB2. The operating system is the KRUD distribution July 2000 release, based on Red Hat 6.2 (see my last Oracle article -- linked in Resources -- for more on KRUD).
If you caught my Oracle article, you'll remember how harshly I criticized the 8.1.5 Linux release for installation and database creation difficulties ("tortures" would be more accurate, if too colorful). In that article, I might have mentioned that Oracle's terrible installation process on Linux was probably no worse than that of IBM DB2 Universal Database for Linux 6.1, which I actually gave up on trying to install and set up.
Part of the problem was the infamous confusion Red Hat introduced through its botched handling of the migration to Glibc 6.0 in Red Hat 6.1. But even after dodging the bullets that sent at the DB2 user in either of two ways -- by trying an install under Red hat 6.0 or by following detailed, unofficial instructions for install under Red Hat 6.1 -- there were many remaining problems with the installer and in the DB2 tools. Judging from my experience and the size of the resulting DB2 6.1 fixpaks, it was pretty much of beta quality. And so I sat down to install DB2 7.1 with some trepidation. Was I in for more of the IBM DB2 6.1 and Oracle 8.1.5 treatment? (See sidebar for a brief update on whether Oracle 8.1.6 is any more lenient for the mortal user).
The fact that I was able to get Oracle 8.1.5 installed and not DB2 6.1 was probably not because Oracle demanded less indulgence but because in addition to DejaNews and Technet, excellent third-party HOWTOs were available by people such as Jesus Salvo. DB2 had rather few such resources that I could find until recently.
This time there is a HOWTO put together by Daniel Scott of IBM and available from the Linux Documentation Project (see Resources). Knowing IBM's culture of brilliant individuals within a stultifying bureaucracy, Mr. Scott probably got tired of DB2/Linux frustrations and took the initiative to put together the HOWTO but, regardless, it's an excellent resource. As a bonus, it covers a wide range of distributions, including Caldera OpenLinux, Debian, Red Hat, SuSE, and Turbo Linux.
Armed with that, I dove into the install, starting with the installation of the pdksh RPM from the KRUD CD.
[root@euro /root]# rpm -i /mnt/cdrom/RedHat/RPMS/pdksh-5.2.14-2.i386.rpm Then I ran db2setup from the untarred DB2 Personal Edition root directory [root@euro s000510.personal]# ./db2setup & That, of course, is less flashy than the Java-based installers Oracle and Sybase have moved to, but it's far more readable and less idiosyncratic.
I selected the Administration Client, then Customize. and enabled Control Center. I selected DB2 Personal Edition, then Customize, and enabled all options except for the Local Control Warehouse Database and Asian code page options. I then selected DB2 Application Development Client, Customize, and selected Create Links for DB2 Libraries. The next screen allowed automatic creation of a database instance, which I opted for, using the default user names for the administration. I also opted to add sample data to the created instance. I opted to set up distributed join on the database instance, which allows SQL statements to involve multiple database instances. I also opted to Create the Administration Server with all default options.
Finally, after a stern "Are you sure?" warning, the installation went merrily off, displaying a single-line status message with the currently installing component. It left a comprehensive log of its actions at /tmp/db2setup.log.
So far, so good. None of the problems I ran into with DB2 6.1 -- freezes and screen-scrawling. With considerable relief, I found I had what appeared to be a working DB2 install and database instance with little or no fuss.
One final and often neglected aspect of installation is deinstallation. It's nice to know that if you ever cease to need a package or decide against using it after installation, you can remove it cleanly from your system. Windows users have the capable InstallShield for that, but Linux packagers often each do their own thing. Luckily, since the DB2 is RPM-based, uninstallation is simple enough and accomplished by running the db2_deinstall script from the package, CD, or installed location. Oracle also provided uninstallation support but through a custom interface.
Getting started
DB2 comes with many nice GUI utilities, all using Java for display (which seems to be the standard modus operandi of Linux database companies). To use it, you must use IBM's JDK 1.1.8 for Linux, no other version, no other vendor. Write once, run anywhere on any JVM, right? Ha. At any rate, IBM's JDK is the fastest available for Linux, and they hope to have a 1.3 version soon (not that you'll be able to use it with DB2 UDB 7.1) See Resources for the download link.
I downloaded the RPM and installed it:
[root@euro V7.1]# rpm -i /root/packages/IBMJava118-SDK-1.1.8-4.0.i386.rpm Then I added the following environment entries to the various DB2 users: export JAVA_HOME=/usr/jdk118export PATH=$PATH:$JAVA_HOME/binexport CLASSPATH=$CLASSPATH:$JAVA_HOME/lib/classes.zipThat set up the environment for bash. Note that if you're using some other shell, you might need a different format for commands to set up the environment. Note, too, that the DB2 installer places its environment setup script in the ~/.bashrc script rather than ~/.bash_profile.
After setting up the environment, I logged in to the db2 administration user and fired up the DB2 control center.
[db2@euro db2]$ db2cc &
And I promptly ran into problems. The DB2 splash window panel came up and seemed to be hung until I noticed that there were the outlines of another window behind it. I wondered whether it was an error message of some sort, but the splash screen refused to get out of the way and obscured any controls for bringing it to the front. I was running the default Enlightenment window manager under GNOME, so I ended up switching to Sawfish (with which I'm more familiar), and I played with the config until I was able to force the error message dialog to the front.
So there it was in all its ignominy: "[IBM][JDBC Driver] CLI0602E Memory allocation error on Server" (see Figure 2) Eh, what? Again this is a machine with 256 MB of RAM and though top did show that all RAM was soaked up (most of it hogged by the Java Runtime Environment, of course), 120 MB of swap space was free. So I shrugged and closed the error window expecting to have to get out my medieval system administration tools to get to the bottom of it. Suddenly, to my surprise, the control center started up just fine despite the dire warning.
However, now the DB2 control center window was empty. I was familiar with that tool and knew that I should have seen entries representing my system and the database instances set up therein, but there was nothing. I closed the control center and restarted, wondering whether the memory error was the culprit. Upon restarting I got the memory error again, but that time I did get the entries I expected for my system and a sample database instance. The bother didn't stop there, though. On clicking on the database instance icon, I got a message that the database was not started. Odd, since I opted to have the database start on bootup.
So I went digging. DB2 sets up an /etc/rc.db2 and adds an entry to the /etc/inittab to start DB2 in runlevels 2, 3, and 4. Of course, X Windows on Red Hat (and most distros) operates in run level 5, so DB2 wasn't getting started. I checked just to be positive, and sure enough, DB2 didn't start after an explicit run level change (using telinit) nor after a reboot.
But one thing the reboot did seem to cure was the JDBC memory error on control-center startup. I guess the installer must have seized resources which it refused to give up when done. Unless you want to spend time figuring out exactly what rogue processes are involved, I'd suggest a reboot after DB2 install (what a shame!).
At any rate, although I expect that not many people besides database reviewers run databases on machines with X Windows, for those that do, one can fix DB2's autostart by changing the /etc/inittab from db:234:once:/etc/rc.db2 > /dev/console 2>&1 # Autostart DB2 Services to db:2345:once:/etc/rc.db2 > /dev/console 2>&1 # Autostart DB2 Services Note the added "5".
Finally, Control Center was content, and so was I because Control Center is quite nice. It's generally more responsive, and it has more options than the very similar tool from Sybase, and it abstracts all the command-line and editing tasks that help make Oracle such a pain. There are still times when it takes ages to pop up windows and refresh screens, but there was no clear pattern to that behavior.
One of the tools in the Control Center is the Command Center, which is a GUI-based query tool for Dynamic SQL, IBM's extended ANSI SQL command set. It allows you to enter individual commands and scripts into DB2 instances, and presents the results in a nice spreadsheet-like display. For instance, firing up the Command Center, using the DB2 browser tool to connect to the SAMPLE database (or just entering "connect to SAMPLE;" in the Command text box, then entering select * from staff; and then pressing CTRL-ENTER to execute the contents of the Command text box, resulted in the window in Figure 4. I must keep harping on how slow those Java tools are. The Command Center window was very sluggish in displaying the result set. I remember using a very similar DB2 tool under OS/2 Presentation Manager years (and several cycles of Moore's Law) ago that displayed results with considerable alacrity. I guess that's the price for having a cross-platform UI.
But most Unix purists won't even bother because they'll be too busy enjoying the command-line tools DB2 provides. The main ingredient is the db2 command, which can manage database commands across invocations, maintaining the session. That provides great administrative power, especially when database management and programming needs to be integrated into scripts. A sample session is very simple. First, the database connection is made:
[db2@euro db2]$ db2 CONNECT TO SAMPLE
resulting in:
Database Connection Information
Database server = DB2/LINUX 7.1.0
SQL authorization ID = DB2
Local database alias = SAMPLE
[db2@euro db2]$
and then subsequent commands can be issued:
[db2@euro db2]$ db2 "select * from staff where id < 100" which earns you:
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ --------- --------- 10 Sanders 20 Mgr 7 18357.50 - 20 Pernal 20 Sales 8 18171.25 612.45 30 Marenghi 38 Mgr 5 17506.75 - 40 O'Brien 38 Sales 6 18006.00 846.55 50 Hanes 15 Mgr 10 20659.80 - 60 Quigley 38 Sales - 16808.30 650.25 70 Rothman 15 Sales 7 16502.83 1152.00 80 James 20 Clerk - 13504.60 128.20 90 Koonitz 42 Sales 6 18001.75 1386.70 9 record(s) selected.
[db2@euro db2]$
At any time, you can jump into interactive mode:
[db2@euro db2]$ db2
(c) Copyright IBM Corporation 1993,2000
Command Line Processor for DB2 SDK 7.1.0You can issue database manager commands and SQL statements from the command prompt. For example: db2 => connect to sampledb2 => bind sample.bndFor general help, type: ?.
For command help, type: ? command, where command can be the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command or ? CATALOG for help on all of the CATALOG commands.
To exit db2's 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.
Here's another example:
db2 => select * from staff where dept = 20
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ --------- --------- 10 Sanders 20 Mgr 7 18357.50 - 20 Pernal 20 Sales 8 18171.25 612.45 80 James 20 Clerk - 13504.60 128.20 190 Sneider 20 Clerk 8 14252.75 126.50 4 record(s) selected.db2 => quitDB20000I The QUIT command completed successfully.
[db2@euro db2]$
As you can see, just issue Dynamic SQL commands at the db2 => prompt, or quit when you're done. In either mode, to close the database connection, just use the DISCONNECT command:
[db2@euro db2]$ db2 disconnect SAMPLE
DB20000I The SQL DISCONNECT command completed successfully.
[db2@euro db2]$
Online information
DB2 combines system information and online documentation into the Information Center tool, which you can invoke from the command line as follows:
[db2@euro db2]$ db2ic &
That brings up a huge index. The Information Center looks like a Web/Java evolution of IBM's venerable Bookmanager documentation system, and sure enough, the DB2 information is very well organized, indexed, and associated in several views, with each tree-view icon launching a chapter in my Web browser. The search function does not seem to come with DB2 Personal Edition, reducing the utility of the Information enter somewhat.
Whaaa! No XML for Linux
DB2 has not been left behind in the stampede to support XML. Oracle, IBM, Software AG, and Informix have all announced major XML integration tools and initiatives. Oracle's XSQL, Intermedia, and other facilities are available for Linux already, but everyone else seems to have trouble seeing past Windows XML users: Software AG might have been a Linux database pioneer with Adabas, but its intriguing Tamino Native XML DBMS is NT and Solaris only so far and alas, IBM's DB2 XML Extender is Windows only. I had hoped to make the developer's section of this review focus on XML, but that will have to wait until IBM is ready. More on that sad state of Linux/XML/DBMS affairs will be covered in another article.
Conclusion
IBM's DB2 is probably the most mature DBMS on the market, and it shows in many respects. It feels like a weird cross between the old, unhip IBM and the new e-Business IBM. (Even though all my DB2 experience has been using SQLC, using DB2 on Linux I almost feel as if I should be firing up GNU COBOL for application programming!) I've reported on several glitches I ran into during postinstallation, but after that, and if one discounts the occasional slowdowns in the management tools, DB2 proved solid and accessible during management and programming. IBM's pricing structure, which is uniform across most platforms, is also very flexible, ranging from $359 for the Personal Edition to the $499 (until December 13) Developer's Edition to the grand Enterprise Extended Edition. It is priced at $17,500 per processor, and it includes revenue-sharing options and other dot-com friendly packages.
One nice thing about using IBM products is that IBM takes defect management very seriously. You always have a clear idea of how to proceed after troubleshooting. You look for an APAR (Authorized Program Analysis Report): IBM-ese for a bug report, which defines a very clear path through remediation. A couple of handy references are the Technical Library for DB2 and the DB2 downloads page (see Resources) where you can find fixpaks and add-ons. Also, if you use DB2 heavily, it might be a good idea to keep up with the International DB2 User's Group.
There are a few third-party Linux DB2 tools such as J Enterprise Technologies' DataBrowser and DataManager 2.1, Java tools for browsing and managing a variety of databases including DB2 and MySQL. Easysoft's SQLEngine allows queries and applications to work across heterogeneous databases, including DB2 Postgres and MySQL. I've already discussed OpenLink's ODBC drivers for Linux, which support DB2 as well as Oracle, Sybase, and others. All of them are commercial and listed in the Resources section.
Nowadays, the hardest choice for the prospective commercial Linux database user is deciding which product to use. At the very high end, it is a very close matter between Oracle and DB2, and though Oracle has ported more of its enterprise add-ons to Linux, DB2 UDB 7.1 gives IBM the edge for Linux users by providing superior ease of use, stability, and a variety of tools.