469,282 Members | 1,909 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

tough choices

Hello:
We are designing two multi-user client server applications that
performs large number of transactions on database servers. On an
average Application A has a 50% mix of select and update/insert/delete
statements and application B has 80-20 mix of select and
update/insert/delete statements. Being able to scale the databases as
needed so the performance is unaffected, is one of our critical
requirements. We've been investigating Oracle 10g RAC and DB2 ESE as
alternatives and in both cases unfortunately, we get a lot more
marketing spin than real answers. I've looked through some of the
newsgroup postings on oracle and ibm's websites and most of the
discussions seem to be about high availability(and technology
evangelism). The information we've gathered so far seems to point to:

1. The critical factor (and possibly the bottleneck) for Oracle's RAC
performance is the network and the storage access speed- if the
network does not have ample unused bandwidth or the rate at which
storage can be accessed by various nodes has reached the point of
diminishing returns - we won't get any additional performance by
simply increasing the number of nodes. Also, the application that
performs more writes will hugely increase the network traffic because
of synchronization requirements.

2. DB2 can deliver better performance but only if the data that is
accessed together is physically laid out together and the application
has knowledge of the physical data layout (so it can connect to the
right node in the cluster ). However, if, we separate the application
logic from physical layout of the data the performance will be
unpredictable.

All this is just hypotheses - if anyone has some real world experience
with these two offerings and can offer an objective opinion - we'd
really appreciate it.
Nov 12 '05 #1
198 10044
Sy Borg wrote:
Hello:
We are designing two multi-user client server applications that
performs large number of transactions on database servers. On an
average Application A has a 50% mix of select and update/insert/delete
statements and application B has 80-20 mix of select and
update/insert/delete statements. Being able to scale the databases as
needed so the performance is unaffected, is one of our critical
requirements. We've been investigating Oracle 10g RAC and DB2 ESE as
alternatives and in both cases unfortunately, we get a lot more
marketing spin than real answers. I've looked through some of the
newsgroup postings on oracle and ibm's websites and most of the
discussions seem to be about high availability(and technology
evangelism). The information we've gathered so far seems to point to:

1. The critical factor (and possibly the bottleneck) for Oracle's RAC
performance is the network and the storage access speed- if the
network does not have ample unused bandwidth or the rate at which
storage can be accessed by various nodes has reached the point of
diminishing returns - we won't get any additional performance by
simply increasing the number of nodes. Also, the application that
performs more writes will hugely increase the network traffic because
of synchronization requirements.

2. DB2 can deliver better performance but only if the data that is
accessed together is physically laid out together and the application
has knowledge of the physical data layout (so it can connect to the
right node in the cluster ). However, if, we separate the application
logic from physical layout of the data the performance will be
unpredictable.

All this is just hypotheses - if anyone has some real world experience
with these two offerings and can offer an objective opinion - we'd
really appreciate it.


Do you already have a hardware/os platform picked out, because the
underlying technology in a high-transaction rate application is just as
important as the database engine you choose. Since "high-transaction"
rate is quite subjective, can you quantify that point a bit more.

With RAC, (and depending on your platform) physical access to the data
from ALL nodes in the cluster - concurrently - would be IMHO imperative.
There are "cluster" technologies out there that do not allow
concurrent access to all file systems from multiple nodes and you need
to research them carefully.

But if you want to use the "original" RAC, use Oracle Rdb (not to be
confused with RDBMS - formerly DEC Rdb) on an OpenVMS cluster. A
cluster that can be ~256 nodes and using SAN storage, multiple-PB of
storage. And the application can run concurrently on all nodes in the
cluster. BTW, A large number of stock exchanges rely on OpenVMS and Rdb
on the trading floor... draw your own conclusions.

Michael Austin.
Nov 12 '05 #2
"Sy Borg" <bo*********@yahoo.ca> wrote in message
news:b2*************************@posting.google.co m...
Hello:

2. DB2 can deliver better performance but only if the data that is
accessed together is physically laid out together and the application
has knowledge of the physical data layout (so it can connect to the
right node in the cluster ). However, if, we separate the application
logic from physical layout of the data the performance will be
unpredictable.

I am not sure what you mean by this (number 2), but I don't think it is
correct. But before passing final judgment, I would like hear a better
explanation of what you mean.

Also, either database will probably handle the majority of applications
equally well from a performance standpoint. The exception might be if you
had some unusually high transaction or query volumes or unusually large
tables (neither of which was specified in enough detail to pass judgment
on)..

Nov 12 '05 #3
Your point about DB2 is true in some but not all cases. It certainly
implies you're using DB2 with DPF (Distributed Partitioning Feature -
called EEE before v8).

Here is an article on speeding up inserts in a partitioned environment:

http://www-106.ibm.com/developerwork...04pooloth.html
Sy Borg wrote:
Hello:
We are designing two multi-user client server applications that
performs large number of transactions on database servers. On an
average Application A has a 50% mix of select and update/insert/delete
statements and application B has 80-20 mix of select and
update/insert/delete statements. Being able to scale the databases as
needed so the performance is unaffected, is one of our critical
requirements. We've been investigating Oracle 10g RAC and DB2 ESE as
alternatives and in both cases unfortunately, we get a lot more
marketing spin than real answers. I've looked through some of the
newsgroup postings on oracle and ibm's websites and most of the
discussions seem to be about high availability(and technology
evangelism). The information we've gathered so far seems to point to:

1. The critical factor (and possibly the bottleneck) for Oracle's RAC
performance is the network and the storage access speed- if the
network does not have ample unused bandwidth or the rate at which
storage can be accessed by various nodes has reached the point of
diminishing returns - we won't get any additional performance by
simply increasing the number of nodes. Also, the application that
performs more writes will hugely increase the network traffic because
of synchronization requirements.

2. DB2 can deliver better performance but only if the data that is
accessed together is physically laid out together and the application
has knowledge of the physical data layout (so it can connect to the
right node in the cluster ). However, if, we separate the application
logic from physical layout of the data the performance will be
unpredictable.

All this is just hypotheses - if anyone has some real world experience
with these two offerings and can offer an objective opinion - we'd
really appreciate it.


Nov 12 '05 #4
Sy Borg wrote:
Hello:
We are designing two multi-user client server applications that
performs large number of transactions on database servers. On an
average Application A has a 50% mix of select and update/insert/delete
statements and application B has 80-20 mix of select and
update/insert/delete statements. Being able to scale the databases as
needed so the performance is unaffected, is one of our critical
requirements. We've been investigating Oracle 10g RAC and DB2 ESE as
alternatives and in both cases unfortunately, we get a lot more
marketing spin than real answers. I've looked through some of the
newsgroup postings on oracle and ibm's websites and most of the
discussions seem to be about high availability(and technology
evangelism). The information we've gathered so far seems to point to:

1. The critical factor (and possibly the bottleneck) for Oracle's RAC
performance is the network and the storage access speed- if the
network does not have ample unused bandwidth or the rate at which
storage can be accessed by various nodes has reached the point of
diminishing returns - we won't get any additional performance by
simply increasing the number of nodes. Also, the application that
performs more writes will hugely increase the network traffic because
of synchronization requirements.

2. DB2 can deliver better performance but only if the data that is
accessed together is physically laid out together and the application
has knowledge of the physical data layout (so it can connect to the
right node in the cluster ). However, if, we separate the application
logic from physical layout of the data the performance will be
unpredictable.

All this is just hypotheses - if anyone has some real world experience
with these two offerings and can offer an objective opinion - we'd
really appreciate it.


The "bottleneck" you have identified is only a problem if you don't
obtain the proper hardware. The number of transactions, and volume,
going through an HBA to a storage device is not related to RAC versus
federated data. Buy the right hardware and there is no issue.

The main consideration I would think would be the overhead of federating
data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #5
Mark A wrote:
Sy Borg wrote:

2. DB2 can deliver better performance but only if the data that is
accessed together is physically laid out together and the application
has knowledge of the physical data layout (so it can connect to the
right node in the cluster ). However, if, we separate the application
logic from physical layout of the data the performance will be
unpredictable.

All this is just hypotheses - if anyone has some real world experience
with these two offerings and can offer an objective opinion - we'd
really appreciate it.


The "bottleneck" you have identified is only a problem if you don't
obtain the proper hardware. The number of transactions, and volume,
going through an HBA to a storage device is not related to RAC versus
federated data. Buy the right hardware and there is no issue.

The main consideration I would think would be the overhead of federating
data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.

--
Daniel Morgan

Where does he say that federated support will be used (or is needed) for
DB2?


He didn't. Another response to him did.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #6
Daniel Morgan <da******@x.washington.edu> wrote in message news:<1087421232.498660@yasure>...

The "bottleneck" you have identified is only a problem if you don't
obtain the proper hardware. The number of transactions, and volume,
going through an HBA to a storage device is not related to RAC versus
federated data. Buy the right hardware and there is no issue.
Thank you Daniel. Please pardon my ignorance - my background is
software development. I have one further question - does this mean
that we need to recommend an upfront investment in a super fast
network and storage hardware to our customers? In other words, when
the oracle guys make the scalability claim for their database
clusters, are they saying that - sure you can scale your system up and
add many more processor nodes - but make sure you spend the big bucks
on the network and storage right now because if you don't and add more
nodes in the future - your network and storage hardware might not be
able to handle the volume.

The main consideration I would think would be the overhead of federating
data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.

Nov 12 '05 #7
Comments in-line.

Sy Borg wrote:
Daniel Morgan <da******@x.washington.edu> wrote in message news:<1087421232.498660@yasure>...

The "bottleneck" you have identified is only a problem if you don't
obtain the proper hardware. The number of transactions, and volume,
going through an HBA to a storage device is not related to RAC versus
federated data. Buy the right hardware and there is no issue.

Thank you Daniel. Please pardon my ignorance - my background is
software development. I have one further question - does this mean
that we need to recommend an upfront investment in a super fast
network and storage hardware to our customers? In other words, when
the oracle guys make the scalability claim for their database
clusters, are they saying that - sure you can scale your system up and
add many more processor nodes - but make sure you spend the big bucks
on the network and storage right now because if you don't and add more
nodes in the future - your network and storage hardware might not be
able to handle the volume.


My comment is generic and has nothing to do with Oracle or any other
specific product. Every system has a breaking point based on whatever
resource is going to be most challenged. It might be CPU. It might be
the bus. It might be the storage array. Without benchmarking and load
testing your system you are just making expensive guesses and they could
be wrong.

What you need to do is:
1. Write specs
2. Choose your development environment
3. Size your hardware with help from the vendor
4. Write a good contract with the vendor putting the sizing burden
of all aspects of the hardware on them

So in answer to your question ... scaling on any database system
requires that your hardware, all of it, support the scaling. And
scaling RAC is not significantly different from scaling SMP. More
CPUs, more RAM, more bytes moved, more storage, more HBAs, etc.

No database can violate the rule.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #8

"Sy Borg" <bo*********@yahoo.ca> wrote in message
news:b2*************************@posting.google.co m...
Daniel Morgan <da******@x.washington.edu> wrote in message news:<1087421232.498660@yasure>...

