Re: Db2, Oracle, SQL Server

Valentin Minzatu wrote:

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

> - DB2 partitioning mimics federated database architecture while Oracle's one
> is in a shared disk architecture

Now that sentence makes me shudder...

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?

Let me quote from the Oracle docs *sneaky* :-) http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/ap_standard_sql001.htm#sthref7415 "Oracle Compliance with SQL/PSM:2003

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.

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab