Partitioning ROT

K T

Partitioning ROT
Hi All,

I would like to know some ROT's for partitioning. While a partitioning
column need to be one that provides a good partitioning is this the only
criteria to be used ?, I have a table created that is partitioned on a
column that gives it a good distribution of data across its partitions.
Its is neither a unique column nor one that is used for clustering .
Also its not a frequently used column either. The NPI defined on it is a
unique and clustering index and is the one that is used frequently. My
question is will making this unique column as a partitioning column help
its I/O. I am getting a decent partitioning of data if using this
column. Any pointers will be helpful.



Rgds



KT




-----------------------------------------
Use of email is inherently insecure. Confidential information,
including account information, and personally identifiable information,
should not be transmitted via email, or email attachment. In no event
shall Citizens or any of its affiliates accept any responsibility for
the loss, use or misuse of any information including confidential
information, which is sent to Citizens or its affiliates via email, or
email attachment. Citizens does not guarantee the accuracy of any email
or email attachment, that an email will be received by Citizens or that
Citizens will respond to any email.

This email message is confidential and/or privileged. It is to be used
by the intended recipient only. Use of the information contained in
this email by anyone other than the intended recipient is strictly
prohibited. If you have received this message in error, please notify
the sender immediately and promptly destroy any record of this email.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: Partitioning ROT
(in response to K T)
My personal favorites would be
1. Choose a partitioning key that will randomly place any insert across the expanse of partitions. This will give better space usage, I/O performance, Parallelism potential etc.

2. Never make up an artificial key for the high level part of a partitioning index. PART-NUMBER or the like tend to very popular for a reason that has never been clear to me.

Of course my statements are
My own opinions
May not apply to your case
May not be universally accepted
and of course
No Warranties, expressed, implied, or even hinted at.


"Trivedi, Kaushal" <[login to unmask email]> wrote:
Hi All,
I would like to know some ROT’s for partitioning. While a partitioning column need to be one that provides a good partitioning is this the only criteria to be used ?, I have a table created that is partitioned on a column that gives it a good distribution of data across its partitions. Its is neither a unique column nor one that is used for clustering . Also its not a frequently used column either. The NPI defined on it is a unique and clustering index and is the one that is used frequently. My question is will making this unique column as a partitioning column help its I/O. I am getting a decent partitioning of data if using this column. Any pointers will be helpful.

Rgds

KT


---------------------------------

Use of email is inherently insecure. Confidential information,
including account information, and personally identifiable information,
should not be transmitted via email, or email attachment. In no event
shall Citizens or any of its affiliates accept any responsibility for
the loss, use or misuse of any information including confidential
information, which is sent to Citizens or its affiliates via email, or
email attachment. Citizens does not guarantee the accuracy of any email
or email attachment, that an email will be received by Citizens or that
Citizens will respond to any email.

This email message is confidential and/or privileged. It is to be used
by the intended recipient only. Use of the information contained in
this email by anyone other than the intended recipient is strictly
prohibited. If you have received this message in error, please notify
the sender immediately and promptly destroy any record of this email.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm




Avram Friedman
(877)311-0480 Voice Mail
[login to unmask email]
Http://www.IBMsysProg.com




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: Partitioning ROT
(in response to Avram Friedman)
Avram, we have successfully and routinely violated both of these rules at my
place of work.

I particularly caution the unwary that *a random partitioning key will lead
to random inserts, forcing synchronous reads and heavy I/O activity during
insert and subsequent access.* High-volume INSERTs to large tables should
never be random if it can be avoided, as the likelihood of the desired page
being in the bufferpool is correspondingly small. This is a good way to
learn about "Death by Random I/O."

In DB2V7 and earlier, where it is impossible to "rotate" aged partitions (in
a partitioning scheme based on time or some substitute), artificial
partition numbers make sense. Ditto for a system where all of the
attributes on some (denormalized) table can change but still be related to
the "parent" table.

