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

The Nomad wrote:

> > 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:
>
> a) Provide an isolation between our code, and database-specific stuff.
> b) 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:
>
> a) I'm running on Windows 2000 Server.
> b) 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

Your review seems pretty accurate though I do not know the other products, in the versions you used, well enough to comment on what you wrote. But your information on Oracle makes me wonder about its source:

1. With respect to modifying a table with a trigger on that table: Why didn't you just put PRAGMA AUTONOMOUS_TRANSACTION between the trigger declaration and the word BEGIN.
2. Result sets can be passed into and out of Oracle procedures with REF CURSORS, PL/SQL tables, and arrays. Why did would think there was something you could return from a function that you could not return from a procedure?
3. What tool were you using to create schemas where you couldn't see compilation errors? You don't say. I don't know anyone that uses a GUI tool for schema creation that doesn't work better than what you describe.

Daniel Morgan Received on Fri May 03 2002 - 10:38:28 CDT