Partitioning a large tablespace - DB2 UDB V6 for OS/390

Sri Sri

Partitioning a large tablespace - DB2 UDB V6 for OS/390
Dear List,

We are trying to re-partition an existing Tablespace which has the
following statistics. We are on DB2- UDB V6 for OS/390

1) We have 256 million records in this table with 10 partitions.
2) Each partition containing around 25 million records.
3) Currently we do not have any NPI on it.
4) Data is stored from 1997 .... onwards.
5) Programs do not update records.
6) Deletes are possible on this table.
7) This is a Child table.
8) Currently TS occupies 25,000 cylinders, Cluster IX occupies 15,000
cylinders. (No NPIs)
9) Currently 100 million records are inserted per year.

Primary Key :

Sales_ID_Fk (char(8)) , Sales_id_seq_Fk (Integer) , seq_pk(Smallint)
are the primary keys for this table which is the clustering Index.

Sales_id_FK First character is always generated which is a check digit ,
which uniformly distributes the data among all the currently existing 10
partitions.

Example primary key : 40000032(sales_id_fk) , Sales_id_seq_fk( 0024) ,
seq_pk (0002) row resides in the 4 th partition.

As per our client instructions, we need to archive this data which is older
than 3 years.
So, we want to separate the earlier years data from the current year data
in the table, and we would like to have 10 partitions active during the
Current year, where data is inserted.

Older partitions will not have any new data inserted into them, deletes may
happen rarely.

We will only reorg the current year partitions.

QUESTIONS :

1) Considering the need for archival we would like add a
partioning index with YEAR, SALES_ID_FK as the partitioning
index columns. i.e the existing primary key becomes the NPI,
which will occupy 15,000 cylinders DASD.
With the above criteria we created a test table, results are like
this.
1) Created with DSSIZE 4G, with 200 partitions.
2) 20 partitions are filled with 1997,1998,1999,2000 data
3) 20 to 29 Partitions are allocated for 2001 data
4) 30 to 200 Partitions are for future years use.
5) RUNSTATS gave
For NPI - 99% clustered.
6) All the data access will be using NPI.

3) Do we need consider the PIECESIZE for the NPI, if so what is the
optimum PIECESIZE for this NPI with 15,000cylinders DASD space.

4) We considered using a control table for rebalancing the
partitions, Since
DB2 V6 allows partition rebalancing, we felt that this is a
maintenance and Performance overhead.

5) Are there any performance overheads having 200 partitions as
compared to 10 partitions currently. (also note that as per the
above design we will be keeping lot of partitions vacant for
future year use.)

6) What are the best number of partitions we should have.

We will appreciate , if the list experts evaluate this design and
provide us with your valuable and better suggestions, if any. Sorry
for this long posting.

Thanks,
Sri



Walter Janißen

Re: Partitioning a large tablespace - DB2 UDB V6 for OS/390
(in response to Sri Sri)
What do you think about that

Take the MOD-function from year, say 3 times the number of partitions that
you need to store data for 1 year, then you don't need a NPI.



John Hardy

Re: Partitioning a large tablespace - DB2 UDB V6 for OS/390
(in response to Walter Janißen)
Walter,

Please could you elicidate your proposal a little for the benefit of
numbskulls like me please?

Whatever you are proposing, obviating the need to create the NPI is
definitely a step in the right direction. Sri talks about a 'control table'
as a maintenance overhead: the new NPI is going to be a real maintenance
pain in the neck.

Your solution might be the ideal one, but as I do not understand it, then I
shall put forward one of my own (to no doubt be shot down in flames by my
betters).

As I see it, due to the requirement to archive data after three years,
there is a need to maintain data on the table for four years, i.e. the
current year plus the previous three. I therefore propose 4 'sets' of
partitions,

e.g.

Set 1 : 1998
Set 2 : 1999
Set 3 : 2000
Set 4 : 2001

Next year will see:

Set 1 : 2002
Set 2 : 1999
Set 3 : 2000
Set 4 : 2001

...as 1998s data is archived.

This could be achieved by partitioning the table on the integer
representation of the decimal place of the year divided by 4, and using as
much of the existing high order key as is required in order to maintain as
many partitions as are required. There seems to be a need for ten
partitions per year, which is extremely convenient, as the high order digit
of the key is a random check digit between zero and nine.

Clear as mud? OK.

For the first part:

the remainder of the year divided by four cycles though values: 0, 0.25,
0.5, and 0.75. Take the first decimal place, convert it to an integer and
you have the first part of your partitioning key:

0....
2....
5....
7....

I'll call this the 'year-cycle digit' from now on.