For large tablespaces with high volumes of inserted data which ages off over
time (a common problem in our business, don't know about others'), we have
found it efficient to partition/cluster based on date. We then add other
columns to the cluster key as may be useful. This also minimizes the length
of RID chains (for the sake of efficiency in retrieval and DELETEs).

We then LOAD the appropriate partition with the latest data, purge the data
from the oldest partitions (usually with a LOAD calling on a SYSREC DD DUMMY
in the JCL). When possible, we avoid NPIs with these large tablespaces, but
if necessary, we try to keep them similar in column order to the
partitioning key, so as to minimize random inserts during the LOAD and
random deletes during the Purge.

--Phil

On 1/4/06, Avram Friedman <[login to unmask email]> wrote:
>
> My personal favorites would be
> 1. Choose a partitioning key that will randomly place any insert across
> the expanse of partitions. This will give better space usage, I/O
> performance, Parallelism potential etc.
>
> 2. Never make up an artificial key for the high level part of a
> partitioning index. PART-NUMBER or the like tend to very popular for a
> reason that has never been clear to me.
>
> Of course my statements are
> My own opinions
> May not apply to your case
> May not be universally accepted
> and of course
> No Warranties, expressed, implied, or even hinted at.
>
>
> *"Trivedi, Kaushal" <[login to unmask email]>* wrote:
>
> Hi All,
> I would like to know some ROT's for partitioning. While a partitioning
> column need to be one that provides a good partitioning is this the only
> criteria to be used ?, I have a table created that is partitioned on a
> column that gives it a good distribution of data across its partitions. Its
> is neither a unique column nor one that is used for clustering . Also its
> not a frequently used column either. The NPI defined on it is a unique and
> clustering index and is the one that is used frequently. My question is will
> making this unique column as a partitioning column help its I/O. I am
> getting a decent partitioning of data if using this column. Any pointers
> will be helpful.
>
> Rgds
>
> KT
>
>


--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: Partitioning ROT
(in response to Philip Sevetson)
I should add that we consider ourselves to be processor-constrained. IBM
mainframes consist of a relatively small number of powerful processors,
multitasking, and we're out to get the most bang for every buck we spend on
'em, juggling datawarehousing and OLTP tasks in fenced environments.

If you have:
1) A large number of not very powerful processors, which you can dedicate
to the task of supporting this table:
2) plenty of memory
Then Avram's rules about randomizing inserts work just fine, taking
advantage of the cheapness of buying more processors.

However, in a z/OS environment, you generally can't afford to clog up your
I/O channels with massive amounts of I/O, and you generally don't have 10
milliseconds times a billion rows available to get your response. So don't
use random I/O.

--Phil

On 1/6/06, Philip Sevetson <[login to unmask email]> wrote:
>
> Avram, we have successfully and routinely violated both of these rules at
> my place of work.
>
> I particularly caution the unwary that *a random partitioning key will
> lead to random inserts, forcing synchronous reads and heavy I/O activity
> during insert and subsequent access.* High-volume INSERTs to large tables
> should never be random if it can be avoided, as the likelihood of the
> desired page being in the bufferpool is correspondingly small. This is a
> good way to learn about "Death by Random I/O."
>
>

[snip the rest of the post and predecessors]

--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: Partitioning ROT
(in response to Philip Sevetson)
Hi Phil,
Most shops I have been associated with don't follow my rules either.
I would like to stress the fact that I am not a DBA. I am a systems programmer who kind of remembers how to wire unit record equipment plug panels.

However I do strongly believe
The basic activity of data processing is processing data. Any scheme that proposes to eliminate the processing of data or is intolerant to the processing of data is fundamentally flawed and subject to eventual failure.

My suggested rule of thumb of wide distribution (randomizing) I/O operations is intended to maximize the ability of processing the data via
Query Parallelism
Concurrent threads (jobs, transactions, and etc)