The "bottleneck" you have identified is only a problem if you don't
obtain the proper hardware. The number of transactions, and volume,
going through an HBA to a storage device is not related to RAC versus
federated data. Buy the right hardware and there is no issue.


Thank you Daniel. Please pardon my ignorance - my background is
software development. I have one further question - does this mean
that we need to recommend an upfront investment in a super fast
network and storage hardware to our customers? In other words, when
the oracle guys make the scalability claim for their database
clusters, are they saying that - sure you can scale your system up and
add many more processor nodes - but make sure you spend the big bucks
on the network and storage right now because if you don't and add more
nodes in the future - your network and storage hardware might not be
able to handle the volume.


PMFJI

No, they're not saying that. But it would be foolish to pretend you keep on
bolting on extra boxes ad infinitum and the thing just keeps on scaling
linearly. There comes a point where you do have to consider that multiple
nodes and multiple instances are all sharing the one disk storage array, and
are all sending messages across the one internconnect/networking
infrastructure... and hence, contention issues will eventually arise. At
*that* point, new interconnects and new storage solutions may need to be
considered.

The marketing guys are probably over-selling scalability. That's what
marketing guys are paid for. But they're not just making it all up either.
As ever, it's 'take every grain of truth with a pinch of salt'.

Regards
HJR
Nov 12 '05 #9
Thanks Blair. We are indeed looking at DB2 - DPF. I'm not a database
expert and I believe our initially transaction load guestimates for
production environment are around 400-500 tpc going against mostly
10-12 fairly large tables (~1 GB each) and 10 -15 smaller tables(but
we expect both table and transactions to grow as the product matures).
Our apps are written in plain old C++ on windows.

My questions are probably very naive - I am a software developer- and
I think in terms of interfaces and not implementations. What I was
trying to figure out was if we could write my application completely
independent of the physical layout of a DB2 DPF database (just
treating the database as if it were a single node) and then based on
individual customer requirements, retain the same throughput by
increasing/decreasing nodes based on the load. (Some of the stuff in
the marketing brochures would certianly make you believe that). What I
have gathered from all the sources for DB2 so far suggests that, for
our apps that do perform writes to frequently accessed data on large
tables, to take advantage of the DB2 DPF scaling features, we would
probably need to carefully design of the application logic with the
db2 DPF in mind. Also, adding new nodes would also add some complexity
to the DBA's work (re-partitioning data properly, additional
maintaince tasks etc..)
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...
Your point about DB2 is true in some but not all cases. It certainly
implies you're using DB2 with DPF (Distributed Partitioning Feature -
called EEE before v8).

Here is an article on speeding up inserts in a partitioned environment:

http://www-106.ibm.com/developerwork...04pooloth.html

Nov 12 '05 #10
Thanks Blair. We are indeed looking at DB2 - DPF. I'm not a database
expert and I believe our initially transaction load guestimates for
production environment are around 400-500 tpc going against mostly
10-12 fairly large tables (~1 GB each) and 10 -15 smaller tables(but
we expect both table and transactions to grow as the product matures).
Our apps are written in plain old C++ on windows.

My questions are probably very naive - I am a software developer- and
I think in terms of interfaces and not implementations. What I was
trying to figure out was if we could write my application completely
independent of the physical layout of a DB2 DPF database (just
treating the database as if it were a single node) and then based on
individual customer requirements, retain the same throughput by
increasing/decreasing nodes based on the load. (Some of the stuff in
the marketing brochures would certianly make you believe that). What I
have gathered from all the sources for DB2 so far suggests that, for
our apps that do perform writes to frequently accessed data on large
tables, to take advantage of the DB2 DPF scaling features, we would
probably need to carefully design of the application logic with the
db2 DPF in mind. Also, adding new nodes would also add some complexity
to the DBA's work (re-partitioning data properly, additional
maintaince tasks etc..)
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...
Your point about DB2 is true in some but not all cases. It certainly
implies you're using DB2 with DPF (Distributed Partitioning Feature -
called EEE before v8).

Here is an article on speeding up inserts in a partitioned environment:

http://www-106.ibm.com/developerwork...04pooloth.html

Nov 12 '05 #11
answered offline

Sy Borg wrote:
Thanks Blair. We are indeed looking at DB2 - DPF. I'm not a database
expert and I believe our initially transaction load guestimates for
production environment are around 400-500 tpc going against mostly
10-12 fairly large tables (~1 GB each) and 10 -15 smaller tables(but
we expect both table and transactions to grow as the product matures).
Our apps are written in plain old C++ on windows.

My questions are probably very naive - I am a software developer- and
I think in terms of interfaces and not implementations. What I was
trying to figure out was if we could write my application completely
independent of the physical layout of a DB2 DPF database (just
treating the database as if it were a single node) and then based on
individual customer requirements, retain the same throughput by
increasing/decreasing nodes based on the load. (Some of the stuff in
the marketing brochures would certianly make you believe that). What I
have gathered from all the sources for DB2 so far suggests that, for
our apps that do perform writes to frequently accessed data on large
tables, to take advantage of the DB2 DPF scaling features, we would
probably need to carefully design of the application logic with the
db2 DPF in mind. Also, adding new nodes would also add some complexity
to the DBA's work (re-partitioning data properly, additional
maintaince tasks etc..)
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...
Your point about DB2 is true in some but not all cases. It certainly
implies you're using DB2 with DPF (Distributed Partitioning Feature -
called EEE before v8).

Here is an article on speeding up inserts in a partitioned environment:

http://www-106.ibm.com/developerwork...04pooloth.html


Nov 12 '05 #12
Sy Borg wrote:
Daniel Morgan <da******@x.washington.edu> wrote in message news:<1087421232.498660@yasure>...

The "bottleneck" you have identified is only a problem if you don't
obtain the proper hardware. The number of transactions, and volume,
going through an HBA to a storage device is not related to RAC versus
federated data. Buy the right hardware and there is no issue.

Thank you Daniel. Please pardon my ignorance - my background is
software development. I have one further question - does this mean
that we need to recommend an upfront investment in a super fast
network and storage hardware to our customers? In other words, when
the oracle guys make the scalability claim for their database
clusters, are they saying that - sure you can scale your system up and
add many more processor nodes - but make sure you spend the big bucks
on the network and storage right now because if you don't and add more
nodes in the future - your network and storage hardware might not be
able to handle the volume.

The main consideration I would think would be the overhead of federating
data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.


and to put it another way... your scalability will only be as scalable
as your slowest technology (disk, controller, HBA, cpu, bus, memory,
network infrastructure, SAN infrastructure etc...)

We can only give you so much advice with the information provided, so
our suggestions will only go so far.

If you are talking hundreds of transactions an hour versus hundreds per
minute, the scale is vastly different and choice of hardware and
database can vary with each -- and this can vary with the size of each
transaction as well. You don't put in a single-processor Windows server
to achieve thousands per minute rate. (I wouldn't even consider that for
my guestbook with 10 hits a year, but that's just me :) )

As always, YMMV.

Michael Austin.
Nov 12 '05 #13
"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1087421232.498660@yasure...

The main consideration I would think would be the overhead of federating
data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.


I'd be impressed with a RAC 'scalability' solution that didn't have higher
downtime than an appropriately sized single node equivalent. More complexity
= less screwups is an equation with which I am unfamiliar :) The same of
course applies to IBM clustered solutions.

--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
Nov 12 '05 #14
"Mark A" <no****@switchboard.net> wrote in message
news:Za*****************@news.uswest.net...
Sy Borg wrote:

2. DB2 can deliver better performance but only if the data that is
accessed together is physically laid out together and the application
has knowledge of the physical data layout (so it can connect to the
right node in the cluster ). However, if, we separate the application
logic from physical layout of the data the performance will be
unpredictable.

All this is just hypotheses - if anyone has some real world experience
with these two offerings and can offer an objective opinion - we'd
really appreciate it.


The "bottleneck" you have identified is only a problem if you don't
obtain the proper hardware. The number of transactions, and volume,
going through an HBA to a storage device is not related to RAC versus
federated data. Buy the right hardware and there is no issue.

The main consideration I would think would be the overhead of federating
data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.

--
Daniel Morgan


Where does he say that federated support will be used (or is needed) for
DB2?


I got that from
"DB2 can deliver better performance but *only if* the data that is
accessed together is physically laid out together and the application
has knowledge of the physical data layout (so it can connect to the
right node in the cluster )."

I may of course be wrong.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
Nov 12 '05 #15
Niall Litchfield wrote:
"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1087421232.498660@yasure...
The main consideration I would think would be the overhead of federating
data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.

I'd be impressed with a RAC 'scalability' solution that didn't have higher
downtime than an appropriately sized single node equivalent. More complexity
= less screwups is an equation with which I am unfamiliar :) The same of
course applies to IBM clustered solutions.


Daniel,

If I understand the correct programming of a RAC application is to have
a connection to multiple nodes in the cluster simultaneously and if
there is a failure, the transaction continues unscathed on another node
in the cluster. I have seen this demonstrated to be true. So, what this
should mean is that even though you may have a node crash, your
application AND database AND transactions will survive with no
"downtime" experienced by the end user. The application and database is
available 100% or as near 100% as you can get...

According to Oracle marketing and technical folks (2 years ago), this
really only worked as advertised on 2 platforms. Can you guess which
ones they were??

You mentioned that you have a multi-node Linux cluster using a NAS-head
for disk access... Can you provide me a pointer to the details of the
complete configuration? I am not opposed to learning new configurations
and platforms. What do you see as it's weaknesses and strong-points.

Michael Austin.
Nov 12 '05 #16

"Michael Austin" <ma*****@firstdbasource.com> wrote in message
news:tH*****************@newssvr24.news.prodigy.co m...
Niall Litchfield wrote:
"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1087421232.498660@yasure...
The main consideration I would think would be the overhead of federating
data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.

I'd be impressed with a RAC 'scalability' solution that didn't have higher downtime than an appropriately sized single node equivalent. More complexity = less screwups is an equation with which I am unfamiliar :) The same of
course applies to IBM clustered solutions.


Daniel,

If I understand the correct programming of a RAC application is to have
a connection to multiple nodes in the cluster simultaneously and if
there is a failure, the transaction continues unscathed on another node
in the cluster. I have seen this demonstrated to be true.


