Re: Db2, Oracle, SQL Server

> I would like not to be the match, but to get answers.
I tried hard. And I waive counter questions. But in return I'll provide
details so we all learn instead.

> Just to add on the "Oracle's the best" item list:> - Data Guard/Streams support DDL, while DB2's corresponding feature does> only support DML

Correct for replication only. I'm told Data Guard is best compared with
HADR. HADR stubbornly applies all logs, including catalog changes.
Thus it covers all DDL. HADR comes in three flavors: Asynchronous,
near-synchronous and synchronous. If Informix is any judge most
customers will use near-synchronous.
In that case the primary server waits for the seondary to acknowledge
receipt of a log record. To loose a transaction the secondary and
primary have to die simultaneously.
Asynchronous means that the primary puts the log on the wire and trusts
in the network. Synchronous means the secondary confirms persistence of
the log.
Note that is HADR the primary does not require the secondary to be up.
It can declare the secondary dead via timeout and move on (the affected
transactions will experience a delay on commit during the timeout interval).
The seondary will catch up after it comes back online (e.g. after a
scheduled fixpack upgrade). No problem since the seocndary is otherwise
idle (minus doing whatever other duties it may serve - like development)
A nice side-effect of the way its design is that the log writes go
through the seocndarys bufferpool. So the secondary is pre-heated.

> - DB2 has nothing similar to Oracle's transportable tablespace feature
That is correct.

A federation is set of _independent_ entities. (Like the European Union,
or as Bavaria and Saxony may claim Germany :-). That is not the case
with DB2 + DPF. You may be confusing with SQL Server which uses
federation to achieve scale-out (via UNION ALL views).
If you look at Websphere II that is federation. The system has no
special rights on the various enitities. Paranoia up to the point that
authorization is not encapsulated by design (no definer's rights).

The lines of shrared disk vs shared nothing are fuzzy.
If my understanding is correct each RAC node owns a piece of the data.
(that's why the re-mastering happens when a node fails).
DB2 for DPF (hear me out , no yelling!) is the same on that level.
Now on RAC any node can read and write data under strict control of the
owning node which acts as a traffic cop.
The difference with DB2 + DPF is that while any node can ask the owning
node to read and write data on its behalf, the requesting node may
never, ever touch the data directly.
On the optimizer level DB2 does what we call "function shipping" that is
the plan is analyzed w.r.t. whether it can be parallelized across the
nodes or whether certain portions of the plan should be executed on a
specific node (or a group of nodes). Table Queues (TQ) serve as the
equivalent of a pipe where data moves between nodes.
The goal is to reduce x-node communication (any MPP system works that
way AFAIK, so: Teradata, Informix XPS). Ironically in a RAC system one
will try the same. Both data-pages flow as well as the permission chit-chat.
I read a paper on RAC optimizer/runtime a while ago but forgot most of
it. At the point it appeared that RAC pretty much expands SMP concepts
(?)... "straw scans", etc, etc.

On the DMS level the difference is the most pronounced.
In DB2 + DPF the tablespaces are split along the lines of the ownership.
That means if I want to add a node without being able to keep ownership
of existing data consistent te dat aneeds to be redistributed between
the tablespaces, this is the equivalent of "remastering" but, of course,
more expensive.
It is important to keep in mind that DPF is not a failover solution,
never meant to be.
If a DPF node fails over it is restarted (in case of software failure)
or moved to another physical node (in case of software failure). So
there is no remastering at all in that case, the number of nodes does
not change unless the DBA wants it so. This is where many of the
misconceptions are rooted. "Shared nothing" is a logical statement.
Nodes can move and all boxes can access all the data. It is logical
ownership that is strictly divided up.
In a well designed application failure of a DPF node often only impacts
a subset of the system. The rest of DB2 keeps going for all transactions
that don't need the failed nodes data.

Now, my personal opinion, is that in the long run the truth will me
somewhere in the middle. E.g. one could dream up a system where
data is still stricly separated, yet not split into different files.
That would be a chimara of DPF and RAC.
(hopefully not the kind being all rear and no heads ;-)
In that system "remastering" would take place on scale out, instead of
"redistribution", yet the system would technically still qualify as
shared nothing.

> Questions:> - Is DB2's SQL PL any close to PL/SQL in terms of flexibility, performance,> and so on?

Oracle's PL/SQL provides functionality equivalent to SQL/PSM:2003, with
minor syntactic differences, such as the spelling or arrangement of
keywords."
SQL PL is a fair sized subset of SQL/PSM (it's missing the "module part".
I haven't quite wrapped my brain around Oracle packages. They seem to
overlap a lot with schemas (?).
Outside of SQL/PSM, but related to procedural programming:
DB2 supports distinct types ("typedef") instead of %TYPE.
What DB2 does not (yet) have is a match for %ROWTYPE, passing cursors,
passing around tables aqnd global variables.
But then again good programmers don't use global variables, right? ;-)

> - Does DB2 provide with any functionality similar to Oracle's external> tables?

Capability I think (?) yes for reads. DB2 supports external table UDF.
There is no CREATE TABLE language hooking directly into the load
facility. I don't know what Oracle's write capabilities are.
> - Does DB2 provide out-of-the-box ETL type of functionality such as Oracle's> combination of Streams, SQL*Loader, external tablespaces, etc?
I can't comment since I don't know the features. DB2 supports LOAD (low
level, high speed), IMPORT (using INSERT statements). I think there also
is a high performance unloader. Not my cup of tea...

> - Is ther any ASM-like feature in DB2?
ASM as in automatic storage manager added in O10g I believe? As of Feb
2005, no.