It is true that I/O bottle necks do exist. Part of the reason is historical CPU and memory functionality grew at a much faster rate that of I/O. I was always surprised by early PC performance studies that showed a 10% improvement in processor power resulted in an order of magnitude improvement in throughput. The real functional improvement was better / faster I/O!

If one is building a system for the 21st century it should not be constructed on the basis 0f 1960 - 1980 I/O configurations. When DB2 was introduced the early people explained the increased cost and reduced performance by pointing out the applications were converted from older systems and not redesigned to exploit DB2 ideas like result set processing.

Rule of thumb for some one building a very large table today? Build it to exploit today's technology or even the technology that can be reasonably expected 5 to 10 years from now.

Hence my suggestion to distribute the I/O it maximizes the I/O (Data Processing) potential.

One of the enhancements in DB2 for Z/os V8 is longer names. Come to think of it this is also one of the enhancements in V7, V6, V5 etc. What is wrong with good old classic 8 character names? or 16? Readability, verifiability, maintainability etc. I don't know how far back this lesson goes, It was one of the justifications for the development of COBOL, but I think it actually predates that historical event. The requirement for long names is not because the planet is running out of short names but rather the difficulty in providing meaningfull short artificial names. Is it really a good long term future idea to base the primary partitioning key of a very large table on some artificial non self documenting field? This is the reason for my second suggestion of avoiding fake primary keys like PARTITION-NUM.

A couple other suggested, progressive partitioning rules of thumb that come to mind are
3. Avoid too many indexes on big tables, index maintenance is often a pain.
4. Plan for all data activities
Retrieval
Insert
Delete
Update / Change
Backup
Recovery
Reorg
Purge / Archive
Re-Architecture

I hope I have been clear that I am not proposing rules that work in all cases or even rules that are generally accepted. What I am proposing is an opinion or suggestion of design for a new 21st century large database table application.

I am also grateful to you, Phil, for providing practical experience to guide the list member who originated this thread. There is no magic bullet ROT for most issues the truth lies in learning to fish, taking an idea evaluating, experimenting, and possibly implementing it. Given the many changes to partitioning support in V8 I sort of expected a lively discussion dealing with the original posters question.

Philip Sevetson <[login to unmask email]> wrote:
Avram, we have successfully and routinely violated both of these rules at my place of work.

I particularly caution the unwary that a random partitioning key will lead to random inserts, forcing synchronous reads and heavy I/O activity during insert and subsequent access. High-volume INSERTs to large tables should never be random if it can be avoided, as the likelihood of the desired page being in the bufferpool is correspondingly small. This is a good way to learn about "Death by Random I/O."

In DB2V7 and earlier, where it is impossible to "rotate" aged partitions (in a partitioning scheme based on time or some substitute), artificial partition numbers make sense. Ditto for a system where all of the attributes on some (denormalized) table can change but still be related to the "parent" table.

For large tablespaces with high volumes of inserted data which ages off over time (a common problem in our business, don't know about others'), we have found it efficient to partition/cluster based on date. We then add other columns to the cluster key as may be useful. This also minimizes the length of RID chains (for the sake of efficiency in retrieval and DELETEs).

We then LOAD the appropriate partition with the latest data, purge the data from the oldest partitions (usually with a LOAD calling on a SYSREC DD DUMMY in the JCL). When possible, we avoid NPIs with these large tablespaces, but if necessary, we try to keep them similar in column order to the partitioning key, so as to minimize random inserts during the LOAD and random deletes during the Purge.

--Phil

On 1/4/06, Avram Friedman <[login to unmask email]> wrote: My personal favorites would be
1. Choose a partitioning key that will randomly place any insert across the expanse of partitions. This will give better space usage, I/O performance, Parallelism potential etc.

2. Never make up an artificial key for the high level part of a partitioning index. PART-NUMBER or the like tend to very popular for a reason that has never been clear to me.