PMFJI, but no you haven't. You've seen selects resume on another node. You
won't have seen a *transaction* resume on a surviving node, because that is
not possible. What you're talking about is something called Transparent
Application Failover or TAF. It works for selects only, and only for
applications that use OCI somewhere in their connectivity (ie, it doesn't
work for a thin Java app, for example). Incidentally, TAF does not require
you to connect to multiple nodes simultaneously. You can do so if you wish,
using the PRECONNECT connection method. But the default is not to, meaning
that if the node you are connected to dies, you have to inititate a fresh
connection to the RAC (that's the BASIC connection method).

Also incidentally, TAF goes a long way further back than RAC, and works for
example in a standby database, or Data Guard, configuration. It is not a
RAC-specific technology.
So, what this
should mean is that even though you may have a node crash, your
application AND database AND transactions will survive with no
"downtime" experienced by the end user. The application and database is
available 100% or as near 100% as you can get...
Well, see, that's just not true. A select statement, maybe, yes, sort of
(depends on how you define 100%, I think: there is a delay in resuming the
select statement, because it can't resume until LMON on surviving nodes has
performed instance recovery. The user would certainly 'experience' such a
delay). But for DML, nowhere close: the statement fails, is rolled back by
PMON, and has to be re-performed from scratch. That is definitely an
'experience' of downtime by a User.
According to Oracle marketing and technical folks (2 years ago), this
really only worked as advertised on 2 platforms. Can you guess which
ones they were??
You'll have to refresh my memory, but from my own personal experience, two
years ago, TAF was working as advertised on Windows and Solaris and Linux.
And Tru64, too.

Regards
HJR


You mentioned that you have a multi-node Linux cluster using a NAS-head
for disk access... Can you provide me a pointer to the details of the
complete configuration? I am not opposed to learning new configurations
and platforms. What do you see as it's weaknesses and strong-points.

Michael Austin.

Nov 12 '05 #17
> > > > 2. DB2 can deliver better performance but only if the data that is
> accessed together is physically laid out together and the application > has knowledge of the physical data layout (so it can connect to the
> right node in the cluster ). However, if, we separate the application > logic from physical layout of the data the performance will be
> unpredictable.
>
> All this is just hypotheses - if anyone has some real world experience > with these two offerings and can offer an objective opinion - we'd
> really appreciate it.

The "bottleneck" you have identified is only a problem if you don't
obtain the proper hardware. The number of transactions, and volume,
going through an HBA to a storage device is not related to RAC versus
federated data. Buy the right hardware and there is no issue.

The main consideration I would think would be the overhead of federating data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.

--
Daniel Morgan


Where does he say that federated support will be used (or is needed) for
DB2?


I got that from
"DB2 can deliver better performance but *only if* the data that is
accessed together is physically laid out together and the application
has knowledge of the physical data layout (so it can connect to the
right node in the cluster )."

I may of course be wrong.
Niall Litchfield
Oracle DBA


That does not sound like he needs Federated support to me. I think he
(original poster) was asking a question and not answering one. But I am not
sure what he was talking about.
Nov 12 '05 #18
Niall Litchfield wrote:
"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1087421232.498660@yasure...
The main consideration I would think would be the overhead of federating
data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.

I'd be impressed with a RAC 'scalability' solution that didn't have higher
downtime than an appropriately sized single node equivalent. More complexity
= less screwups is an equation with which I am unfamiliar :) The same of
course applies to IBM clustered solutions.


It is not the more complexity equals fewer screw-ups but rather the
implications are different.

If I have a single table in a tablespace in Oracle stored in a single
datafile the data is equally accessible from all nodes. Lose a node and
there is no negative effect on the ability to access any part of a the
data in that table. Federate the data as is required by shared nothing
architectures and the loss of a single node effectively kills the
system.

Thus with shared everything the more nodes the less likely a failure
whereas with shared nothing loss of a node makes part of the data
inaccessible.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #19
Michael Austin wrote:
Niall Litchfield wrote:
"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1087421232.498660@yasure...
The main consideration I would think would be the overhead of federating
data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.


I'd be impressed with a RAC 'scalability' solution that didn't have
higher
downtime than an appropriately sized single node equivalent. More
complexity
= less screwups is an equation with which I am unfamiliar :) The same of
course applies to IBM clustered solutions.


Daniel,

If I understand the correct programming of a RAC application is to have
a connection to multiple nodes in the cluster simultaneously and if
there is a failure, the transaction continues unscathed on another node
in the cluster. I have seen this demonstrated to be true. So, what this
should mean is that even though you may have a node crash, your
application AND database AND transactions will survive with no
"downtime" experienced by the end user. The application and database is
available 100% or as near 100% as you can get...

According to Oracle marketing and technical folks (2 years ago), this
really only worked as advertised on 2 platforms. Can you guess which
ones they were??

You mentioned that you have a multi-node Linux cluster using a NAS-head
for disk access... Can you provide me a pointer to the details of the
complete configuration? I am not opposed to learning new configurations
and platforms. What do you see as it's weaknesses and strong-points.

Michael Austin.


I have 8 HP DL360 dual proc servers running RedHat EL AS 3 update 2.
They each NFS mount a NetApp F810 Filer Head with an 8GB RAM cache
connected to a tray with 1.2TB of disk.

We use the Oracle TAF demo running on a workstation as the load and
shutdown nodes by either pulling the power cord or doing a SHUTDOWN
ABORT. Transactions seamlessly continue after less than one second.

The TAF demo can be downloaded from the PSOUG web site
(http://www.psoug.org) along with a zip file with our configuration
files for the demo.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #20
Daniel Morgan wrote:
If I have a single table in a tablespace in Oracle stored in a single
datafile the data is equally accessible from all nodes. Lose a node and
there is no negative effect on the ability to access any part of a the
data in that table. Federate the data as is required by shared nothing
architectures and the loss of a single node effectively kills the
system.

Thus with shared everything the more nodes the less likely a failure
whereas with shared nothing loss of a node makes part of the data
inaccessible.

Let's clarify the lingo:
DB2 II uses federation. One of the main properties of federation is that
any one piece of the federation is pretty independent from the rest.
They may not even know or admit they are part of it (a bit like Bavaria
or Quebec ;-)
DB2 UDB for LUW + DPF is a partitioned DBMS. The partitioning is done on
a logical level. Partitions assume logical (not physical) ownership of data.
If a partition goes down it can either simply be restarted, or, if there
is a problem with the hardware that hosts the partiton the partition can
be restarted on another hardware.
The data sits on the same physical storage subsystem as it does in a
shared disc.
There is no problem for a partition to access that data from any
physical node it happens to run on using the same technology Oracle
exploits

So the question really boils down to this:
1. The time it takes to detect a partition is down. That is a universal
problem independent of the underlying technology.
2. The time it takes to get the partition back into the game compared to
the failover work a RAC cluster has to do (such as remastering locks).

A curious fact (no more no less) is that there are numerous kinds of
applications where a set of clients only ever accesses one or a few
partitions of the overall system. In these cases an unrelated partition
can fail over completely without notice by those clients. An example may
be the operation of a retail store chain with a partitioning key on the
store id. While headquartes may not be able to get the big picture while
a partition fails over, most the individual stores can operate without a
hitch throughout the event.

It's my understanding that remastering locks by contrast has a global
impact. Does this happen twice, btw? First time when the node goes down.
Second time when it comes up?

While _adding_ a partition to increase capacity would usually be
followed by a redistribute which, today, has significant impact, the
fact that nodes are not physical has interesting implications.
E.g. one can oversize the number of partitions per physical node.
When more capacity is needed additional hardware can be made available
and the partitions themselves get redistributed amongst the new
infrastructure. Note that no data has to move since no partition was
added or removed.
Similarly one would never remove a partition during an HA event or to
lower resource consumption. One simply restarts the partition or clears
out a physical node.

In reality most DB2 + DPF customers run more than one partition per
physical node. So this is indeed a realistic option.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #21
Ian
Niall Litchfield wrote:
"Mark A" <no****@switchboard.net> wrote in message
news:Za*****************@news.uswest.net...

Where does he say that federated support will be used (or is needed) for
DB2?

I got that from
"DB2 can deliver better performance but *only if* the data that is
accessed together is physically laid out together and the application
has knowledge of the physical data layout (so it can connect to the
right node in the cluster )."

I may of course be wrong.


In this case, he is most likely referring to collocation when you're
using DPF, which will make a big difference in query performance.
This has nothing to do with federation.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #22
Howard J. Rogers wrote:
"Michael Austin" <ma*****@firstdbasource.com> wrote in message
news:tH*****************@newssvr24.news.prodigy.co m...
Niall Litchfield wrote:

"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1087421232.498660@yasure...
The main consideration I would think would be the overhead of federating
data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.
I'd be impressed with a RAC 'scalability' solution that didn't have
higher
downtime than an appropriately sized single node equivalent. More
complexity
= less screwups is an equation with which I am unfamiliar :) The same of
course applies to IBM clustered solutions.

Daniel,

If I understand the correct programming of a RAC application is to have
a connection to multiple nodes in the cluster simultaneously and if
there is a failure, the transaction continues unscathed on another node
in the cluster. I have seen this demonstrated to be true.

PMFJI, but no you haven't. You've seen selects resume on another node. You
won't have seen a *transaction* resume on a surviving node, because that is
not possible. What you're talking about is something called Transparent
Application Failover or TAF. It works for selects only, and only for
applications that use OCI somewhere in their connectivity (ie, it doesn't
work for a thin Java app, for example). Incidentally, TAF does not require
you to connect to multiple nodes simultaneously. You can do so if you wish,
using the PRECONNECT connection method. But the default is not to, meaning
that if the node you are connected to dies, you have to inititate a fresh
connection to the RAC (that's the BASIC connection method).

Also incidentally, TAF goes a long way further back than RAC, and works for
example in a standby database, or Data Guard, configuration. It is not a
RAC-specific technology.

So, what this
should mean is that even though you may have a node crash, your
application AND database AND transactions will survive with no
"downtime" experienced by the end user. The application and database is
available 100% or as near 100% as you can get...

Well, see, that's just not true. A select statement, maybe, yes, sort of
(depends on how you define 100%, I think: there is a delay in resuming the
select statement, because it can't resume until LMON on surviving nodes has
performed instance recovery. The user would certainly 'experience' such a
delay). But for DML, nowhere close: the statement fails, is rolled back by
PMON, and has to be re-performed from scratch. That is definitely an
'experience' of downtime by a User.

According to Oracle marketing and technical folks (2 years ago), this
really only worked as advertised on 2 platforms. Can you guess which
ones they were??

You'll have to refresh my memory, but from my own personal experience, two
years ago, TAF was working as advertised on Windows and Solaris and Linux.
And Tru64, too.


Tru64 and OpenVMS... the others had to do some gyrations with the
filesystems on Windows and Solaris after the original node crashed...
IIRC -- didn't take long, just longer than the reuqired instance
recovery on a surviving node. As I said this was 2 years ago and the
grey-matter gets clouded sometimes... :)

Michael.


Regards
HJR
You mentioned that you have a multi-node Linux cluster using a NAS-head
for disk access... Can you provide me a pointer to the details of the
complete configuration? I am not opposed to learning new configurations
and platforms. What do you see as it's weaknesses and strong-points.

Michael Austin.


Nov 12 '05 #23
Daniel Morgan wrote:
Michael Austin wrote:
Niall Litchfield wrote:
"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1087421232.498660@yasure...

The main consideration I would think would be the overhead of
federating
data for DB2. The more data the more difficult and time consuming and
the fact that losing nodes with RAC is an inconvience ... with DB2 you
have a lot more to worry about ... and mean time between failures goes
down, not up, as you add nodes.


I'd be impressed with a RAC 'scalability' solution that didn't have
higher
downtime than an appropriately sized single node equivalent. More
complexity
= less screwups is an equation with which I am unfamiliar :) The same of
course applies to IBM clustered solutions.


