Questions about some DB2 concepts.

[Amber]

Hello, I am new to DB2, and I found myself get lost among the new
concepts, such as non-partitioned database/partitioned
database/database partition/partition group/node ,ect. Would you
explain to me the concepts(using db2 8.2 terms), and the relationship
among them. Some helpful resource links are also welcome. Thranks in
advance.

[Serge Rielau]

Amber wrote:

Hello, I am new to DB2, and I found myself get lost among the new
concepts, such as non-partitioned database/partitioned
database/database partition/partition group/node ,ect. Would you
explain to me the concepts(using db2 8.2 terms), and the relationship
among them. Some helpful resource links are also welcome. Thranks in
advance.

Amber,

A non-partitioned database is your run-of the mill databases.
That is there is an instance of DB2 installed on a computer and a
database residing on that same computer.
In a partitioned database multiple instances of DB2 each own pieces of
a databases potentially spread across multiple computers.
A database partition group is a (sub) group of such database partitions.
When you create tablespace you can partition it's content over any
number of partitions in the environment. database partition groups are
used to label such a collection. The most common groups are
single-partition groups (typically the coordinator node - that is a node
that you connect to) or a data-partition group (all partition which hold
data (excluding a dedicated catalog partition and perhaps dedicated
coordinator partitions)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Amber]

Serge Rielau

That's very kind of you. How about instances? Can two instances on
the same physical machine hold individual partitions of a partitioned
database? And if InstanceA holds one partition of a partitioned
database DatabaseA, can it hold another non-partitioned database
DatabaseB, or another partition of another partitioned database
DatabaseC?
And there are some client connection considerations. If InstanceA
on physical machine ServerA holds partition1 of partitioned database
DatabaseP, InstanceB on physical machine ServerB holds partition2 of
DatabaseP, InstanceC on physical machine ServerC holds partition3 of
DatabaseP ... , then if I only catalog DatabaseP at InstanceA on a
client workstation, can I get any content of the entire database. And
whether catalog any individual instance I can get the same result?

[Serge Rielau]

Amber wrote:

Serge Rielau

That's very kind of you. How about instances? Can two instances on
the same physical machine hold individual partitions of a partitioned
database? And if InstanceA holds one partition of a partitioned
database DatabaseA, can it hold another non-partitioned database
DatabaseB, or another partition of another partitioned database
DatabaseC?

I'm a language guy. My topology knowledge is limited. Perhaps Mark A.
can go into more detail or correct me.
You definitely can have more than one partition on the same machine and
even in the same instance. That is called "logical partitioning" and it
is fairly common to have e.g. 10 SMP boxes running a 100 partition
database. You may want to google for a DB2 BCU (Balanced configuration
unit) for recommended topologies.
In general the correlation between an instance (a copy of DB2) and a
database is loose. An instance can be associated with any number of
databases and for small databases that is often the case.
For ease of administration with sizable databases IBM recommends a
one-one mapping.

And there are some client connection considerations. If InstanceA
on physical machine ServerA holds partition1 of partitioned database
DatabaseP, InstanceB on physical machine ServerB holds partition2 of
DatabaseP, InstanceC on physical machine ServerC holds partition3 of
DatabaseP ... , then if I only catalog DatabaseP at InstanceA on a
client workstation, can I get any content of the entire database. And
whether catalog any individual instance I can get the same result?

Correct. Form an application point of view there is only one database.
We are in process of extending that model to administration as well.
The grade of transparency depends w.r.t backup/restore/load depends on
your version.

I think that's as far as I can go without spreading false information
due to ignorance :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Amber]

>The most common groups are single-partition groups (typically the coordinator >node - that is a node that you connect to) or a data-partition group (all partition >which hold data (excluding a dedicated catalog partition and perhaps dedicated

>coordinator partitions)

The last question:-) what's the coordinator node, the one who
creates the first partition? Do you mean clients should catalog the
coordinator node, but not other nodes in the group. I originally think
clients can catalog any node in the group, and the results are the
same.

[Serge Rielau]

Amber wrote:

>The most common groups are single-partition groups (typically the coordinator >node - that is a node that you connect to) or a data-partition group (all partition >which hold data (excluding a dedicated catalog partition and perhaps dedicated
coordinator partitions)

The last question:-) what's the coordinator node, the one who
creates the first partition? Do you mean clients should catalog the
coordinator node, but not other nodes in the group. I originally think
clients can catalog any node in the group, and the results are the
same.

The coordinator partition is the partition that you connect the
application to.
It's job is to compile the query start it and collect the results.
Since there may be several connections doing a lot of compiles (in a
warehouse often parameter markers are not used to get the best plan)
this puts strain on the system, so the coordinator partition will only
store temporary data or small tables.
Another special partition (which may be identical to the coordinator) is
the catalog partition. It holds the db schema information.
Again the BCU topology holds best practices.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Ian]

Amber wrote:

Serge Rielau

That's very kind of you. How about instances? Can two instances on
the same physical machine hold individual partitions of a partitioned
database? And if InstanceA holds one partition of a partitioned
database DatabaseA, can it hold another non-partitioned database
DatabaseB, or another partition of another partitioned database
DatabaseC?

There's a great picture of the relationships between instance,database,
etc. in the docs, but i couldn't find it quickly, so:

Here's the structure:

1 Instance has 1..999 partitions.
Each partition may exist on a separate physical machine; however,
you can have multiple partitions on a single machine.

A non-partitioned instance is (effectively) an instance with
only 1 partition.

A database is defined within an instance, and is spread across
*all* of the partitions that make up the instance. Yes, all.

Each instance may have multiple databases.

Inside a database, you have database partition groups (aka
nodegroups), which define logical groups of database partitions.

Tablespaces are defined in a database partition group. Since a
database partition group does not need to include all partitions,
a tablespace can therefore be defined on a subset of the partitions.

Tables are defined in a tablespace.

And there are some client connection considerations. If InstanceA
on physical machine ServerA holds partition1 of partitioned database
DatabaseP, InstanceB on physical machine ServerB holds partition2 of
DatabaseP, InstanceC on physical machine ServerC holds partition3 of
DatabaseP ... , then if I only catalog DatabaseP at InstanceA on a
client workstation, can I get any content of the entire database. And
whether catalog any individual instance I can get the same result?

Your terminology is wrong, but I get what you're asking:

Yes, you can connect to any partition participating in an instance in a
database and access ALL data. From a user perspective, a database spead
over 999 partitions doesn't look any different from a database using
only 1 partition.
Good luck,
Ian

[Amber]

lan

Thanks for your reply. So partition is instance's partition, but
not databases'. And if a instance is partitioned, all databases it
holds are partitioned, and that's why the physical struct is database
directories insiding instance directories?

[Ian]

Amber wrote:

lan

Thanks for your reply. So partition is instance's partition, but
not databases'. And if a instance is partitioned, all databases it
holds are partitioned, and that's why the physical struct is database
directories insiding instance directories?

You got it.
Good luck,
Ian