Re: Which one is better? Oracel 9i or DB2 7.2??

From: The Nomad <nobody_at_nowhere.com>
Date: Fri, 03 May 2002 15:27:32 GMT
Message-ID: <E%xA8.386545$K52.62427326@typhoon.tampabay.rr.com>

> I know that both of them are famous and occupied large market share. But
> can some tell me, or has link to the research document which can compare
> these two products objectively.

Hi - I think I can do just that - objectively. Let me give you some quick background.

I've work for a software company that produces a product that is 100% Java, and runs in J2EE application servers. Our product has data and metadata that needs to be stored in an RDBMS. My requirement when we started was to support Microsoft SQL Server (7 and 2000), Oracle 8i, and DB2 UDB.

My approach to the problem was to try to have as much in the database layer as possible (meaning less code in Java). Since there are differences (some subtle, some not so subtle) between JDBC Drivers, SQL dialects, etc., I chose to use Stored Procedures/Stored Functions for all interactions with the database. This was for two reasons:

Provide an isolation between our code, and database-specific stuff.
Reduce the amount of Dynamic SQL which should allow access paths and optimizations not normally afforded to DSQL.

So, that's what I did - I implemented our repository in 4 different RDBMS (yes - MSSQL 7 is slightly different than MSSQL 2k).

Here were my general findings/pain points between the databases...

Microsoft SQL

SQL Server 7 doesn't support BIGINTs, or cascading deletes. So, because of the requirement to support MSSQL7, we had to use regular integers for all of those types of fields. I also had to manually code into stored procedures the deletion cascading logic. This was painful.

MSSQL 2000 caused me very little pain at all. I didn't have anything that I couldn't implement in stored procedures or their trigger language. The downside is that MSSQL 2k only supports after triggers. I'll also note that the JDBC driver for MSSQL 2k from Microsoft is extremely slow. I recomment JSQL Connect. MSSQL is single-platform only.

MSSQL 2k 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 MSSQL 2k 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 8i

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 arrays. 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).

Working with BLOBs in Oracle (at least from JDBC) is very difficult. Particularly inserting and updating BLOBS. This is because they use BLOB Locators. So, you have to use Oracle-specific SQL statements to insert an empty blob (empty_blob()) and get it back as a return value. The return value can then be written to using Oracle-specific casts. Also, simply updating a blob (without emptying it first) results in a blob that is corrupted (i.e. they don't truncate the blob if you update a large blob with a smaller blob, leaving what is in my opinion a corrupted blob). This is also 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. However, the Stored procedure language support in Oracle 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. Also, Oracle Packages are very convenient and cool.

DB2 UDB UWO

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."

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'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.

DB2's JDBC 2.x support is very weak as well. They don't have Updatable Scrollable ResultSet, support for the new SQL types (Array, Ref, Distinct, Java Object) or SQL type mapping.

DB2 uses OS users only. You can't create users in DB2 outside of the OS.

All that being said, there are friendly, smart IBM'ers that 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. It is really easy to create and configure databases. Almost as intuitive as MSSQL. Performance

Of all the databases I've worked with, MSSQL is the quickest. This is very likely due to a couple of factors:

I'm running on Windows 2000 Server.
I'm not a DBA in all these databases, and they are likely not tuned properly

That said, MSSQL's defaults have provided by far the best performance. IBM's DB2 comes in at a distant second, distantly followed by Oracle 8i. For Oracle 8i, the long pole seems to be updating/inserting Blobs from JDBC.

As of now, I have a new requirement to support Informix 9.21, and DB2 UDB iSeries. I've also run into several issues with Informix that I'm trying to iron out, and porting to DB2 iSeries has been fairly painless except for a problem with one trigger.

That's my $0.02. Hope everyone finds it helpful. As I stated before, I am not a zealot for any of the databases - I'm just a developer trying to use a tool. The opinions expressed above are solely mine, and not the opinions of my company.

Marc Received on Fri May 03 2002 - 10:27:32 CDT