Daniel,

If I understand the correct programming of a RAC application is to
have a connection to multiple nodes in the cluster simultaneously and
if there is a failure, the transaction continues unscathed on another
node in the cluster. I have seen this demonstrated to be true. So,
what this should mean is that even though you may have a node crash,
your application AND database AND transactions will survive with no
"downtime" experienced by the end user. The application and database
is available 100% or as near 100% as you can get...

According to Oracle marketing and technical folks (2 years ago), this
really only worked as advertised on 2 platforms. Can you guess which
ones they were??

You mentioned that you have a multi-node Linux cluster using a
NAS-head for disk access... Can you provide me a pointer to the
details of the complete configuration? I am not opposed to learning
new configurations and platforms. What do you see as it's weaknesses
and strong-points.

Michael Austin.

I have 8 HP DL360 dual proc servers running RedHat EL AS 3 update 2.
They each NFS mount a NetApp F810 Filer Head with an 8GB RAM cache
connected to a tray with 1.2TB of disk.

We use the Oracle TAF demo running on a workstation as the load and
shutdown nodes by either pulling the power cord or doing a SHUTDOWN
ABORT. Transactions seamlessly continue after less than one second.


So which is correct? HJR says that "transactions" don't survice only
the select statements. Howard, isn't a select statement ALWAYS a part of
a transaction? Daniel, just curious, but have you tested the durability
and survivability of "transactions" and what were those finding?
Example: Bank transaction moving $$$ from one account to another... and
yanking the power cord in the middle etc...

Michael Austin.
Nov 12 '05 #24
thanks for the clarification.

--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
"Ian" <ia*****@mobileaudio.com> wrote in message
news:40**********@corp.newsgroups.com...
Niall Litchfield wrote:
"Mark A" <no****@switchboard.net> wrote in message
news:Za*****************@news.uswest.net...

Where does he say that federated support will be used (or is needed) for
DB2?

I got that from
"DB2 can deliver better performance but *only if* the data that is
accessed together is physically laid out together and the application
has knowledge of the physical data layout (so it can connect to the
right node in the cluster )."

I may of course be wrong.


In this case, he is most likely referring to collocation when you're
using DPF, which will make a big difference in query performance.
This has nothing to do with federation.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Nov 12 '05 #25
"Michael Austin" <ma*****@firstdbasource.com> wrote in message
news:LV****************@newssvr23.news.prodigy.com ...
So which is correct? HJR says that "transactions" don't survice only
the select statements. Howard, isn't a select statement ALWAYS a part of
a transaction? Daniel, just curious, but have you tested the durability
and survivability of "transactions" and what were those finding?
Example: Bank transaction moving $$$ from one account to another... and
yanking the power cord in the middle etc...


Insert/Update/Delete do not qualify for TAF. (in 9i anyway and I would
imagine 10g). Select does. Your example involves modifying data and would
not survive. A report on the balances probably would.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
Nov 12 '05 #26

"Michael Austin" <ma*****@firstdbasource.com> wrote in message
news:LV****************@newssvr23.news.prodigy.com ...
So which is correct? HJR says that "transactions" don't survice only
the select statements. Howard, isn't a select statement ALWAYS a part of
a transaction?
No. A transaction is initiated with an insert, update or delete and
terminated with a commit or a rollback.
Selects don't (ordinarily) take locks in Oracle. They are not transactional.

Some people talk about selects as though they were transactions. I don't.
And those people would be wrong, IMO.
Daniel, just curious, but have you tested the durability
and survivability of "transactions" and what were those finding?
Example: Bank transaction moving $$$ from one account to another... and
yanking the power cord in the middle etc...


There's no possibility for confusion here. If you are transferring money
(inserting to one account, deleting from another) and the power goes in the
middle, the entire transaction is lost, and does not fail over (the user
connection will fail over 'transparently', but the work that user was in the
middle of is lost. Permanently. It has to be re-input from scratch). TAF
only works with selects.

Regards
HJR
Nov 12 '05 #27

"Michael Austin" <ma*****@firstdbasource.com> wrote in message
news:gO*****************@newssvr24.news.prodigy.co m...
According to Oracle marketing and technical folks (2 years ago), this
really only worked as advertised on 2 platforms. Can you guess which
ones they were??

You'll have to refresh my memory, but from my own personal experience, two years ago, TAF was working as advertised on Windows and Solaris and Linux. And Tru64, too.


Tru64 and OpenVMS... the others had to do some gyrations with the
filesystems on Windows and Solaris after the original node crashed...
IIRC -- didn't take long, just longer than the reuqired instance
recovery on a surviving node. As I said this was 2 years ago and the
grey-matter gets clouded sometimes... :)


Fair enough. But let's be clear: regardless of your operating system or file
system, resources in use at the time of a node/instance failure are always
locked until instance recovery has completed on other nodes (performed
automatically by LMON, incidentally). So, no matter your operating system,
or the file system, the resumption of a select statement will always "take
longer than the instance recovery" if what is being selected is not 100%
clean (ie, if it has also been subjected to DML, thus necessitating
recovery). A clean buffer needs no recovery, and so the block on disk it
represents can be made available to surviving nodes more quickly than a
dirty one (remastering may still be needed, even so).

There are no more "gyrations" involved with a file system on Windows and
Solaris than there is with any file system-based RAC installation on any
other O/S.

The only thing I can think of that Oracle "marketing and technical folks"
might have been saying 2 years ago was that you had to RAC with raw devices
unless you had Tru64 or VMS, because no cluster file systems were widely
available for other platforms. One of Tru64's great claims to fame at that
time was. indeed, that it was the only Unix for which a cluster file system
was available. That rapidly became "no longer the case". And, of course,
Oracle itself wrote a cluster file system for Windows and Linux.

TAF, nevertheless, has nothing whatsoever to do with the way the database is
physically stored, and therefore the question of whether you are RAC'ing
with raw or a cluster file system has no bearing on its capabilites... and
never did.

Regards
HJR
Nov 12 '05 #28
Serge Rielau wrote:
Daniel Morgan wrote:
If I have a single table in a tablespace in Oracle stored in a single
datafile the data is equally accessible from all nodes. Lose a node and
there is no negative effect on the ability to access any part of a the
data in that table. Federate the data as is required by shared nothing
architectures and the loss of a single node effectively kills the
system.

Thus with shared everything the more nodes the less likely a failure
whereas with shared nothing loss of a node makes part of the data
inaccessible.
Let's clarify the lingo:
DB2 II uses federation. One of the main properties of federation is that
any one piece of the federation is pretty independent from the rest.
They may not even know or admit they are part of it (a bit like Bavaria
or Quebec ;-)
DB2 UDB for LUW + DPF is a partitioned DBMS. The partitioning is done on
a logical level. Partitions assume logical (not physical) ownership of
data.
If a partition goes down it can either simply be restarted, or, if there
is a problem with the hardware that hosts the partiton the partition can
be restarted on another hardware.


Partitioning of data in DB2 is an entirely different concept than is the
partitioning of data in Oracle so the word may be misunderstood.
Partioning in Oracle has nothing to do with RAC or clustering or nodes.
In Oracle, as you know, data is never federated.
The data sits on the same physical storage subsystem as it does in a
shared disc.
There is no problem for a partition to access that data from any
physical node it happens to run on using the same technology Oracle
exploits
It can't use the same technology as shared everything is not shared
nothing and visa versa. Can you clarify what you intended to say?
So the question really boils down to this:
1. The time it takes to detect a partition is down. That is a universal
problem independent of the underlying technology.
That time is as long as it takes to rip the power cord out of the back
of the machine: A fraction of a second.
2. The time it takes to get the partition back into the game compared to
the failover work a RAC cluster has to do (such as remastering locks).
In my tests that is less than one second. Far to small with RAC to be of
concern to anyone.
A curious fact (no more no less) is that there are numerous kinds of
applications where a set of clients only ever accesses one or a few
partitions of the overall system. In these cases an unrelated partition
can fail over completely without notice by those clients. An example may
be the operation of a retail store chain with a partitioning key on the
store id. While headquartes may not be able to get the big picture while
a partition fails over, most the individual stores can operate without a
hitch throughout the event.
You are still using the word partitions as though there is some
relationship between partitions and RAC: There is not.
It's my understanding that remastering locks by contrast has a global
impact. Does this happen twice, btw? First time when the node goes down.
Second time when it comes up?
Lock what? I have no idea what you are refering to? Why would an Oracle
SELECT statement lock anything? Could it ... of course? But generally
that is not the case as the multiversioning architecture does not
require it.
While _adding_ a partition to increase capacity would usually be
followed by a redistribute which, today, has significant impact, the
fact that nodes are not physical has interesting implications.
That redistribution we should note requires that the database be taken
off-line. This never happens with RAC.
E.g. one can oversize the number of partitions per physical node.
Which equates to what in Oracle?
When more capacity is needed additional hardware can be made available
and the partitions themselves get redistributed amongst the new
infrastructure. Note that no data has to move since no partition was
added or removed.
And if one adds or removes nodes with RAC no alteration of any database
object is required nor does any line of code require modification.
Similarly one would never remove a partition during an HA event or to
lower resource consumption. One simply restarts the partition or clears
out a physical node.
Ah but we can. With the 10g grid control I can dynamically reassign
resources from a database node to an application server node and back.
I can dynamically redistribute the CPU and RAM where it is needed.
In reality most DB2 + DPF customers run more than one partition per
physical node. So this is indeed a realistic option.


But how does this change the fact that with DB2 the more nodes that
exist the more likely a system failure?

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #29
Howard J. Rogers wrote:
Some people talk about selects as though they were transactions. I don't.
And those people would be wrong, IMO.


I agree with one caveat:

SELECT ...
FROM ...
FOR UPDATE;

Which is the beginning of a transaction that must end with a commit or
rollback.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #30

"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1087602573.649098@yasure...
Howard J. Rogers wrote:
Some people talk about selects as though they were transactions. I don't. And those people would be wrong, IMO.


I agree with one caveat:

SELECT ...
FROM ...
FOR UPDATE;

Which is the beginning of a transaction that must end with a commit or
rollback.


Which is precisely why I included the sentence: "Selects don't (ordinarily)
take locks in Oracle" in my original reply. The "ordinarily" was
significant.

You may call it word games, but I wouldn't in any case regard a 'select for
update' as being the same as a 'select', any more than I would regard an
insert as being the same as an update. I very carefully said *selects* were
not transactions.

Regards
HJR
Nov 12 '05 #31
"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1087602479.441629@yasure...
Partitioning of data in DB2 is an entirely different concept than is the
partitioning of data in Oracle so the word may be misunderstood.
Partioning in Oracle has nothing to do with RAC or clustering or nodes.
In Oracle, as you know, data is never federated.

Federated in DB2 has a specific meaning other than what you are referring
to. DB2 partitioned tables are not Federated.