The value of 'year-cycle digit' for each year looks like this:

1997: 2 (1997/4 = 499.25)
1998: 5 (1998/4 = 499.5)
1999: 7 (1999/4 = 499.75)
2000: 0 (2000/4 = 500)
2001: 2 (2001/4 = 500.25)
2002: 5 (2002/4 = 500.5)
2003: 7 (2003/4 = 500.75)
2004: 0 (2004/4 = 501)
etc.

So when we get to the end of 2001, we will clear out the partition set
beginning '5' (data for 1998) in preparation for data for 2002.

The index of the table will be the 'year-cycle digit' followed by the
existing primary key. The high order digit of the first column of the
existing key (sales_id_fk) is a check digit from 0 to 9, so the table could
have the following partitioning keys defined:

key consists of:

YEAR-CYCLE-DIGIT
SALES_ID_FK
SALES_ID_SEQ_FK
SEQ_PK

The values are:

xyyyyyyyy (x= YEAR-CYCLE-DIGIT, yyyyyyyy = SALES_ID_FK)


Part 1 : 009999999 ---+
Part 2 : 019999999 |
Part 3 : 029999999 | 1 complete
.... | years data
.... |
Part 10: 099999999 ---+
Part 11: 209999999
....
Part 20: 299999999
Part 21: 509999999
....
Part 39: 789999999
Part 40: 799999999 (or 999999999, if you insist)

Ok, so far so good. We've isolated the different years data in different
sets of partitions. Now for access by SQL using the existing key:

Using the 'year-cycle digit', we have eliminated the need for a control
table to derive the value of the high order part of the partitioning key.
Each program inserting rows into the table will need a new algorithm to
calculate 'year-cycle'. Maybe this could be achieved with a FIELDPROC.
Obviously, queries using the existing primary key would not be indexable.
This could be resolved with the addition of an extra predicate:

AND YEAR_CYCLE IN (0,2,5,7)

...in order to use in-list processing.

An extra sophistication would be the use of a view with the name of the
existing table.

e.g. (assuming the name of the existing table is 'SALES_DATA'):

CREATE VIEW SALES_DATA
AS SELECT SALES_ID_FK,
SALES_ID_SEQ_FK,
SEQ_PK,
etc.)
FROM SALES_DATA_TAB
WHERE YEAR_CYCLE IN (0,2,5,7)

Queries against the table using the existing primary key will not of course
be quite as efficient as present, but eliminating the NPI is very nice and
DBA-friendly...

There we are, I'm finished. Now somebody can point out the glaring great
hole in the proposal which I've sure I've missed ;)



On Wed, 10 Jan 2001 06:10:05 -0600, Walter Janissen
<[login to unmask email]> wrote:

>What do you think about that
>
>Take the MOD-function from year, say 3 times the number of partitions that
>you need to store data for 1 year, then you don't need a NPI.
>
>
>





Walter Jani&#223;en

Re: Partitioning a large tablespace - DB2 UDB V6 for OS/390
(in response to John Hardy)
John

I think, I thought a little bit too quick. What I've meant was something
like you proposed. With MOD I mean the MODULO-function in version 6.

MOD(1998, 4) = 2

But what was wrong was to multiply with number of partitions per year,
because that will always yield in the same partition.

My first correction is: MOD(YEAR(CURRENT DATE), 4) + 1
my second: multiply that with MONTH(CURRENT DATE)

then you need 12 partitions per year which means 48 partitions totally.
This number must be the first column of the partitioned key



John Hardy

Re: Partitioning a large tablespace - DB2 UDB V6 for OS/390
(in response to Walter Janißen)
Of course, the year modulus 4 is a more elegant solution than the highest
decimal place of the result of division, so:

1996 / 4 // 0
1997 / 4 // 1
1998 / 4 // 2
1999 / 4 // 3
2000 / 4 // 0
etc.

which now I think about it, was maybe what Walter was talking about....



On Wed, 10 Jan 2001 08:43:33 -0600, John Hardy
<[login to unmask email]> wrote:

