Comparison of DB2 and Oracle?

[Rhino]

One of my friends, Scott, is a consultant who doesn't currently have
newsgroup access so I am asking these questions for him. I'll be telling him
how to monitor the answers via Google Newsgroup searches.

Scott has heard a lot of hype about DB2 and Oracle and is trying to
understand the pros and cons of each product. I'm quite familiar with DB2
but have never used Oracle so I can't make any meaningful comparisons for
him. He does not have a lot of database background but sometimes has to
choose or recommend a database to his clients.

Scott has enough life-experience to take the marketing information produced
by IBM and Oracle with a grain of salt and would like to hear from real
DBAs, especially ones who are fluent with both products, for their views on
two questions:

1. What are the pros and cons of the current releases of DB2 and Oracle?

2. What other sources of *independent* information are available to help
someone new to databases choose between DB2 and Oracle?

This is *not* a troll and we don't want to start a flame war! Scott just
want some honest facts to help him decide which product is best at which
jobs.

--
Rhino

[Jim Kennedy]

"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:2t******** *****@uni-berlin.de...

OK, here is the deal:
When you execute a dynamic statement which depends on table T.
DB2 will hold a usage lock on T until the end of the transaction.
So noone will be able to alter T (in a non-trivial way) until this
transaction is over.
An alternate design would be to release the lock after usage.
However in the interest of keeping the cache fast the capturing of locks
needs to be minimized. Schema evolution is considered a much rarer (more
rare??) event than cache-hits

Does that answer the comment?

Cheers
Serge

Here was the behavior that was observed. (on db2 on a mainframe)
1. Issue commit;
2. Issue a select statement (like select ... from mytable where ...)
3. People try to bind their programs and no dice.
4. Minutes pass and programmers start calling because they can't get their
work done.
5. Issue a commit (or rollback).
6. People can now bind their programs.

The explanation according to the manual was that DB2 doesn't do dynamic SQL,
it takes dynamic SQL and turns it into static SQL and then binds that static
SQL and runs it. Since a commit happens minutes later access to the plan
table is serialized. OUCH!

We observed this behavior and so had to turn the system into an autocommit
system just so we didn't turn a multi million dollar machine into the
equivalent of an based 8080 PC.

This went on for at least a couple of years and then I went onto other
companies.
Jim

[Serge Rielau]

Jim,

I can't comment on DB2 for zOS. Would be interested to know whether this
behaviour is still in existence and whether it was condidered working as
designed or a bug (e.g. a bad lock).
The behaviour you describe seems to indicate that users also wouldn't be
able to bind static apps concurrently... ..

Cheers
Serge

[Noons]

Serge Rielau <sr*****@ca.ibm .com> wrote in message news:<2t******* ******@uni-berlin.de>...

Now that R correlates, for all major RDBMS that I know, quite well with
SQL as it's access language. Do you know of other languages commonly
used in an RDBMS? Yes, there could be, but there aren't.
Yes I do, and yes there are. Quel from Ingres is one of them.
They still make it available, last time I looked. Sure it's not
much used and anyone using anything other than SQL must have rocks
on their head or doing research. That's not the point, though.
The point is that relational != SQL. Period. A DML is not a
data storage theory.
There is a lot of bloat going on in the market (and I'm not excluding
any vendor)
and that's where the open source products come in...
Absolutely. But let's bot in the name of marketing subvert
theory, OK?
Not everyone needs a Winnebago. Some folks just want to commute to
work... Mind you that doesn't make Winnebagos bad

They better all have at least four wheels...

[Serge Rielau]

Noons wrote:

Serge Rielau <sr*****@ca.ibm .com> wrote in message news:<2t******* ******@uni-berlin.de>...

Now that R correlates, for all major RDBMS that I know, quite well with
SQL as it's access language. Do you know of other languages commonly
used in an RDBMS? Yes, there could be, but there aren't.

Yes I do, and yes there are. Quel from Ingres is one of them.
They still make it available, last time I looked. Sure it's not
much used and anyone using anything other than SQL must have rocks
on their head or doing research. That's not the point, though.
The point is that relational != SQL. Period. A DML is not a
data storage theory.

... and that's where many, many customer's installations ail.
They believe by storing their data in tables and having some RI they are
using an RDBMS.
All they have done is found persistent storage for their data which then
is "processed" using nested cursors and procedural languages.
It's the curse of providing PL/SQL, SQL PL, SPL, TSQL....
The _center piece_ of RDBMS: "relational alegbra" ends up as roadkill in
the ditch. 30 years of research and all there is to show for it is that
data is stored in tables.

I should be fine with it.. it does sell hardware.

Cheers
Serge

[DA Morgan]

Comments in-line.

michael newport wrote:

Daniel,

in Ingres I wrote 4GL, in Oracle I write PL/SQL
Because you don't know how to write Java in the database?
in Ingres I wrote SQL, in Oracle I write SQL
Because you don't know how to write Java in the database?
in Ingres I ran an overnight batch from a Unix cron job, in Oracle I
schedule a dbms_job
Or dbms_scheduler
Or UNIX cron job
Or AppWorx
Or any one of a large number of other possible solutions.
in Ingres my results went to a database table, in Oracle my results go
to a database table
I'm impressed.
in Ingres I wrote a user parameterized report, in Oracle I write a
user parameterized report
Similarly impressed.
in Ingres I ran the report with a system call, in Oracle I use Oracle
Reports server (with all its nasty bugs)
Then you made a horrible choice of reporting software.
Ingres is free, Oracle is not

did I miss something ?

Regards
Michael Newport

What did you miss?