In DB2 Federated refers to a feature that allows access to multiple
databases in a single SQL statement (even if some of the databases are not
DB2).
Nov 12 '05 #32
Daniel Morgan wrote:
Serge Rielau wrote:
Daniel Morgan wrote:
If I have a single table in a tablespace in Oracle stored in a single
datafile the data is equally accessible from all nodes. Lose a node and
there is no negative effect on the ability to access any part of a the
data in that table. Federate the data as is required by shared nothing
architectures and the loss of a single node effectively kills the
system.

Thus with shared everything the more nodes the less likely a failure
whereas with shared nothing loss of a node makes part of the data
inaccessible.

Let's clarify the lingo:
DB2 II uses federation. One of the main properties of federation is
that any one piece of the federation is pretty independent from the
rest. They may not even know or admit they are part of it (a bit like
Bavaria or Quebec ;-)
DB2 UDB for LUW + DPF is a partitioned DBMS. The partitioning is done
on a logical level. Partitions assume logical (not physical) ownership
of data.
If a partition goes down it can either simply be restarted, or, if
there is a problem with the hardware that hosts the partiton the
partition can be restarted on another hardware.

Partitioning of data in DB2 is an entirely different concept than is the
partitioning of data in Oracle so the word may be misunderstood.
Partioning in Oracle has nothing to do with RAC or clustering or nodes.
In Oracle, as you know, data is never federated.

Actually both these interpretations can happily live next to each other:
Oracle refers to a partitioned tabled while DB2 refers to a partitioned
database. No judjement.. Just applications of the same technique at a
different level.
Orcale, btw, uses Transparant Gateway, and supposedly OLEDB I would
guess. So federation is very well available, although not heavily pushed.
The data sits on the same physical storage subsystem as it does in a
shared disc.
There is no problem for a partition to access that data from any
physical node it happens to run on using the same technology Oracle
exploits

It can't use the same technology as shared everything is not shared
nothing and visa versa. Can you clarify what you intended to say?

Yes I can, or at least I can try being fairly detached from cables and
plugs myself.
In an Orcale RAC setup each Oracle instance(?) of the cluster has access
to all the data. Obviously the system that the instance is running on
must have an interconnect to the storage subsystem.
Now while in DB2 with DPF a given databse partition only is allowed
access to teh data that it owns. that data usually still lives on the
shared storage subsystem and hance teh system that the partition runs on
has an interconnect to the that system, just the same as Oracle RAC has.
So, the system has access to all the data. The DB2 partition has not.
So the question really boils down to this:
1. The time it takes to detect a partition is down. That is a
universal problem independent of the underlying technology.

That time is as long as it takes to rip the power cord out of the back
of the machine: A fraction of a second.

OK, fine by me.
2. The time it takes to get the partition back into the game compared
to the failover work a RAC cluster has to do (such as remastering
locks).

In my tests that is less than one second. Far to small with RAC to be of
concern to anyone.

OK, I take your word for it.
A curious fact (no more no less) is that there are numerous kinds of
applications where a set of clients only ever accesses one or a few
partitions of the overall system. In these cases an unrelated
partition can fail over completely without notice by those clients. An
example may be the operation of a retail store chain with a
partitioning key on the store id. While headquartes may not be able to
get the big picture while
a partition fails over, most the individual stores can operate without
a hitch throughout the event.

You are still using the word partitions as though there is some
relationship between partitions and RAC: There is not.

I was talking about DB2. Above is a DB2 + DPF scenario using DB2 Lingo
which I set out to clarify.
It's my understanding that remastering locks by contrast has a global
impact. Does this happen twice, btw? First time when the node goes
down. Second time when it comes up?

Lock what? I have no idea what you are refering to? Why would an Oracle
SELECT statement lock anything? Could it ... of course? But generally
that is not the case as the multiversioning architecture does not
require it.

Where did SELECT come into play here? This is cache-fusion stuff. There
is a need to synch up. The details you know certainly better than I do,
and HJR better than both of us I presume.
While _adding_ a partition to increase capacity would usually be
followed by a redistribute which, today, has significant impact, the
fact that nodes are not physical has interesting implications.

That redistribution we should note requires that the database be taken
off-line. This never happens with RAC.

Significant impact, as I say.
E.g. one can oversize the number of partitions per physical node.

Which equates to what in Oracle?

Does it have to equate to anything on Oracle?
I just describe a DB2 + DPF setup.
When more capacity is needed additional hardware can be made available
and the partitions themselves get redistributed amongst the new
infrastructure. Note that no data has to move since no partition was
added or removed.

And if one adds or removes nodes with RAC no alteration of any database
object is required nor does any line of code require modification.

OK please read teh following sentence ATOMIC. Do not take parts out of it:
BEGIN ATOMIC
Adding a node in DB2 + DPF does not require application changes.
If you go from n -> m where _n>1_ there is no need.
Going from DB2 without DPF to DB2 with DPF requires some thought,
because now the partitioning keys need to be decided on.
W.r.t. speeding up, e.g. batch-jobs Oracle and DB2 are actually in
a similar boat. To speed up a procedural thing one calls it n-times
and partitions the dataset to keep the threads out of each others
hair. There is no magic here.
The difference between a parallelized Oracle batchjob and a
parallelized DB2 batch job is that in Oracle you may want to partition
by ranges of data (maybe along table-partitions..).
In DB2 the parallization occurs first along teh partitioning key then
ranges can be used in addition ranges.
If a new database partition gets added, no change.
Simple more instances of the batch job.
Similarly one would never remove a partition during an HA event or to
lower resource consumption. One simply restarts the partition or
clears out a physical node.

Ah but we can. With the 10g grid control I can dynamically reassign
resources from a database node to an application server node and back.
I can dynamically redistribute the CPU and RAM where it is needed.

We? I thought you are not tied to Oracle?
Yes and I will the last to say that that's not a good thing (albeit I
don't know it. However do you see anything in the design described above
that precludes DB2 for DPF from doing the same. There may not be a
"grid-control" but that's no design flaw, just a choice of focus.
Automatic failover, workload balancing and client reroute tehcnology are
independent of "shared disk" vs "shared nothing".
In reality most DB2 + DPF customers run more than one partition per
physical node. So this is indeed a realistic option.

But how does this change the fact that with DB2 the more nodes that
exist the more likely a system failure?

The more RAC nodes you have the more likely it is that one of these Dell
boxes gets sick.
Oracle RAC can fail over and not turn the sick box into a failed system.
The same is true for DB2 + DPF. A database partition can fail over and
not turn a sick box into a failed system.
You have to say good by of the notion that a DB2 database partition
completely and _irrevocable_ fails, it's just a program.
If a program is that sick the system as a whole is sick, because it's
just a copy of the same program with the same bugs and that is as true
for DB2 + DPF as it is for Oracle RAC.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #33
Howard J. Rogers wrote:
"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1087602573.649098@yasure...
Howard J. Rogers wrote:

Some people talk about selects as though they were transactions. I
don't.
And those people would be wrong, IMO.


I agree with one caveat:

SELECT ...
FROM ...
FOR UPDATE;

Which is the beginning of a transaction that must end with a commit or
rollback.

Which is precisely why I included the sentence: "Selects don't (ordinarily)
take locks in Oracle" in my original reply. The "ordinarily" was
significant.

You may call it word games, but I wouldn't in any case regard a 'select for
update' as being the same as a 'select', any more than I would regard an
insert as being the same as an update. I very carefully said *selects* were
not transactions.

Regards
HJR


Not a word game at all. In the Oracle group perfectly appropriate. But
as this was also cross-posted to the DB2 group a bit of explanation is
helpful to those who may not understand the implication.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #34
Mark A wrote:
"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1087602479.441629@yasure...
Partitioning of data in DB2 is an entirely different concept than is the
partitioning of data in Oracle so the word may be misunderstood.
Partioning in Oracle has nothing to do with RAC or clustering or nodes.
In Oracle, as you know, data is never federated.


Federated in DB2 has a specific meaning other than what you are referring
to. DB2 partitioned tables are not Federated.

In DB2 Federated refers to a feature that allows access to multiple
databases in a single SQL statement (even if some of the databases are not
DB2).


Oracle does not use the word federated to describe any part of its
architecture and I assumed Microsoft's definition had been stolen from
DB2.

Thanks for the clarification.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #35
Comments in-line.

Serge Rielau wrote:
Daniel Morgan wrote:
Serge Rielau wrote:
Daniel Morgan wrote:

If I have a single table in a tablespace in Oracle stored in a single
datafile the data is equally accessible from all nodes. Lose a node and
there is no negative effect on the ability to access any part of a the
data in that table. Federate the data as is required by shared nothing
architectures and the loss of a single node effectively kills the
system.

Thus with shared everything the more nodes the less likely a failure
whereas with shared nothing loss of a node makes part of the data
inaccessible.

Let's clarify the lingo:
DB2 II uses federation. One of the main properties of federation is
that any one piece of the federation is pretty independent from the
rest. They may not even know or admit they are part of it (a bit like
Bavaria or Quebec ;-)
DB2 UDB for LUW + DPF is a partitioned DBMS. The partitioning is done
on a logical level. Partitions assume logical (not physical)
ownership of data.
If a partition goes down it can either simply be restarted, or, if
there is a problem with the hardware that hosts the partiton the
partition can be restarted on another hardware.
Partitioning of data in DB2 is an entirely different concept than is the
partitioning of data in Oracle so the word may be misunderstood.
Partioning in Oracle has nothing to do with RAC or clustering or nodes.
In Oracle, as you know, data is never federated.


Actually both these interpretations can happily live next to each other:
Oracle refers to a partitioned tabled while DB2 refers to a partitioned
database. No judjement.. Just applications of the same technique at a
different level.
Orcale, btw, uses Transparant Gateway, and supposedly OLEDB I would
guess. So federation is very well available, although not heavily pushed.
The data sits on the same physical storage subsystem as it does in a
shared disc.
There is no problem for a partition to access that data from any
physical node it happens to run on using the same technology Oracle
exploits


It can't use the same technology as shared everything is not shared
nothing and visa versa. Can you clarify what you intended to say?


Yes I can, or at least I can try being fairly detached from cables and
plugs myself.
In an Orcale RAC setup each Oracle instance(?) of the cluster has access
to all the data. Obviously the system that the instance is running on
must have an interconnect to the storage subsystem.
Now while in DB2 with DPF a given databse partition only is allowed
access to teh data that it owns. that data usually still lives on the
shared storage subsystem and hance teh system that the partition runs on
has an interconnect to the that system, just the same as Oracle RAC has.
So, the system has access to all the data. The DB2 partition has not.


Which is critical when considering mean-time between failures which was
the point I was making.
So the question really boils down to this:
1. The time it takes to detect a partition is down. That is a
universal problem independent of the underlying technology.


That time is as long as it takes to rip the power cord out of the back
of the machine: A fraction of a second.


OK, fine by me.
2. The time it takes to get the partition back into the game compared
to the failover work a RAC cluster has to do (such as remastering
locks).


In my tests that is less than one second. Far to small with RAC to be of
concern to anyone.


OK, I take your word for it.