>Walter,
>
>Please could you elicidate your proposal a little for the benefit of
>numbskulls like me please?
>
>Whatever you are proposing, obviating the need to create the NPI is
>definitely a step in the right direction. Sri talks about a 'control table'
>as a maintenance overhead: the new NPI is going to be a real maintenance
>pain in the neck.
>
>Your solution might be the ideal one, but as I do not understand it, then I
>shall put forward one of my own (to no doubt be shot down in flames by my
>betters).
>
>As I see it, due to the requirement to archive data after three years,
>there is a need to maintain data on the table for four years, i.e. the
>current year plus the previous three. I therefore propose 4 'sets' of
>partitions,
>
>e.g.
>
>Set 1 : 1998
>Set 2 : 1999
>Set 3 : 2000
>Set 4 : 2001
>
>Next year will see:
>
>Set 1 : 2002
>Set 2 : 1999
>Set 3 : 2000
>Set 4 : 2001
>
>...as 1998s data is archived.
>
>This could be achieved by partitioning the table on the integer
>representation of the decimal place of the year divided by 4, and using as
>much of the existing high order key as is required in order to maintain as
>many partitions as are required. There seems to be a need for ten
>partitions per year, which is extremely convenient, as the high order digit
>of the key is a random check digit between zero and nine.
>
>Clear as mud? OK.
>
>For the first part:
>
>the remainder of the year divided by four cycles though values: 0, 0.25,
>0.5, and 0.75. Take the first decimal place, convert it to an integer and
>you have the first part of your partitioning key:
>
>0....
>2....
>5....
>7....
>
>I'll call this the 'year-cycle digit' from now on.
>
>The value of 'year-cycle digit' for each year looks like this:
>
>1997: 2 (1997/4 = 499.25)
>1998: 5 (1998/4 = 499.5)
>1999: 7 (1999/4 = 499.75)
>2000: 0 (2000/4 = 500)
>2001: 2 (2001/4 = 500.25)
>2002: 5 (2002/4 = 500.5)
>2003: 7 (2003/4 = 500.75)
>2004: 0 (2004/4 = 501)
>etc.
>
>So when we get to the end of 2001, we will clear out the partition set
>beginning '5' (data for 1998) in preparation for data for 2002.
>
>The index of the table will be the 'year-cycle digit' followed by the
>existing primary key. The high order digit of the first column of the
>existing key (sales_id_fk) is a check digit from 0 to 9, so the table could
>have the following partitioning keys defined:
>
>key consists of:
>
>YEAR-CYCLE-DIGIT
>SALES_ID_FK
>SALES_ID_SEQ_FK
>SEQ_PK
>
>The values are:
>
> xyyyyyyyy (x= YEAR-CYCLE-DIGIT, yyyyyyyy = SALES_ID_FK)
>
>
>Part 1 : 009999999 ---+
>Part 2 : 019999999 |
>Part 3 : 029999999 | 1 complete
>.... | years data
>.... |
>Part 10: 099999999 ---+
>Part 11: 209999999
>....
>Part 20: 299999999
>Part 21: 509999999
>....
>Part 39: 789999999
>Part 40: 799999999 (or 999999999, if you insist)
>
>Ok, so far so good. We've isolated the different years data in different
>sets of partitions. Now for access by SQL using the existing key:
>
>Using the 'year-cycle digit', we have eliminated the need for a control
>table to derive the value of the high order part of the partitioning key.
>Each program inserting rows into the table will need a new algorithm to
>calculate 'year-cycle'. Maybe this could be achieved with a FIELDPROC.
>Obviously, queries using the existing primary key would not be indexable.
>This could be resolved with the addition of an extra predicate:
>
>AND YEAR_CYCLE IN (0,2,5,7)
>
>...in order to use in-list processing.
>
>An extra sophistication would be the use of a view with the name of the
>existing table.
>
>e.g. (assuming the name of the existing table is 'SALES_DATA'):
>
>CREATE VIEW SALES_DATA
> AS SELECT SALES_ID_FK,
> SALES_ID_SEQ_FK,
> SEQ_PK,
> etc.)
> FROM SALES_DATA_TAB
> WHERE YEAR_CYCLE IN (0,2,5,7)
>
>Queries against the table using the existing primary key will not of course
>be quite as efficient as present, but eliminating the NPI is very nice and
>DBA-friendly...
>
>There we are, I'm finished. Now somebody can point out the glaring great
>hole in the proposal which I've sure I've missed ;)
>
>
>
>On Wed, 10 Jan 2001 06:10:05 -0600, Walter Janissen
><[login to unmask email]> wrote:
>
>>What do you think about that
>>
>>Take the MOD-function from year, say 3 times the number of partitions that
>>you need to store data for 1 year, then you don't need a NPI.
>>
>>
>>
>
>
>
>
>





John Hardy

Re: Partitioning a large tablespace - DB2 UDB V6 for OS/390
(in response to John Hardy)
Walter,

This Email crossed in the post with my last Email!