Of course my statements are
My own opinions
May not apply to your case
May not be universally accepted
and of course
No Warranties, expressed, implied, or even hinted at.


"Trivedi, Kaushal" <[login to unmask email]> wrote:
Hi All,
I would like to know some ROT's for partitioning. While a partitioning column need to be one that provides a good partitioning is this the only criteria to be used ?, I have a table created that is partitioned on a column that gives it a good distribution of data across its partitions. Its is neither a unique column nor one that is used for clustering . Also its not a frequently used column either. The NPI defined on it is a unique and clustering index and is the one that is used frequently. My question is will making this unique column as a partitioning column help its I/O. I am getting a decent partitioning of data if using this column. Any pointers will be helpful.

Rgds

KT






--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S. --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



Avram Friedman
(877)311-0480 Voice Mail
[login to unmask email]
Http://www.IBMsysProg.com




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: Partitioning ROT
(in response to Avram Friedman)
Avram,

I can definitely get behind your Rule 3 and Rule 4, of course, good common
sense that most people learn after getting surprised and bruised two or
three times!

I know randomness supports parallelism, but I have to tell you that it's
been our experience that a good index beats parallelism every time in our
biggest retrievals, just because of the great speeds achieved by buffered
input. Again, this is for a processor-constrained,
memory-constrained environment. If you've got a lot of cheap processors and
gigabytes on gigabytes of secondary cache, go for parallelism by all means.

I should also make clear that I'm principally talking about retrievals
against multi-terabyte fact tables here when I tout the superiority of
indexing. For tables that only measure in the tens of gigabytes, as long as
you can achieve a single pass through the data (e.g. a prejoin or aggregate
table), parallelism is quite acceptable and in some cases superior.

Finally, if your tables are small (ROT: <100MB down to <10MB), you can size
your bufferpools to encompass the active data and get what Larry Kintisch
(and Tapio Lahdenmaki, an industry expert on index design) refer(s) to as
"cheap random touches" for your access.

--Phil

P.S. Frankly, I'm surprised more people haven't jumped into this thread.
This is one of the more interesting topics in modern database performance.
Where is everybody?

On 1/6/06, Avram Friedman <[login to unmask email]> wrote:
>
> Hi Phil,
> Most shops I have been associated with don't follow my rules either.
> I would like to stress the fact that I am not a DBA. I am a systems
> programmer who kind of remembers how to wire unit record equipment plug
> panels.
>
> However I do strongly believe
> The basic activity of data processing is processing data. Any scheme that
> proposes to eliminate the processing of data or is intolerant to the
> processing of data is fundamentally flawed and subject to eventual failure.
>
> My suggested rule of thumb of wide distribution (randomizing) I/O
> operations is intended to maximize the ability of processing the data via
> Query Parallelism
> Concurrent threads (jobs, transactions, and etc)
>
> It is true that I/O bottle necks do exist. Part of the reason is
> historical CPU and memory functionality grew at a much faster rate that of
> I/O. I was always surprised by early PC performance studies that showed a
> 10% improvement in processor power resulted in an order of magnitude
> improvement in throughput. The real functional improvement was better /
> faster I/O!
>
> If one is building a system for the 21st century it should not be
> constructed on the basis 0f 1960 - 1980 I/O configurations. When DB2 was
> introduced the early people explained the increased cost and reduced
> performance by pointing out the applications were converted from older
> systems and not redesigned to exploit DB2 ideas like result set processing.
>
> Rule of thumb for some one building a very large table today? Build it to
> exploit today's technology or even the technology that can be reasonably
> expected 5 to 10 years from now.
>
> Hence my suggestion to distribute the I/O it maximizes the I/O (Data
> Processing) potential.
>
> One of the enhancements in DB2 for Z/os V8 is longer names. Come to think
> of it this is also one of the enhancements in V7, V6, V5 etc. What is wrong
> with good old classic 8 character names? or 16? Readability, verifiability,
> maintainability etc. I don't know how far back this lesson goes, It was
> one of the justifications for the development of COBOL, but I think it
> actually predates that historical event. The requirement for long names is
> not because the planet is running out of short names but rather the
> difficulty in providing meaningfull short artificial names. Is it really a
> good long term future idea to base the primary partitioning key of a very
> large table on some artificial non self documenting field? This is the
> reason for my second suggestion of avoiding fake primary keys like
> PARTITION-NUM.
>
> A couple other suggested, progressive partitioning rules of thumb that
> come to mind are
> 3. Avoid too many indexes on big tables, index maintenance is often a
> pain.
> 4. Plan for all data activities
> Retrieval
> Insert
> Delete
> Update / Change
> Backup
> Recovery
> Reorg
> Purge / Archive
> Re-Architecture
>
> I hope I have been clear that I am not proposing rules that work in all
> cases or even rules that are generally accepted. What I am proposing is an
> opinion or suggestion of design for a new 21st century large database table
> application.
>
> I am also grateful to you, Phil, for providing practical experience to
> guide the list member who originated this thread. There is no magic bullet
> ROT for most issues the truth lies in learning to fish, taking an
> idea evaluating, experimenting, and possibly implementing it. Given the
> many changes to partitioning support in V8 I sort of expected a lively
> discussion dealing with the original posters question.
>