Even Oracle's cover-your-a.. marketing talk says 3 seconds. So it really
is inconsequential.
A curious fact (no more no less) is that there are numerous kinds of
applications where a set of clients only ever accesses one or a few
partitions of the overall system. In these cases an unrelated
partition can fail over completely without notice by those clients.
An example may be the operation of a retail store chain with a
partitioning key on the store id. While headquartes may not be able
to get the big picture while
a partition fails over, most the individual stores can operate
without a hitch throughout the event.


You are still using the word partitions as though there is some
relationship between partitions and RAC: There is not.


I was talking about DB2. Above is a DB2 + DPF scenario using DB2 Lingo
which I set out to clarify.


Thanks.
While _adding_ a partition to increase capacity would usually be
followed by a redistribute which, today, has significant impact, the
fact that nodes are not physical has interesting implications.


That redistribution we should note requires that the database be taken
off-line. This never happens with RAC.


Significant impact, as I say.
E.g. one can oversize the number of partitions per physical node.


Which equates to what in Oracle?


Does it have to equate to anything on Oracle?
I just describe a DB2 + DPF setup.


No. I was trying to determine if there was an equivalency. Often the way
each vendor uses/misuses words means the same thing exists but appears
to be different. Or something doesn't exist but appears to.
When more capacity is needed additional hardware can be made
available and the partitions themselves get redistributed amongst the
new infrastructure. Note that no data has to move since no partition
was added or removed.


And if one adds or removes nodes with RAC no alteration of any database
object is required nor does any line of code require modification.


OK please read teh following sentence ATOMIC. Do not take parts out of it:
BEGIN ATOMIC
Adding a node in DB2 + DPF does not require application changes.
If you go from n -> m where _n>1_ there is no need.
Going from DB2 without DPF to DB2 with DPF requires some thought,
because now the partitioning keys need to be decided on.
W.r.t. speeding up, e.g. batch-jobs Oracle and DB2 are actually in
a similar boat. To speed up a procedural thing one calls it n-times
and partitions the dataset to keep the threads out of each others
hair. There is no magic here.
The difference between a parallelized Oracle batchjob and a
parallelized DB2 batch job is that in Oracle you may want to partition
by ranges of data (maybe along table-partitions..).
In DB2 the parallization occurs first along teh partitioning key then
ranges can be used in addition ranges.
If a new database partition gets added, no change.
Simple more instances of the batch job.


I wasn't aware of DPF. Correct me if I am wrong ... but it seems DPF
is an extra cost add-in only available on ESE systems with that some
functionality only works on AIX. One question ... is it supported by the
optimizer? There is also, from what I can see a master instance which,
if it goes down takes down, the entire thing down with it. Overall not
a very pretty picture. And providing functionality available in Oracle
at no aditional cost, on all operating systems, and without the
liability of one node being capable of bringing down the entire cluster.
Similarly one would never remove a partition during an HA event or to
lower resource consumption. One simply restarts the partition or
clears out a physical node.


Ah but we can. With the 10g grid control I can dynamically reassign
resources from a database node to an application server node and back.
I can dynamically redistribute the CPU and RAM where it is needed.


We? I thought you are not tied to Oracle?


Not tied at all. I also work on all of the other major databases. But
when it comes to clustering ... I always recommend RAC unless mainframes
are involved.
Yes and I will the last to say that that's not a good thing (albeit I
don't know it. However do you see anything in the design described above
that precludes DB2 for DPF from doing the same. There may not be a
"grid-control" but that's no design flaw, just a choice of focus.
Automatic failover, workload balancing and client reroute tehcnology are
independent of "shared disk" vs "shared nothing".
In reality most DB2 + DPF customers run more than one partition per
physical node. So this is indeed a realistic option.
But how does this change the fact that with DB2 the more nodes that
exist the more likely a system failure?


The more RAC nodes you have the more likely it is that one of these Dell
boxes gets sick.


But if it gets sick nothing changes.
Oracle RAC can fail over and not turn the sick box into a failed system.
Not in my experience unless I am misunderstanding what you mean by using
the highly technical term ... "sick." ;-)
The same is true for DB2 + DPF. A database partition can fail over and
not turn a sick box into a failed system.
You have to say good by of the notion that a DB2 database partition
completely and _irrevocable_ fails, it's just a program.
If a program is that sick the system as a whole is sick, because it's
just a copy of the same program with the same bugs and that is as true
for DB2 + DPF as it is for Oracle RAC.

Cheers
Serge


I've now had a chance to read all of the DPF documentation (well at
least a lot of it) available on the net. I am far from impressed.
Bad code is bad code so whether run stand-alone, clustered, or whatever
blame the developers and QA. But when disaster strikes, and a CPU fries
itself or someone unplugs a box ... that's when the system's survival
is most at risk.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #36
Daniel Morgan wrote:
Oracle does not use the word federated to describe any part of its
architecture and I assumed Microsoft's definition had been stolen from
DB2.

I may blame Microsft for a lot, but "federated database" is a computer
science term. Here is a random link with a definition.

http://www-lsi.upc.es/bloom/home.html

Distributed means roughly that the parts are scattered, but there is one
whole DBMS.
Federated means that each part is autonomous.
If an Oracle system incorporates data from another, independent Oracle
system, then you are facing a federated database setup, whether you or
Oracle call it that way or not, any computer science student beyond his
introductory databases course will know what it means.
The next step is a DBMS that is not only federated, but also heterogeneous.
That's where shades of grey come in.
If Oracle and DB2 end up in the same system that could be called
heterogeneous (different vendors, different engines).
It's more common though to refer to heterogeneity if sources with
different models are involved. E.g. DB2 (realtional) accessing Blast
(not relational).
IBM is concentrating a lot on that part of the picture with "Garlic"
http://portal.acm.org/citation.cfm?i...CM&coll=portal

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #37
Daniel Morgan wrote:
<snip>
The data sits on the same physical storage subsystem as it does in a
shared disc.
There is no problem for a partition to access that data from any
physical node it happens to run on using the same technology Oracle
exploits
It can't use the same technology as shared everything is not shared
nothing and visa versa. Can you clarify what you intended to say? Yes I can, or at least I can try being fairly detached from cables and
plugs myself.
In an Orcale RAC setup each Oracle instance(?) of the cluster has
access to all the data. Obviously the system that the instance is
running on must have an interconnect to the storage subsystem.
Now while in DB2 with DPF a given databse partition only is allowed
access to teh data that it owns. that data usually still lives on the
shared storage subsystem and hance teh system that the partition runs
on has an interconnect to the that system, just the same as Oracle RAC
has.
So, the system has access to all the data. The DB2 partition has not.

Which is critical when considering mean-time between failures which was
the point I was making.

No it isn't. A node in RAC can die, a DB2 Partition can die. Lets assume
both with the same likelyhood (let's put code quality aside for the sake
of the argument..)
You have to willing (well you don't ahve to but it would be open mided)
to concede that restarting the failed DB2 partition (on a different box)
is an alternative to the Oracle RAC approach of redistributing teh work
across the remaining RAC nodes.
I don't ask you to say whether it's better or not, I just ask you to
consider it an alternative approach to solve the same problem.
2. The time it takes to get the partition back into the game
compared to the failover work a RAC cluster has to do (such as
remastering locks).
In my tests that is less than one second. Far to small with RAC to be of
concern to anyone. Even Oracle's cover-your-a.. marketing talk says 3 seconds. So it really
is inconsequential.

OK, so recussicating the failed DB2 partition in 3 seconds would then
alos fall under inconsequential. And given that most connectiosn to
other partitions may not even feel that the DB2 partition is down
(because they don't talk to it) then maybe a few more seconds could be
granted as acceptable.
E.g. one can oversize the number of partitions per physical node.
Which equates to what in Oracle?

Does it have to equate to anything on Oracle?
I just describe a DB2 + DPF setup.

No. I was trying to determine if there was an equivalency. Often the way
each vendor uses/misuses words means the same thing exists but appears
to be different. Or something doesn't exist but appears to.

I think the equivalency would be to run an 15 Node RAC on 3 boxes (on
LAPRs perhaps).
If more resources are needed you get 2 more boxes and move 2 nodes from
each box over to the two new boxes.
The number of nodes stayed the same, but you gained 66% more horsepower.
I doubt that would make much sense with the RAC architecture, but it
makes a lot of sense for DB2's. I wasn't aware of DPF. Uhm.. but's what half our discussion in past were about :-( Correct me if I am wrong ... but it seems DPF
is an extra cost add-in only available on ESE systems with that some
functionality only works on AIX. One question ... is it supported by the
optimizer? To the best of my knowledge DPF is offered on all supported platforms of
DB2 for LUW. The optimizer is very aware of DPF. An important job is to
make sure the function and the data meet in the right place.
So only minimal data needs to cross the wire. There is also, from what I can see a master instance which,
if it goes down takes down, the entire thing down with it. Overall not
a very pretty picture. Not quite true, but not totally wrong either.
The coordinator is the DB2 Partition that a client connects to. Each
partition serves as coordinator. There is no master database partition.
However, the DB2 catalogs today reside on one specific partition.
So, if that partition came down and a local coordinator needed
information which is not in it's cache (to compile a new statement
referencing e.g. a previously unheard table) then that would be bad.
Most customers I know using DB2 + DPF therefore do not allow connections
to the catalog partition. It is dedicated to serve meta data.And providing functionality available in Oracle
at no aditional cost, on all operating systems, and without the
liability of one node being capable of bringing down the entire cluster. OK, now we are talking licensing. I know neither Oracle nor DB2
licensing well enough to comment. I doubt RAC is free for Oracle's
enterprice license, though.
The more RAC nodes you have the more likely it is that one of these
Dell boxes gets sick.

But if it gets sick nothing changes.
Oracle RAC can fail over and not turn the sick box into a failed system.

Not in my experience unless I am misunderstanding what you mean by using
the highly technical term ... "sick." ;-)

Misunderstanding. Its the whole point of RAC to fail over (in contrast
to simply fail)
I've now had a chance to read all of the DPF documentation (well at
least a lot of it) available on the net. I am far from impressed.

You are a fast reader I must say.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #38
Serge Rielau wrote:
Daniel Morgan wrote:
>Oracle does not use the word federated to describe any part of its

architecture and I assumed Microsoft's definition had been stolen from
DB2.


I may blame Microsft for a lot, but "federated database" is a computer
science term. Here is a random link with a definition.

http://www-lsi.upc.es/bloom/home.html
Cheers
Serge


Thanks but I know the computer science definition of these words ...
what I don't know is how they have been used, this week, within the
context of a marketing and sales organization.

Consider, for a minute that the word 'database' doesn't have the same
meaning between products. Once you understand that ... you don't take
on faith much of anything without a dictionary current to the last
advertising campaign.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #39
Serge Rielau wrote:
Daniel Morgan wrote:
<snip>