Yes, although our solutions differ in detail, I think that in essence they
are the same. I am sure that if Sri likes this approach, and decides to
adopt it then he/she (sorry Sri, I can't deduce your gender from your name)
will adapt the finer points according to his/her taste.

On Wed, 10 Jan 2001 09:29:27 -0600, Walter Janissen
<[login to unmask email]> wrote:

>John
>
>I think, I thought a little bit too quick. What I've meant was something
>like you proposed. With MOD I mean the MODULO-function in version 6.
>
>MOD(1998, 4) = 2
>
>But what was wrong was to multiply with number of partitions per year,
>because that will always yield in the same partition.
>
>My first correction is: MOD(YEAR(CURRENT DATE), 4) + 1
>my second: multiply that with MONTH(CURRENT DATE)
>
>then you need 12 partitions per year which means 48 partitions totally.
>This number must be the first column of the partitioned key
>
>
>





Sri Sri

Re: Partitioning a large tablespace - DB2 UDB V6 for OS/390
(in response to John Hardy)
Dear John, Walter

Thanks for your excellent response on partition of the large tablespace.

I have few doubts :

It may happen with my client that , they may change their decision about
how many years before archiving, and i may end-up in dropping the
tablespace again

After realising the above fact, by further investigation, i found
A create timestamp is available on the parent table, from which i can
obtain the 'YEAR' part and can be included as the first column of the
partitioned key, which is also the primary key of the table, In this
way also , we can elliminate the NPI too.

Your excellent ideas helped me out, in thinking further about
elliminating the NPI.

QUESTIONS:

1) Considering future growth, what is the optimum no of parts i can
allocate.

IS THERE ANY PERFORMANCE OVERHEAD, HAVING THE VACANT PARTITIONS
FOR FUTURE USE.

2) Is there any performance overhead using DSSIZE 4G.

3) Just out of curiosity, what are the "pain in the neck" with NPI's
other than more DASD, longer rebuild, reorg times.

I am sure, your excellent suggestions , i will use in my future
partitioning.

BTW, Sri is HE:=).

Thanks,
Sri



Walter Jani&#223;en

Re: Partitioning a large tablespace - DB2 UDB V6 for OS/390
(in response to Sri Sri)
Sri

To your recent questions:

to 1: we often define some dummy partitions (in one case for a large
table about 10). Because these are not used, we habe no
performance problems with them. Their size are only 1 track.

to 2: I think no, isn't DSSIZE 4 Gig the default ?

to 3: Inserts, deletes and updates must be considered
As mentioned a few time in this list you loose concurrency in the
BUILD2-phase of an Online-Reorg

Walter



John Hardy

Re: Partitioning a large tablespace - DB2 UDB V6 for OS/390
(in response to Walter Janißen)
Hello Sri,

Sounds like you've found the correct solution to your problem.

To your questions:

1. The optimum number of parts which you should allocate? I can't answer
that question. It sounds as if you are not certain yourself how long you
might want to keep your data for. I guess if you are unable to extract a
final and definitive answer from your clients (ha,ha), then you might have
to guess it. You are obviously going to use a LARGE Tablespace (or one
defined with DSSIZE). Because with V6 you can alter the partitioning keys,
I would be happier to allocate too many rather than too few. I would not
expect there to be an appreciable overhead in allocating extra or 'spare'
partitions. I would think that as more sites move over to V6, this is going
to become recognised as a good DBA practice.

2. Performance overhead with DSSIZE? So far as I know, the only overhead
will be incurred by the need for a 5 byte RID for each entry in all of your
indices. Nothing comes for free, but it's a small (and in your case
probably necessary) price to pay for the benefits.

3. You partly answered your own question, but a complete list of the
drawbacks of NPIs would be quite long. They are a severe hindrance to
complete partition independance. Most of the problems boil down to one
thing. Without an NPI, data in different partitions are physically isolated
in both your Tablespace and your Index. A consequence of this is that (for
instance) you can run utilities against some partitions whilst allowing a
large degree of availability by applications to data in others. Also,
utilities can happily run against all your partitions concurrently (This is
of course possible even with an NPI, but with lots of gotchas). With an
NPI, you can have data from every partition on every page, so your data
independance is immediately compromised, with all the obvious consequences
to availability and concurrency. Another big drawback is that your NPI will
almost certainly be your biggest physical object, and will dictate the
elapsed time for REORG,REBUILD, etc. Using PIECESIZE will not help this, as
you cannot run utilities against PIECEs.

John


On Wed, 10 Jan 2001 22:10:42 -0600, Sri Sri <[login to unmask email]> wrote:

