DB2 - A First Look
My company is moving towards DB2 as a standard. Not necessarily in all areas and I'm not particularly worried about SQL Server being displaced, but moving forward, most of our software is targeted for DB2. So I got stuck into an installation/admin class for a week. After a day and a half, I decided to drop a few notes on DB2 for you all to be aware of how your life with SQL Server compares.
My first impressions? Well, as usual, I was late for the first day. The class started an hour earlier than I normally arrive and my home life was a little crazy. Fortunately it was being taught at our internal training center by an employee, so I wasn't too concerned.
Everyone had installed DB2 (on W2K) when I arrived, so I got the install path and ran the setup. Let me say that it was a breeze. Like SS2K, you supply a name and password for the system admin (it's created for you on the local machine) and basically hit "next" and bunch of times. I also applied a patch, fixpack in IBM speak, and that went very easy.
One of the first things I noticed is that on reboot there is a "First Steps" app that runs, similar to Microsoft's Manage Your Server" dialog that you get when you first log in. It contains create sample db, tour, etc., stuff that you'd want to do on your first install. From there, however, I wasn't so thrilled. I've got a few notes on areas that I thought were worth addressing.
Tools
The tools are written in Java. So they look and act slightly different from a native Windows app. Refreshes aren't as clean, scrolling doesn't always work, some of the hot keys are funky, etc. Not necessarily a bad thing, but certainly a double edged sword.
First the annoying things. Focus doesn't always drop into the first control. So when you create a new database, the name field is highlighted, but there's not focus. A quick "tab" hit gets you there, but that's slightly hokey. Sometimes the refresh doesn't work so well. Especially when something hangs. Not sure I like the blank window in Java any better than the one in EM, but it behaves different. There's a command line, actually 2. One seems to drop you into the command window at the DB2 install folder, the other seems to run the isql like command processor called, appropriately, db2. The command line processor runs like dos, every time you hit return or enter, it submits the command. Makes longer statements hard to follow. Score for isql's numbering and submit on "go".
The scripting tool that resembles QA has some issues as well. Only returns one result set, access plan which is like the execution plan, is a little hokey. Have to click and select to get details. Save/load is a little cumbersome. The toolbar is the most annoying. All of the tools have links to the other tools, in this case, like 6 tools. But on the toolbar of the scripting tool (Control Center), there is no open or save button. There's an execute, and there are hot keys, but not having common buttons seems funny to me. If I get a bunch of results in the interactive tab, the results tab, kind of like a "paper tape" cannot be cleared without clearing the command window. Not a huge deal, but it's unpolished.
Now to the Java part. Oracle, IBM, and others would point to their tool being written in Java as a plus. "It looks like a Windows app!". Whoopty Doo!
Who cares? I want it to work for me. Having it in Java means that they write once, run everywhere, so less resources for them. Again, who cares? Aside from shareholders, users like me don't. It does mean that my platform doesn't have to wait for the tool to be updated for me or ported. That's good. However on Windows, it seems the performance sucks. Well maybe not sucks, but it is funny and noticeable. Maybe I'd get used to it, but write once, suck everywhere doesn't seem to be great to me. Works well for IBM in saving money and shipping products, but I'm not sold it's a good idea.
I do like the single right click and select to get the first 32 rows from the table. I can then filter these, get the next ones, etc. Much better than the all or 1000 multi click, select, behavior in EM.
Architecture
I can't delve too deep here, but the architecture is more multi file based rather than the 2 (basically) file approach of SQL Server. You specify a database as having "table spaces" and "containers" and then drop tables in here . There are system managed tables spaces and DBA managed ones and I'm not sure why the system ones are there. Supposed to be less admin, but not sure that's a good thing. We created table spaces and created databases without tablespaces, so I'm still confused on day 2.
This is a perfect opportunity for me to shoot myself in the foot. Actually to shoot my foot off.
I've been working with SQL Server for twelve years. With SQL 2000 for nearly 3 years and I still am learning about how to work with the system. And still learning more about what settings and values mean to me. I cannot imagine if I had to decide whether the container settings were properly tweaked for each drive. Some might say that you leave it at the defaults. OK, but if that's the prevailing wisdom, why include the knob? I'm willing to bet that most DB2 DBAs, not all, but most, like most Oracle DBAs I've run into are barely competent. Not stupid, but more ignorant. And I'm betting the reason that the good ones are very highly paid is that the sheer knowledge required to tweak the system to decent performance is overwhelming. Most probably run defaults for everything so anyone with a little knowledge can make an improvement. Just dealing with this crap is probably why average DBAs are paid well to run Oracle. Not that I dislike Oracle or think it doesn't work, it does. But I think it's overly cumbersome.
I'm sure I'll get some argument here, but the reality of most places, in fact I'd say the vast majority, is that the DBAs don't have great knowledge about the loads and how to best tweak the server. Even if you do, can you do it fast enough? I tend to agree with Microsoft that you can't. Let the server manage it's settings in real time to match the workload. Can SQL Server get better? Sure, but so can DB2, Oracle, etc. In fact, I was working on v7, but DB2 v8 includes an auto tuning wizard/process that is shown to be better than 98% of DB2 DBAs. More on that in another article.
Conclusions
Overall, what do I think?
Personally I don't really have any great bias. I'm more comfortable with SQL Server, but the reality is that I think both products work well. They both do the job and I would work with either one. And I'm sure I'd be as good a DB2 DBA as a SQL DBA. I think both products are good databases, have different strengths, different weaknesses and neither is better (substantially) that the other. Some will fit in one environment better than another, but both fits in many places.
One thing I did find very interesting. DB2, written by IBM, where Dr. Codd worked, the grandfather of relation databases. I issued a simple "set current schema information_schema;select * from tables" at my command prompt. Guess what?
It didn't work. The SQL-92 standard isn't there. I wonder if it's in Oracle 9 or DB2 8? 🙂
Steve Jones
©dkRanch.net May 2003