Re: Db2, Oracle, SQL Server

I still cannot recollect which IBM document I've seen where it was stated that DB2's HA feature does not replicate DDLs or some of them, but I will get back once I find it. Consider this one closed for now.

Per my understanding: DB2 partitions the data at the cluster level (must you have a cluster, parallel server?), therefore each node has its own data and when the query is submitted, one node acts as a dispatcher while the others execute it and then the results are merged in memory. Oracle allows for this as well, but it does not impose it as a limit. One could easily execute the same query in a serial manner (i.e. one single server process retrieves all the data querying each partition). The advantage is that if one of the nodes go down you do not need to repartition the table or move data around at all. It is already available as it is shared by all nodes. - please correct me if i am wrong - I would also like to know if DB2 has the ability to exchange partitions without taking the table/partition offline.

I couldn't find the Oracle's document you refered to (maybe they hid it ??). - i have just removed the rest of this paragraph as they could have caused some flames :)

ASM is there since 2004 - when they launched 10g, and one of the biggies about it is that one can transport tablespaces from one DB to another independent of the O/S (neat, huh?).

"Serge Rielau" <srielau_at_ca.ibm.com> wrote in message news:36nqruF4vd94lU1_at_individual.net...
>
> 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
Received on Thu Feb 10 2005 - 10:04:26 CST