>Dear John, Walter
>
> Thanks for your excellent response on partition of the large tablespace.
>
> I have few doubts :
>
> It may happen with my client that , they may change their decision about
> how many years before archiving, and i may end-up in dropping the
> tablespace again
>
> After realising the above fact, by further investigation, i found
> A create timestamp is available on the parent table, from which i can
> obtain the 'YEAR' part and can be included as the first column of the
> partitioned key, which is also the primary key of the table, In this
> way also , we can elliminate the NPI too.
>
> Your excellent ideas helped me out, in thinking further about
> elliminating the NPI.
>
> QUESTIONS:
>
> 1) Considering future growth, what is the optimum no of parts i can
> allocate.
>
> IS THERE ANY PERFORMANCE OVERHEAD, HAVING THE VACANT PARTITIONS
> FOR FUTURE USE.
>
> 2) Is there any performance overhead using DSSIZE 4G.
>
> 3) Just out of curiosity, what are the "pain in the neck" with NPI's
> other than more DASD, longer rebuild, reorg times.
>
> I am sure, your excellent suggestions , i will use in my future
> partitioning.
>
> BTW, Sri is HE:=).
>
> Thanks,
> Sri
>
>
>





John Hardy

Re: Partitioning a large tablespace - DB2 UDB V6 for OS/390
(in response to John Hardy)
Correction to my comments about dssize. The RIDs are only 5 bytes for EA-
enabled t/s over 4G.

On Thu, 11 Jan 2001 07:35:19 -0600, John Hardy
<[login to unmask email]> wrote:

>Hello Sri,
>
>Sounds like you've found the correct solution to your problem.
>
>To your questions:
>
>1. The optimum number of parts which you should allocate? I can't answer
>that question. It sounds as if you are not certain yourself how long you
>might want to keep your data for. I guess if you are unable to extract a
>final and definitive answer from your clients (ha,ha), then you might have
>to guess it. You are obviously going to use a LARGE Tablespace (or one
>defined with DSSIZE). Because with V6 you can alter the partitioning keys,
>I would be happier to allocate too many rather than too few. I would not
>expect there to be an appreciable overhead in allocating extra or 'spare'
>partitions. I would think that as more sites move over to V6, this is going
>to become recognised as a good DBA practice.
>
>2. Performance overhead with DSSIZE? So far as I know, the only overhead
>will be incurred by the need for a 5 byte RID for each entry in all of your
>indices. Nothing comes for free, but it's a small (and in your case
>probably necessary) price to pay for the benefits.
>
>3. You partly answered your own question, but a complete list of the
>drawbacks of NPIs would be quite long. They are a severe hindrance to
>complete partition independance. Most of the problems boil down to one
>thing. Without an NPI, data in different partitions are physically isolated
>in both your Tablespace and your Index. A consequence of this is that (for
>instance) you can run utilities against some partitions whilst allowing a
>large degree of availability by applications to data in others. Also,
>utilities can happily run against all your partitions concurrently (This is
>of course possible even with an NPI, but with lots of gotchas). With an
>NPI, you can have data from every partition on every page, so your data
>independance is immediately compromised, with all the obvious consequences
>to availability and concurrency. Another big drawback is that your NPI will
>almost certainly be your biggest physical object, and will dictate the
>elapsed time for REORG,REBUILD, etc. Using PIECESIZE will not help this, as
>you cannot run utilities against PIECEs.
>
>John
>
>
>On Wed, 10 Jan 2001 22:10:42 -0600, Sri Sri <[login to unmask email]> wrote:
>
>>Dear John, Walter
>>
>> Thanks for your excellent response on partition of the large tablespace.
>>
>> I have few doubts :
>>
>> It may happen with my client that , they may change their decision about
>> how many years before archiving, and i may end-up in dropping the
>> tablespace again
>>
>> After realising the above fact, by further investigation, i found
>> A create timestamp is available on the parent table, from which i can
>> obtain the 'YEAR' part and can be included as the first column of the
>> partitioned key, which is also the primary key of the table, In this
>> way also , we can elliminate the NPI too.
>>
>> Your excellent ideas helped me out, in thinking further about
>> elliminating the NPI.
>>
>> QUESTIONS:
>>
>> 1) Considering future growth, what is the optimum no of parts i can
>> allocate.
>>
>> IS THERE ANY PERFORMANCE OVERHEAD, HAVING THE VACANT PARTITIONS
>> FOR FUTURE USE.
>>
>> 2) Is there any performance overhead using DSSIZE 4G.
>>
>> 3) Just out of curiosity, what are the "pain in the neck" with NPI's
>> other than more DASD, longer rebuild, reorg times.
>>
>> I am sure, your excellent suggestions , i will use in my future
>> partitioning.
>>
>> BTW, Sri is HE:=).
>>
>> Thanks,
>> Sri
>>
>>
>>
>
>
>
>
>