--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Partitioning ROT
(in response to Philip Sevetson)
How about this one:

"If you even THINK you might have a table that, one day, will grow big
enough to be partitioned, create it today with ONE partition". The
reason being, you can always add MORE partitions, but converting from
non-partitioned to partitioned is still a pain (and promises to continue
to be a pain for some time)

Phil Grainger
CA
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Philip Sevetson
Sent: 10 January 2006 17:06
To: [login to unmask email]
Subject: Re: [DB2-L] Partitioning ROT


Avram,

I can definitely get behind your Rule 3 and Rule 4, of course, good
common sense that most people learn after getting surprised and bruised
two or three times!

I know randomness supports parallelism, but I have to tell you that it's
been our experience that a good index beats parallelism every time in
our biggest retrievals, just because of the great speeds achieved by
buffered input. Again, this is for a processor-constrained,
memory-constrained environment. If you've got a lot of cheap processors
and gigabytes on gigabytes of secondary cache, go for parallelism by all
means.

I should also make clear that I'm principally talking about retrievals
against multi-terabyte fact tables here when I tout the superiority of
indexing. For tables that only measure in the tens of gigabytes, as
long as you can achieve a single pass through the data ( e.g. a prejoin
or aggregate table), parallelism is quite acceptable and in some cases
superior.

Finally, if your tables are small (ROT: <100MB down to <10MB), you can
size your bufferpools to encompass the active data and get what Larry
Kintisch (and Tapio Lahdenmaki, an industry expert on index design)
refer(s) to as "cheap random touches" for your access.

--Phil

P.S. Frankly, I'm surprised more people haven't jumped into this thread.
This is one of the more interesting topics in modern database
performance. Where is everybody?

On 1/6/06, Avram Friedman <[login to unmask email]> wrote:

Hi Phil,
Most shops I have been associated with don't follow my rules
either.
I would like to stress the fact that I am not a DBA. I am a
systems programmer who kind of remembers how to wire unit record
equipment plug panels.

However I do strongly believe
The basic activity of data processing is processing data. Any
scheme that proposes to eliminate the processing of data or is
intolerant to the processing of data is fundamentally flawed and subject
to eventual failure.

My suggested rule of thumb of wide distribution (randomizing)
I/O operations is intended to maximize the ability of processing the
data via
Query Parallelism
Concurrent threads (jobs, transactions, and etc)

It is true that I/O bottle necks do exist. Part of the reason
is historical CPU and memory functionality grew at a much faster rate
that of I/O. I was always surprised by early PC performance studies
that showed a 10% improvement in processor power resulted in an order of
magnitude improvement in throughput. The real functional improvement
was better / faster I/O!