1. Security model
2. Scalability
3. Performance
4. Shared Everything Architecture
5. RAC
6. DataGuard
7. RMAN
8. User defined indexes
9. User defined operators
10. User defined locking
11. Domain indexes
12. Reverse-key indexes
13. Compressed indexes
14. Function based indexes
15. Sequences
16. User defined data types
17. Partitioning and Subpartitioning
18. Global Temporary Tables
19. External Tables
20. Index Organized Tables
21. Enterprise level support 7x24x365
22. Books at Amazon.com
(Oracle 27,707 hits, DB2 1,955 hits, Ingres 0 hits if refering to
your product)
23. Jobs at Dice.com
(Oracle 8,097 jobs, DB2 1,779 jobs, Ingres 18 jobs)
24. Jobs at Monster.com
25. Jobs at Hotjobs.com
26. Packages
27. Native compilation into C of PL/SQL
28. TAF (transparent application failover)
29. A prayer the product will still exist in 10 years.

I think I've made my point.

If you want a more valid comparison ... compare Ingres to MySQL,
PostgreSQL, Firebird, 3x5 cards. The difference will still be
more books, more jobs, and more chance it will survive 5 years.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace 'x' with 'u' to respond)

[Jim Kennedy]

"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:2t******** *****@uni-berlin.de...

Jim,

I can't comment on DB2 for zOS. Would be interested to know whether this
behaviour is still in existence and whether it was condidered working as
designed or a bug (e.g. a bad lock).
The behaviour you describe seems to indicate that users also wouldn't be
able to bind static apps concurrently... ..

Cheers
Serge

I can't tell you since I don't use DB2 anymore and we don't have a mainframe
where I work. I would expect that DB2 would work the same regardless of OS.
(not counting OS limitations ) IBM didn't consider it a bug. I was doing
work for American Transtech at the time (a subsidiary of ATT, certainly a
large enough customer to get attention). People couldn't bind apps
concurrently, but since the machine was fast enough people wouldn't notice.
Jim

[HansF]

DA Morgan wrote:

in Ingres I ran the report with a system call, in Oracle I use Oracle
Reports server (with all its nasty bugs)

Then you made a horrible choice of reporting software.

You're right. Often overlooked is Oracle's free SQLPlus & iSQLPlus which,
with it's formatting capability will satisfy, a significant percentage of
all reports. Especially when coupled with the new raw SQL grouping
functions available such as period-over-period comparisons, etc. (No need
even to go OLAP)

Sometimes the simplest solutions are best, no matter how much people sneer
at them! (I already hear the howls: but , but ... <G>)

/Hans

[DA Morgan]

HansF wrote:

DA Morgan wrote:

in Ingres I ran the report with a system call, in Oracle I use Oracle
Reports server (with all its nasty bugs)

Then you made a horrible choice of reporting software.

You're right. Often overlooked is Oracle's free SQLPlus & iSQLPlus which,
with it's formatting capability will satisfy, a significant percentage of
all reports. Especially when coupled with the new raw SQL grouping
functions available such as period-over-period comparisons, etc. (No need
even to go OLAP)

Sometimes the simplest solutions are best, no matter how much people sneer
at them! (I already hear the howls: but , but ... <G>)

/Hans

Or Discoverer
Or Crystal
Or Cognos
Or Brio
Or Business Objects
Or ....

--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace 'x' with 'u' to respond)

[michael newport]

correction you thought that you had made your point...

You can also use JAVA on Ingres.

1. Security model – same
2. Scalability - same
3. Performance - same

4. Shared Everything Architecture - equivalent
5. RAC - equivalent
6. DataGuard - equivalent
7. RMAN - equivalent
28. TAF (transparent application failover) - equivalent

8. User defined indexes - same
9. User defined operators - same
10. User defined locking - nice but never needed
11. Domain indexes - nice but never needed
12. Reverse-key indexes - same
13. Compressed indexes - same
14. Function based indexes - nice but never needed
15. Sequences - same
16. User defined data types - same
17. Partitioning and Subpartitioning - same
18. Global Temporary Tables - same
19. External Tables - same
20. Index Organized Tables - same
21. Enterprise level support 7x24x365 - same

22. Books at Amazon.com
(Oracle 27,707 hits, DB2 1,955 hits, Ingres 0 hits if refering to
your product)
23. Jobs at Dice.com
(Oracle 8,097 jobs, DB2 1,779 jobs, Ingres 18 jobs)
24. Jobs at Monster.com
25. Jobs at Hotjobs.com

I agree that Oracle wins on the job front but that will change.
Would you rather keep your job and use Ingres ? or
keep Oracle and have your job outsourced to India ?

26. Packages - like programs ?
27. Native compilation into C of PL/SQL – never needed this

29. A prayer the product will still exist in 10 years. –
now that Ingres is Open Source it will still be here, why,

because its free

for the few things that Ingres does not have, Oracle is not worth the money ??

I know I have made my point !

Regards
Michael Newport

[Philip Sherman]

Jim's comment about zOS behavior is what I also remember from my
mainframe days. My recollection is that this was (is?) a characteristic
of mainframe QMF because QMF interacts directly with the user and the
SQL statements don't complete until the user closes the session. We even
had a case where a user ran a query then went to lunch without
terminating it and held locks for over an hour!

The solution I implemented was to switch to using DSNTEP2 (a freebie
supplied with DB2) as a replacement for QMF for simple queries. Heavy
duty reporting was moved to "canned" QMF procedures where knowledgeable
programmers could optimize performance and concurrency.

Phil Sherman

Serge Rielau wrote:

Jim,

I can't comment on DB2 for zOS. Would be interested to know whether this
behaviour is still in existence and whether it was condidered working as
designed or a bug (e.g. a bad lock).
The behaviour you describe seems to indicate that users also wouldn't be
able to bind static apps concurrently... ..

Cheers
Serge