No it isn't. A node in RAC can die, a DB2 Partition can die. Lets assume
both with the same likelyhood (let's put code quality aside for the sake
of the argument..)
You have to willing (well you don't ahve to but it would be open mided)
to concede that restarting the failed DB2 partition (on a different box)
is an alternative to the Oracle RAC approach of redistributing teh work
across the remaining RAC nodes.
I don't ask you to say whether it's better or not, I just ask you to
consider it an alternative approach to solve the same problem.
Please correct me if you think I am incorrect. But losing a single node
with RAC can not deprive users of access to data. The system continues
to run with no effect other than the loss of a few CPU's and their
associated RAM.

With DB2 I could lose a node and either lose access to some of the data
or, worst case, lose the entire database application.
> 2. The time it takes to get the partition back into the game
> compared to the failover work a RAC cluster has to do (such as
> remastering locks).

In my tests that is less than one second. Far to small with RAC to
be of
concern to anyone.


Even Oracle's cover-your-a.. marketing talk says 3 seconds. So it really
is inconsequential.


OK, so recussicating the failed DB2 partition in 3 seconds would then
alos fall under inconsequential. And given that most connectiosn to
other partitions may not even feel that the DB2 partition is down
(because they don't talk to it) then maybe a few more seconds could be
granted as acceptable.


Provided it is not the instance owning machine? Lose that, and as I
understand it, you are toast.

To quote the IBM doc linked below: "As indicated earlier, the DB2
Instance owning machine in a DPF environment is the one whose associated
disk physically stores the instance home directory."

Those more familiar with Oracle might want to check out:
http://www-106.ibm.com/developerwork...ong/index.html
to better understand what DPF is.
> E.g. one can oversize the number of partitions per physical node.

Which equates to what in Oracle?

Does it have to equate to anything on Oracle?
I just describe a DB2 + DPF setup.


No. I was trying to determine if there was an equivalency. Often the way
each vendor uses/misuses words means the same thing exists but appears
to be different. Or something doesn't exist but appears to.


I think the equivalency would be to run an 15 Node RAC on 3 boxes (on
LAPRs perhaps).


15 nodes would be 15 boxes with RAC.
If more resources are needed you get 2 more boxes and move 2 nodes from
each box over to the two new boxes.
The number of nodes stayed the same, but you gained 66% more horsepower.
I doubt that would make much sense with the RAC architecture, but it
makes a lot of sense for DB2's.
I agree.
I wasn't aware of DPF.


Uhm.. but's what half our discussion in past were about :-(


C'est dommage. I am here to learn too.
Correct me if I am wrong ... but it seems DPF
is an extra cost add-in only available on ESE systems with that some
functionality only works on AIX. One question ... is it supported by the
optimizer?


To the best of my knowledge DPF is offered on all supported platforms of
DB2 for LUW.


Not according to:
http://www.developer.ibm.com/tech/fa...al?oid=2:82779

The optimizer is very aware of DPF. An important job is to make sure the function and the data meet in the right place.
So only minimal data needs to cross the wire.
Does that agree with the colorful diagram on the first link, above?
> There is also, from what I can see a master instance which,

if it goes down takes down, the entire thing down with it. Overall not
a very pretty picture.


Not quite true, but not totally wrong either.
The coordinator is the DB2 Partition that a client connects to. Each
partition serves as coordinator. There is no master database partition.


That does not agree with the statement from the IBM quoted above with
respect to there being an "instance owning machine." How do you survive
without access to the instance owning directory?
However, the DB2 catalogs today reside on one specific partition.
So, if that partition came down and a local coordinator needed
information which is not in it's cache (to compile a new statement
referencing e.g. a previously unheard table) then that would be bad.
Is that what I have refered to above?
Most customers I know using DB2 + DPF therefore do not allow connections
to the catalog partition. It is dedicated to serve meta data.
And providing functionality available in Oracle
at no aditional cost, on all operating systems, and without the
liability of one node being capable of bringing down the entire cluster.


OK, now we are talking licensing. I know neither Oracle nor DB2
licensing well enough to comment. I doubt RAC is free for Oracle's
enterprice license, though.


Nothing is free. But RAC is now part of standard edition and up to 4
procs is free. I don't know the latest licensing for Enterprise Edition
but it would, by necessity, need to be more generous given the higher cost.
The more RAC nodes you have the more likely it is that one of these
Dell boxes gets sick.


But if it gets sick nothing changes.
Oracle RAC can fail over and not turn the sick box into a failed system.


Not in my experience unless I am misunderstanding what you mean by using
the highly technical term ... "sick." ;-)


Misunderstanding. Its the whole point of RAC to fail over (in contrast
to simply fail)


But what causes it to fail over ... is in fact going to kill any
connections. So I'm not sure what the issue is you are speaking of.
I've now had a chance to read all of the DPF documentation (well at
least a lot of it) available on the net. I am far from impressed.


You are a fast reader I must say.

Cheers
Serge


Welcome to teaching at the university level.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #40
> Please correct me if you think I am incorrect. But losing a single node
with RAC can not deprive users of access to data. The system continues
to run with no effect other than the loss of a few CPU's and their
associated RAM.

With DB2 I could lose a node and either lose access to some of the data
or, worst case, lose the entire database application.
Do you mean loose the database permanently or just until a fallover can be
accomplished or the hardware repaired? I don't know of a situation where
data would be lost permanently unless there was a multiple disk failure
affecting both the data and logs.
Correct me if I am wrong ... but it seems DPF
is an extra cost add-in only available on ESE systems with that some
functionality only works on AIX. One question ... is it supported by the optimizer?

Of course it is supported by the optimizer. The use of multiple partitions
enables parallel database access (inter-partition parallelism).
Intra-partition parallelism is available even on a single partition system
in certain circumstances.
To the best of my knowledge DPF is offered on all supported platforms of
DB2 for LUW.


Not according to:
http://www.developer.ibm.com/tech/fa...al?oid=2:82779

I don't know exactly what statement you are reading, but DPF (partitioning)
is available on DB2 ESE for Linux, UNIX, and Windows. These are the
platforms known as LUW.
Nov 12 '05 #41
> > > To the best of my knowledge DPF is offered on all supported platforms
of
DB2 for LUW.
Not according to:
http://www.developer.ibm.com/tech/fa...al?oid=2:82779

I don't know exactly what statement you are reading, but DPF

(partitioning) is available on DB2 ESE for Linux, UNIX, and Windows. These are the
platforms known as LUW.

Here is a link with the TPC-H benchmark results for DB2 on Linux with
multiple partitions. The first IBM system on the 100 GB test has 8 physical
nodes, with 2 CPU's per node and has 2 DB2 partitions per physical node
(total of 16 partitions).
http://www.tpc.org/tpch/results/tpch_perf_results.asp

The third system on the list (the 2nd IBM system) is a DB2 for Windows on a
single physical node with 8 CPU's running 8 DB2 partitions.
Nov 12 '05 #42
Mark A wrote:
Please correct me if you think I am incorrect. But losing a single node
with RAC can not deprive users of access to data. The system continues
to run with no effect other than the loss of a few CPU's and their
associated RAM.

With DB2 I could lose a node and either lose access to some of the data
or, worst case, lose the entire database application.


Do you mean loose the database permanently or just until a fallover can be
accomplished or the hardware repaired? I don't know of a situation where
data would be lost permanently unless there was a multiple disk failure
affecting both the data and logs.


I meant only until it is brought back on-line. DB2 is far more robust to
become ashes ... just toast. ;-)
Correct me if I am wrong ... but it seems DPF
is an extra cost add-in only available on ESE systems with that some
functionality only works on AIX. One question ... is it supported by
the
optimizer?
Of course it is supported by the optimizer. The use of multiple partitions
enables parallel database access (inter-partition parallelism).
Intra-partition parallelism is available even on a single partition system
in certain circumstances.


So the optimizer knows that a query not requiring information from all
but one partition can and will query only the single partition?
To the best of my knowledge DPF is offered on all supported platforms of
DB2 for LUW.


Not according to:
http://www.developer.ibm.com/tech/fa...al?oid=2:82779


I don't know exactly what statement you are reading, but DPF (partitioning)
is available on DB2 ESE for Linux, UNIX, and Windows. These are the
platforms known as LUW.


Thanks. But still limited to ESE and still an additional license cost.
Is that correct?

Thanks.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #43
> > Of course it is supported by the optimizer. The use of multiple
partitions
enables parallel database access (inter-partition parallelism).
Intra-partition parallelism is available even on a single partition system
in certain circumstances.


So the optimizer knows that a query not requiring information from all
but one partition can and will query only the single partition?

DB2 partitioning is designed to optimize parallel SQL operations and
therefore data is randomly spread across the partitions with hash
partitioning to spread the load across the partitions (each with its own
processors, memory, etc.) This cuts down on the total execution time of the
SQL in almost all cases. In the case of a single row retrieval based on the
partitioning key, DB2 can calculate which partition has the data.

Range partitioning is not directly supported in DB2, but the same thing can
be done with multiple tables and a UNION ALL view, and the optimizer will
only query the table required. This has many benefits from a DBA standpoint
where partitions have historical data partitioned by date, because once the
date has closed, the table can be backed up without continuously backing up
a much larger partitioned table as new transactions are added to the same
table in Oracle.

The admin cost of adding new tables is about the same as adding new
partitions for each month (in an Oracle range partitioning scenario). This
can be done manually or automatically via application software. One example
of an application that uses multiple tables for time periods instead of
range partitioning is a check archive system by Viewpointe Archive Services:
http://www.viewpointearchive.com/default.aspx?pageid=5

However, there is also a feature in DB2 called MDC (multi-dimensional
clustering) which has some of the characteristics of range partitioning in
that it groups like data close together in a partitioned environment.

We have been through this many times before.

The bottom line is that total performance of Oracle and DB2 are similar.
Oracle "may" slightly excel in some areas and DB2 definitely excels in
others. 99.9% of customers and applications would not notice much
difference. The only exception would be if a company would use one database
product for one type system, and a different database for another type
application. The cost benefit tradeoff of that would be outweighed by the
increased cost of not standardizing on a single product.
To the best of my knowledge DPF is offered on all supported platforms ofDB2 for LUW.

Not according to:
http://www.developer.ibm.com/tech/fa...al?oid=2:82779


I don't know exactly what statement you are reading, but DPF (partitioning) is available on DB2 ESE for Linux, UNIX, and Windows. These are the
platforms known as LUW.


Thanks. But still limited to ESE and still an additional license cost.
Is that correct?

Yes DPF is an additional cost on top of the ESE license. Even so, it is less
expensive than an equivalent Oracle system.
Nov 12 '05 #44
Fair enough. That makes it the more important to reach a deep
understanding of a product before passing judgement.
After all, none of us falls for the patented "Krisp-O-Mat" when buying a
new fridge, right? That, us professionals, should leave to salespeople
in furniture stores and the shopping channel.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #45
Daniel Morgan wrote:
Mark A wrote:
Please correct me if you think I am incorrect. But losing a single node
with RAC can not deprive users of access to data. The system continues
to run with no effect other than the loss of a few CPU's and their
associated RAM.