If one is building a system for the 21st century it should not
be constructed on the basis 0f 1960 - 1980 I/O configurations. When
DB2 was introduced the early people explained the increased cost and
reduced performance by pointing out the applications were converted from
older systems and not redesigned to exploit DB2 ideas like result set
processing.

Rule of thumb for some one building a very large table today?
Build it to exploit today's technology or even the technology that can
be reasonably expected 5 to 10 years from now.

Hence my suggestion to distribute the I/O it maximizes the I/O
(Data Processing) potential.

One of the enhancements in DB2 for Z/os V8 is longer names.
Come to think of it this is also one of the enhancements in V7, V6, V5
etc. What is wrong with good old classic 8 character names? or 16?
Readability, verifiability, maintainability etc. I don't know how far
back this lesson goes, It was one of the justifications for the
development of COBOL, but I think it actually predates that historical
event. The requirement for long names is not because the planet is
running out of short names but rather the difficulty in providing
meaningfull short artificial names. Is it really a good long term
future idea to base the primary partitioning key of a very large table
on some artificial non self documenting field? This is the reason for
my second suggestion of avoiding fake primary keys like PARTITION-NUM.

A couple other suggested, progressive partitioning rules of
thumb that come to mind are
3. Avoid too many indexes on big tables, index maintenance is
often a pain.
4. Plan for all data activities
Retrieval
Insert
Delete
Update / Change
Backup
Recovery
Reorg
Purge / Archive
Re-Architecture

I hope I have been clear that I am not proposing rules that work
in all cases or even rules that are generally accepted. What I am
proposing is an opinion or suggestion of design for a new 21st century
large database table application.

I am also grateful to you, Phil, for providing practical
experience to guide the list member who originated this thread. There
is no magic bullet ROT for most issues the truth lies in learning to
fish, taking an idea evaluating, experimenting, and possibly
implementing it. Given the many changes to partitioning support in V8 I
sort of expected a lively discussion dealing with the original posters
question.




--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for
zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: Partitioning ROT
(in response to Phil Grainger)
I like that one -- Phil, is there anything you have to specify in DB2V7 in
order to be eligible to recluster and/or add partitions in V8 (DSSIZE?)? Or
are all partitioned tablespaces treated equally under the new rules?

--Phil


On 1/10/06, Grainger, Phil <[login to unmask email]> wrote:
>
> How about this one:
>
> "If you even THINK you might have a table that, one day, will grow big
> enough to be partitioned, create it today with ONE partition". The reason
> being, you can always add MORE partitions, but converting from
> non-partitioned to partitioned is still a pain (and promises to continue to
> be a pain for some time)
>
> Phil Grainger
>

--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steen Rasmussen

Re: Partitioning ROT
(in response to Philip Sevetson)
Phil Severtson - as long as your tablespace is PARTITIONED, you are fine
to exploit any of the great V8 features. Depending on LARGE being
present etc. , you might have the same issues as in V7, that some
partitions might end in REORP due to the enforcement of LIMITKEY etc.



Kind regards
Steen Rasmussen
CA
Senior Consultant

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Philip Sevetson
Sent: Tuesday, January 10, 2006 11:35 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Partitioning ROT



I like that one -- Phil, is there anything you have to specify in DB2V7
in order to be eligible to recluster and/or add partitions in V8
(DSSIZE?)? Or are all partitioned tablespaces treated equally under the
new rules?



--Phil



On 1/10/06, Grainger, Phil <[login to unmask email]> wrote:

How about this one:



"If you even THINK you might have a table that, one day, will grow big
enough to be partitioned, create it today with ONE partition". The
reason being, you can always add MORE partitions, but converting from
non-partitioned to partitioned is still a pain (and promises to continue
to be a pain for some time)

Phil Grainger


--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for
zOS
Wakefern Food Corporation
Edison, NJ, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm