For what it's worth, I'll give my $0.02. I'm a software developer that has
been working mainly with OLAP databases in the last 5 years. Recently, we
started a new company, and we're now building a product that relies more
heavily on relational technologies. In particular, our product must support
DB2, Oracle 8i, MSSQL 7.x, and MSSQK 2000. I have spent the better part of
the last month creating Schema's rich with referential integrity, stored
procedures, packages and triggers in each of the databases. I'm not biased
for (or against) any particular database. That said, here is what I've
found:
The only database I haven't had any problems implementing complicated schema
and stored procedures in is Microsoft SQL Server 2000. MSSQL 7x is second,
followed by Oracle, then DB2 UDB (for reasons more in depth below). That
said, I've received abundantly more help implementing my solution with DB2
than in any other RDBMS (although I needed more help with that one than any
other). The IBM'ers follow the news groups religiously, and are always
extremely helpful with any problems I've had. One even took time on a
Saturday to help in developing a particularly nasty trigger. Here are the
problems I've encountered with each RDBMS (in no particular order)
MSSQL Negatives
- MSSQL 7.x doesn't support cascading deletes on tables. So, I had to code
triggers to accommodate this. MSSQL 2k however does.
- MSSQL 7.x also doesn't support BIGINT (although MSSQL 2k does).
- MSSQL doesn't come with a JDBC driver - you are forced to purchase one (I
recommend JSQL Connect)
- MSSQL 7.x and 2k only support after triggers. So, if you need to catch
something before it hits the database - too bad.
- No multi-platform story.
Oracle 8i Negatives
- In update triggers, you cannot modify the table that triggered the
update - the result will be something they call a "mutating table" error.
The only way around this is to create an extremely complex package of
"trigger helper functions" and copy data into temporary tables. This is very
painful.
- When returning resultsets from Oracle 8i Stored functions (yes - you read
that right - Oracle 8i cannot return result sets from stored procedures -
you must create them as stored functions), you cannot use standard JDBC
functions to get at the data in the resultset. You MUST import the Oracle
packages, and make special calls (and casts) to get the data back. This was
a particular problem for us because we are writing supposedly *generic* Java
code that should work transparently against all three databases. We ended up
having to write a class factory and creating oracle-specific subclasses to
handle returning data from Oracle stored functions. Very very very ugly. Not
JDBC compliant. Unacceptable (in my opinion).
- The Oracle documentation is awful. It is particularly difficult to follow
their syntax diagrams - it seems purposely layed out to confound the a
developer (that is, a non-DBA).
- The Oracle tools for creating schema are average. But bugs in their tools
prevented me from finding out what errors were in my triggers. I would see
that compiler errors happened, but clicking on the "show errors" resulted in
an empty window. To get around this, I ended up using their SQL Plus tool to
submit the create statements. Any errors could be displayed using "show
errors".
- Oracle was the most difficult to set up and create databases in.
DB2 Negatives
- DB2 requires a C/C++ compiler to be installed on the database server for
SQL stored procedures. What they seem to do is take your SQL stored
procedure code, pass it through a pre-processor which generates C code, and
from that they compile it into a .DLL which is then linked into the
database. Absolutely absurd for deployability. I guess they want our
documentation to read "to install our product, please install a C compiler
on your database server and configure DB2 to know where to find it. Then,
you can install our product."
- DB2's Trigger language is (at least until Fixpack 3) very weak.
- DB2's SQL functions are extremely weak. That is, when coding an SQL
function, you are allowed only a "return" statement. That's it - no
conditional logic, no cool stuff. Fixpack 3 may address this somewhat.
- DB2's JDBC 2.x support is the worst of the bunch. You cannot pass blobs
back-and-forth to stored procedures/UDF's. They don't have Updatable
Scrollable ResultSet, support for the new SQL types (Array, Ref, Distinct,
Java Object) or SQL type mapping. I also haven't seen any support for
connection pooling. For a company that's so big on Java, this is pathetic
(in my opinion).
- IBM's answer to the compiler issue is to use Java stored procedures. This
might be a good idea if you could pass NULL parameters into a Java stored
procedure. But alas - DB2's support for Java Stored Procedures only allows
scalar types (int, not Integer; float, not Float). So, you can't pass NULL
to them. This makes them useless in my opinion.
- DB2 uses OS users only. You can't create users in DB2 outside of the OS.
This is particularly nasty if your under UNIX.
All the negatives aside, there are plenty of positives to each product. IBM
has been the best at answering questions and handling issues. I've had a
reproducable Oracle 8i bug in their thin driver that I've tried to report
for four weeks. Unfortunately, the only way to report bugs to them seems to
be to pay a huge amount for a support package of some type. Only after that
can I tell them the bug that they have. The Oracle news groups don't seem to
be frequented by Oracle - so posting questions or issues there mainly go
unanswered.
To avoid appearing to be totally negative, I'll post some positives - :-)
MSSQL Positives
- It is very fast. Of all the RDBMS' tested - it was the quickest (1.5 Ghz
Desktop w/512 MB Ram, Windows 2000, inserting about 280,000 rows into
multiple tables).
- The doc is extremely easy to use, and very accurate.
- The data import/export is by far the finest of the bunch.
- MSSQL is extremely easy and intuitive to use. I wish all the other
products were half as intuitive.
Oracle Positives
- Stored procedure language is very powerful.
- Triggers are very flexible (excluding the issues I raised above) -
before/after triggers, etc. The trigger language is identical to their
stored procedure language.
- Oracle Packages are very convenient and cool.
DB2 Positives
- Friendly, smart IBM'ers monitor this news group and are happy to help
with problems and issues.
- The documentation is fair - not as good as MSSQL, but much better than
Oracle
- Really easy to create and configure databases. Almost as intuitive as
MSSQL.
Anyway, that's my $0.02. Hope it helps.
Marc
Received on Sun May 06 2001 - 18:43:21 CDT