With DB2 I could lose a node and either lose access to some of the data
or, worst case, lose the entire database application. Daniel, you are still measures with two metrics :-)
When an Oracle RAC node goes down it has information that is needed by
the other nodes. All the remaining nodes are affected by that during
this timeframe where RAC gets its balance back.
I take your word, that this is in the second ballpark.
Now in a DB2 + DPF scenario, if DB Partition goes down all clients
connected to that partition get kicked.
All other clients will not get kicked and they may or may not feel that
a partition went down, depending on whether the downed partition is
needed or not. When you fire the gardner it will not slow down the
kitchenwork. The likely hood to get food is not affected by the number
of gardners you have.
This is a core differentiator between Oracle RAC and DB2 + DPF.
(not better, not worse, just different)
Do you mean loose the database permanently or just until a fallover
can be
accomplished or the hardware repaired? I don't know of a situation where
data would be lost permanently unless there was a multiple disk failure
affecting both the data and logs.

I meant only until it is brought back on-line. DB2 is far more robust to
become ashes ... just toast. ;-)

Right, so now the question is the race against time to get the down
partition up again. On the same hardware, different hardware, doesn't
matter.
Correct me if I am wrong ... but it seems DPF
> is an extra cost add-in only available on ESE systems with that some
> functionality only works on AIX. One question ... is it supported by the
> optimizer?

Of course it is supported by the optimizer. The use of multiple
partitions
enables parallel database access (inter-partition parallelism).
Intra-partition parallelism is available even on a single partition
system
in certain circumstances.

So the optimizer knows that a query not requiring information from all
but one partition can and will query only the single partition?

Absolutely.
Let's stick with a store example.
The schema involves a table with store-date (pk store id), an inventory
table (pk: store id, product id), a product table (pk: product id).
The partitioning would ne by store_id
(DB2 Stinger's design advisor would propose it).
The product table and store table is pretty fixed and small. It would be
replicated (another word for a synchronized cache).
If you want to query the inventory of Coffee in Starbucks at Time square
and how much that is worth.
The SQL will be a join across all those tables to look up the surrogate
keys for the store, retrieve all the product ids for coffee and then
sum up the price (stored in the inventory table) times inventory.
So what does the plan look like?
1. Get the store id (local to the coordinator partition)
2. Send the rest of the request to the db partition holding the that
store id data
Do a local join between the inventory and product table doing the
local aggregation
3. Pass the result back to the coordinator

At most two nodes were involved.
If the software associating the clients to db partitions has any brains
it uses the same mapping as DB2 and the coordinator is the right
partition to begin with.
Let's assume the query comes in with the store id rather than the store
name (more realistic actually). In this case a feature called "local
bypass" kicks in. The coordinator would simply pass the query on to the
right node.
Now does this require a bit of DB Schema design work? Absolutely.
But in a BI environment which is where DB2 + DPF plays you have that
knowledge. In return for their efforts the user gets near linear
scalability once the homework has been done.

DB2 + DPF is not sold as an HA solution it is sold for scale out.
To the best of my knowledge DPF is offered on all supported
platforms of
DB2 for LUW.
Not according to:
http://www.developer.ibm.com/tech/fa...al?oid=2:82779

I don't know exactly what statement you are reading, but DPF
(partitioning)
is available on DB2 ESE for Linux, UNIX, and Windows. These are the
platforms known as LUW.

Thanks. But still limited to ESE and still an additional license cost.
Is that correct?

Yes. It's a scale out solution.
If anyone tries to sell DB2 + DPF to a mom&pop shop with 100GB for HA
(s)he's missing the boat.

If you just want a simple HA solution with DB2 Stinger you will use the
HDR feature.
For currently available releases HA is driven with general solutions.
I'm no experts in this.. Too far away from my area.
For HA you need to compare: DB2 + HA solutions or soon DB2 + HDR.
To learn about HDR best ask in the informix newsgroup for HADR.
HDR is stage one of that port.

Just to wrap up:
The point being made is:
1. DB2 + DPF for near unlimited scale out
(DB2 supports 999 DB Partitions,
there >100 partition installation out there)
2. DB2 + DPF _supports_ HA solutions if needed
3. DB2 + DPF is not an HA feature and never was meant to be one.

My personal toughts on RAC are:
Oracle RAC is an HA feature with neat limited scale out ability
Oracle RAC has yet to proof how far it can scale out.

I don't believe that near linear scale out can be achieved without a
divide and conquer strategy of sorts. That strategy requires schema/app
changes.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #46

Range partitioning is not directly supported in DB2, but the same thing can
be done with multiple tables and a UNION ALL view, and the optimizer will
only query the table required.
There is an implication here that Range Partitioning in Oracle and UNION
ALL table views in DB2 are equivalent. They are not. UNION ALL views in
DB2 are equivalent to UNIOn ALL views in Oracle. There are many
additonal things you can do with Range Partitioning, including global
indexes. That why Oracle implemented Range Partitioning (Oracle8) after
it had implemented UNION ALL views (Oracle 7.1)

This has many benefits from a DBA standpoint
where partitions have historical data partitioned by date, because once the
date has closed, the table can be backed up without continuously backing up
a much larger partitioned table as new transactions are added to the same
table in Oracle.
There is an implication here that DB2's UNION ALL views better support
rolling window operations than Oracle's Range Partitions. They are not.
A major design point of Range Partitions when they were provided in
Oracle8 was to add better support for rolling window operations than
what could be done for UNION ALL tables. You can, of course, back up 1
or as many partitions in a range partitioned table as you want to.
That's sort of the point.

The admin cost of adding new tables is about the same as adding new
partitions for each month (in an Oracle range partitioning scenario). This
can be done manually or automatically via application software. One example
of an application that uses multiple tables for time periods instead of
range partitioning is a check archive system by Viewpointe Archive Services:
http://www.viewpointearchive.com/default.aspx?pageid=5

Hmm - seems to require a LOT of help from IBM to make it work however -

"In addition to on demand storage, the IBM solution includes fully
managed hosting for Viewpointe’s IBM pSeries™ servers running the AIX®
operating system, as well as management of the advanced storage area
network (SAN)."

"To help Viewpointe manage its architecture and expand its capabilities,
IBM has a team in place that is dedicated completely to the Viewpointe
solution. The team includes a director of operations, who is responsible
for the smooth day-to-day operation of Viewpointe’s applications.
Through this single point of contact, Viewpointe has rapid access to a
full-time staff with a wealth of technical experience, as well as a deep
background in check processing.

A chief technical architect helps support Viewpointe’s existing
infrastructure and develops new requirements as new clients are brought
on or new product functionality is added. Another IBM manager leads a
solution design and delivery team that develops, codes, tests and
implements new product offerings. There is even a dedicated business
development manager who provides IBM sales resources during Viewpointe’s
sales process and works to increase IBM sales teams’ awareness of
Viewpointe’s product offering."

So let me understand this - IBM designs it, codes it, tests it, hosts
it, and sells it. What does Viewpointe the company actually do ? IBM
reference calls ?

<snip>
99.9% of customers and applications would not notice much
difference.
<snip>

Yes DPF is an additional cost on top of the ESE license. Even so, it is less
expensive than an equivalent Oracle system.


I guess if has to be if you have to also factor in the service cost of
an IBM Director of Operations, IBM Chief Technical Architect, an IBM
Development Manager and IBM Development Team, and an IBM BDM. By my
count, that's at least 3 million a year fully loaded before you even buy
a single peice of hardware.

Nov 12 '05 #47
Uhm.. given 3petabytes of storage I'm quite certain there is more to do
that maintaining a union all view.
Not sure how much Oracle Development Managers make, but your math
suggests that even I could overcome my aversion for Oracle's CEO for
that money ;-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #48
"Mark Townsend" <ma***********@comcast.net> wrote in message
news:fo5Bc.143108$Ly.137947@attbi_s01...
Range partitioning is not directly supported in DB2, but the same thing can be done with multiple tables and a UNION ALL view, and the optimizer will only query the table required.
There is an implication here that Range Partitioning in Oracle and UNION
ALL table views in DB2 are equivalent. They are not. UNION ALL views in
DB2 are equivalent to UNIOn ALL views in Oracle. There are many
additonal things you can do with Range Partitioning, including global
indexes. That why Oracle implemented Range Partitioning (Oracle8) after
it had implemented UNION ALL views (Oracle 7.1)

No they are not exactly the same. But they are close enough for most people
and most applications. DB2 for z/OS has had range partitioning for over 15
years and it is not all the great in my opinion. Range partitioning
certainly does not work well for share-nothing parallel operations. But
since both DB2 for z/OS and Oracle only support share-everything
parallelism, that is what they both have range partitioning instead of hash
partitioning (hash partitioning is designed to split the load evenly across
all partitions).
This has many benefits from a DBA standpoint
where partitions have historical data partitioned by date, because once the date has closed, the table can be backed up without continuously backing up a much larger partitioned table as new transactions are added to the same table in Oracle.


There is an implication here that DB2's UNION ALL views better support
rolling window operations than Oracle's Range Partitions. They are not.
A major design point of Range Partitions when they were provided in
Oracle8 was to add better support for rolling window operations than
what could be done for UNION ALL tables. You can, of course, back up 1
or as many partitions in a range partitioned table as you want to.
That's sort of the point.

Having actually worked on such applications, I can say that it can be done
quite well with DB2 UNION ALL views. If Oracle is slightly better in that
respect with range partitioning, then fine. I don't think it is deal
breaker. The use of range partitioning comes at a big cost, especially when
trying to balance a load across multiple partitions for true parallel
operations that are scalable. With range partitioning, one gets a lot of hot
spots on a particular partition (which is usually the most current monthly
or yearly data).

The admin cost of adding new tables is about the same as adding new
partitions for each month (in an Oracle range partitioning scenario). This can be done manually or automatically via application software. One example of an application that uses multiple tables for time periods instead of
range partitioning is a check archive system by Viewpointe Archive Services: http://www.viewpointearchive.com/default.aspx?pageid=5

Hmm - seems to require a LOT of help from IBM to make it work however -


In addition to the fact that the Viewpointe system check image system uses
DB2 EEE version 7 with multiple partitions as their database, Viewpointe has
also contracted with IBM to provide complete datacenter outsourcing
services. This is not required to make DB2 work, but complete data center
outsourcing is just another business that IBM happens to be involved in.

The good news is that a very large chuck of the check written in the US are
stored on that system for many of the largest banks in the US, so some of
your checks may be on there also. Maybe even some Oracle corporate checks.
When adding over 80 million checks every business day to the database,
fortunately they are using a robust and scalable database architecture.
Nov 12 '05 #49
Thanks very much everyone. This has been very helpful to us. We have
realized very quickly that we were going down the wrong path in trying
to figure out the right database for our customers upfront. Our client
apps work with the databases via genreic dbms connectivity apis and
sql and, we can easily allow our apps to pick either one of the
datasources (don't mind the extra development effort on the oracle and
db2 sql).
The ultimate choice of the DBMS platform will perhaps be best made by
our customers (who will have a much better understanding of their dbms
TCO based on existing infrastructure).

~Sy
Nov 12 '05 #50

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Mica Cooper | last post: by
6 posts views Thread by apngss | last post: by
6 posts views Thread by Kennedy_f | last post: by
9 posts views Thread by Rhino | last post: by
2 posts views Thread by Tedros.G | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.