partioned table or partioned index

[Sumanth]

Hi,

I have a table that I would like to partition. It has a column c1 which has
100 distinct values.
I was planning to partition the table on column c1 using a partioned index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour as
above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of the
tables I am dealing with
are really huge?

Any thoughts and pointers are appreaciate.

Thanks,
Sumanth

[Serge Rielau]

Sumanth wrote:

Hi,

I have a table that I would like to partition. It has a column c1 which has
100 distinct values.
I was planning to partition the table on column c1 using a partioned index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour as
above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of the
tables I am dealing with
are really huge?

Sumath, what platform are you on?

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

[Sumanth]

read thru some docs and I believe that going with a partioned table space
with a partition by clause on C1 would
work. I then can define a data partioned secondary index that would be valid
on each of the partitions.

What is an index that is created as a partioned cluster?

Thanks,
Sumanth

"Sumanth" <Su************@sas.com> wrote in message
news:e1**********@foggy.unx.sas.com...

Hi,

I have a table that I would like to partition. It has a column c1 which
has 100 distinct values.
I was planning to partition the table on column c1 using a partioned
index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour
as above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of the
tables I am dealing with
are really huge?

Any thoughts and pointers are appreaciate.

Thanks,
Sumanth

[Mark A]

"Sumanth" <Su************@sas.com> wrote in message
news:e1**********@foggy.unx.sas.com...

Hi,

I have a table that I would like to partition. It has a column c1 which
has 100 distinct values.
I was planning to partition the table on column c1 using a partioned
index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour
as above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of the
tables I am dealing with
are really huge?

Any thoughts and pointers are appreaciate.

Thanks,
Sumanth

Partitioned tablespaces are only for DB2 for z/OS and OS/390. If you are
using that platform, please state that in your response.

[Sumanth]

I am on AIX.

-Sumanth

"Mark A" <no****@nowhere.com> wrote in message
news:c9********************@comcast.com...

"Sumanth" <Su************@sas.com> wrote in message
news:e1**********@foggy.unx.sas.com...

Hi,

I have a table that I would like to partition. It has a column c1 which
has 100 distinct values.
I was planning to partition the table on column c1 using a partioned
index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour
as above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of
the tables I am dealing with
are really huge?

Any thoughts and pointers are appreaciate.

Thanks,
Sumanth

Partitioned tablespaces are only for DB2 for z/OS and OS/390. If you are
using that platform, please state that in your response.

[Sumanth]

So on AIX what are my options. If I have a table that is big (close to say
200 GB) what are my options in partitioning the table.

Thanks,
Sumanth

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:4a************@individual.net...

Sumanth wrote:

Hi,

I have a table that I would like to partition. It has a column c1 which
has 100 distinct values.
I was planning to partition the table on column c1 using a partioned
index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour
as above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of
the tables I am dealing with
are really huge?

Sumath, what platform are you on?

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

[Sumanth]

Can i create separate tables for each value of c1 and use a check constraint
on each of these tables.
And create a view that runs across all these tables (100 of them)
Will the access path of the queries be efficient.?

Any other options?

Thanks,
Sumanth
"Sumanth" <Su************@sas.com> wrote in message
news:e1**********@foggy.unx.sas.com...

Hi,

I have a table that I would like to partition. It has a column c1 which
has 100 distinct values.
I was planning to partition the table on column c1 using a partioned
index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour
as above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of the
tables I am dealing with
are really huge?

Any thoughts and pointers are appreaciate.

Thanks,
Sumanth

[Serge Rielau]

Sumanth wrote:

So on AIX what are my options. If I have a table that is big (close to say
200 GB) what are my options in partitioning the table.

On DB2 V8.2 for AIX you can use
* nothing special and a 16KB or 32KB pagesize.
* Multi-dimensional clustering (MDC) with at 16KB or 32KB pagesize
The advantage of MDC is that it's one table. You get benefits on rollout
(faster deletes of a "partition") and you get fast scans.
MDC is avlaible without extra cost on licence.
* Hash partition the table across multiple database partitions
The advantage of this is scale out across multiple physical machines (if
you choose so).
It requires the Database partitioning feature (significant extra $$/CPU)
and you need to think about the design of your database beyond just this
one table (fnding partitioning keys, etc...).
I would not recommend DPF in an OLTP system just because a single table
is big. DPF works, and works well in OLTP, but it requires careful
layout of your schema.

* UNION ALL views
The advantage can be fast roll-in/roll-out and partition elimination at
runtime as well as compile-ttime.
DB2 is reasonably good about keeping the optimizer plans in check.
Today DB2 supports in access of 100 branches of UNION ALL. I think (!)
you need to run with optimization level 7 to get all the goodies.
In DB2 Viper (ETA pre-announced for "this summer")
You have two more options:
1. DB2 supports more rows per page and more pages per tablespace.
This means you can have a single table of, uh, 16TB and 1.2 trillion
rows if you so desire.
I.e. a simple 4KB pagesize table will do just fine
2. range partitioning
This is the replacement for UNION ALL views. Thousands of partitions are
supported along with special ATTACH and DETACH DDL statements for fast
roll-in, roll-out.

In DB2 Viper we do not recommend UNION ALL views anymore for partitioning.

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

[Sumanth]

Thanks Serge.
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:4a************@individual.net...

Sumanth wrote:

So on AIX what are my options. If I have a table that is big (close to
say 200 GB) what are my options in partitioning the table.

On DB2 V8.2 for AIX you can use
* nothing special and a 16KB or 32KB pagesize.
* Multi-dimensional clustering (MDC) with at 16KB or 32KB pagesize
The advantage of MDC is that it's one table. You get benefits on rollout
(faster deletes of a "partition") and you get fast scans.
MDC is avlaible without extra cost on licence.
* Hash partition the table across multiple database partitions
The advantage of this is scale out across multiple physical machines (if
you choose so).
It requires the Database partitioning feature (significant extra $$/CPU)
and you need to think about the design of your database beyond just this
one table (fnding partitioning keys, etc...).
I would not recommend DPF in an OLTP system just because a single table is
big. DPF works, and works well in OLTP, but it requires careful layout of
your schema.

* UNION ALL views
The advantage can be fast roll-in/roll-out and partition elimination at
runtime as well as compile-ttime.
DB2 is reasonably good about keeping the optimizer plans in check.
Today DB2 supports in access of 100 branches of UNION ALL. I think (!) you
need to run with optimization level 7 to get all the goodies.
In DB2 Viper (ETA pre-announced for "this summer")
You have two more options:
1. DB2 supports more rows per page and more pages per tablespace.
This means you can have a single table of, uh, 16TB and 1.2 trillion rows
if you so desire.
I.e. a simple 4KB pagesize table will do just fine
2. range partitioning
This is the replacement for UNION ALL views. Thousands of partitions are
supported along with special ATTACH and DETACH DDL statements for fast
roll-in, roll-out.

In DB2 Viper we do not recommend UNION ALL views anymore for partitioning.

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

[Ian]

Serge Rielau wrote:

In DB2 Viper we do not recommend UNION ALL views anymore for partitioning.

That's funny - aren't there still people on the mainframe recommending
UNION ALL in certain situations? (but that may be related to issues
with rebuild of global indexes).

And does this mean that the optimizer's "union all" smarts will be
removed?

Thanks -

Ian

[Serge Rielau]

Ian wrote:

Serge Rielau wrote:

In DB2 Viper we do not recommend UNION ALL views anymore for
partitioning.
That's funny - aren't there still people on the mainframe recommending
UNION ALL in certain situations? (but that may be related to issues
with rebuild of global indexes).

Nothing is ever black and white. You are correct, range partitioned
tables use global indexes. However some of the downsides of global
indexes as they may exist on DB2 for zOS have been overcome (e.g.
asynchronous index cleanup after a roll-out).
Also if you have existing tables which do not have identical signatures
then UNION ALL views will still be valuable.
Lastly I see UNION ALL views as a valuable asset for information
integration (although DB2 today does not support check constraints on
nicknames....)
And does this mean that the optimizer's "union all" smarts will be
removed?

Absolutely not! But I doubt we'll invest into further improvements.
E.g. if a customer requested an ALTER VIEW ADD BRANCH (I'm making up
syntax here) there would be a questioning whether they are looking at
the right tool for the job.

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