[DB2 V8 zOS] Converting Segmented Tablespaces into Partioned

Rodney Krick

[DB2 V8 zOS] Converting Segmented Tablespaces into Partioned



Hi,

we plan to convert a segmented tablespace into a partitioned one (for more
flexibility with our utilities and because we fear it could hit the 64 GB
in the future; now we have about 30 GB).
Here the steps we are planing:
(assume the old TS (segmented) being TSSEG (with one table, TAB) and the
new one TSPART)
- unload TSSEG
- define TSPART
- define a new table (TAB_NEW) in TSPART
- load TSPART with unloaded data from TSSEG
- rename TAB to TAB_OLD
- rename TAB_NEW TO TAB
- rebind all relevant packages
- check/test the system/application
- copy all
- and someday drop TSSEG

I believe this should do the job. One point where we're still uncertain is
the definition of the partitioning. We do not have a set of atributes that
allow us to distribute the data in optimized ranges. So we're brainstorming
a little around and I would be very grateful if someone could put some
spice in this soup.

One idea is to change the table (TAB still in TSSEG), add a new column
(TAB.part_key INTEGER) and populate the column with random numbers before
starting the process of changing the structure.
A kind of
- FETCH ROW
- SET column = MICROSECOND(CURRENT TIMESTAMP)
The functions the insert data into the table would use the same algorithm.
This column would be our "partitioned key" (and we hope we get a good
distribution of the data). At the present moment we just have two indexes
on this table, both unique. We could define them as DPSI in the new
structure.

If someone has already gone through this process (I'm sure some of you did
it) AND is willing to share his/her experiences (what I hope!) I would be
very thankful.

To avoid excess of posts you can send me your comments directly. I would
make a resume at the end and post back to the list.

Mit freundlichen Grüßen / Best regards,

Rodney Krick
Senior Consultant
____________________________
aformatik Training & Consulting GmbH & Co. KG
Tilsiter Str. 8, D-71065 Sindelfingen, Germany
Tel.: +49 (0)7031-23 807-0
Fax: +49 (0)7031-23 807-19

mailto:[login to unmask email]
___________________________________________
ECIS, Education Center for IBM Software, Websphere and DB2
---------------------------------------------------------------------------------
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: [DB2 V8 zOS] Converting Segmented Tablespaces into Partioned
(in response to Rodney Krick)
Hi Rodney

A couple of points

- Don't forget indexes, views, aliases, synonyms
- You could use a ROWID (NOT NULL WITH DEFAULT) column to randomly spread the data across partitions as you load it into TAB_NEW
- Make sure you are aware of the relationships between DSSIZE, PAGESIZE and NUMPARTS. If you make the first one too big an the second one too small, you will limit the number of partitions you can have WELL below the possible 4,096 maximum
- Is this really REALLY urgent? If not, then DB2 9 may have a solution for the "what do we partition on" question (but we won't know that for certain until IBM formally announce DB2 9s GA!) - it might certainly be worth waiting a month or few to see what your options may be later. It'd be a shame to do this twice!

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


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Rodney Krick
Sent: 07 December 2006 13:48
To: [login to unmask email]
Subject: [DB2-L] [DB2 V8 zOS] Converting Segmented Tablespaces into Partioned



Hi,

we plan to convert a segmented tablespace into a partitioned one (for more flexibility with our utilities and because we fear it could hit the 64 GB in the future; now we have about 30 GB).
Here the steps we are planing:
(assume the old TS (segmented) being TSSEG (with one table, TAB) and the new one TSPART)
- unload TSSEG
- define TSPART
- define a new table (TAB_NEW) in TSPART
- load TSPART with unloaded data from TSSEG
- rename TAB to TAB_OLD
- rename TAB_NEW TO TAB
- rebind all relevant packages
- check/test the system/application
- copy all
- and someday drop TSSEG

I believe this should do the job. One point where we're still uncertain is the definition of the partitioning. We do not have a set of atributes that allow us to distribute the data in optimized ranges. So we're brainstorming a little around and I would be very grateful if someone could put some spice in this soup.

One idea is to change the table (TAB still in TSSEG), add a new column (TAB.part_key INTEGER) and populate the column with random numbers before starting the process of changing the structure.
A kind of
- FETCH ROW
- SET column = MICROSECOND(CURRENT TIMESTAMP)
The functions the insert data into the table would use the same algorithm. This column would be our "partitioned key" (and we hope we get a good distribution of the data). At the present moment we just have two indexes on this table, both unique. We could define them as DPSI in the new structure.

If someone has already gone through this process (I'm sure some of you did it) AND is willing to share his/her experiences (what I hope!) I would be very thankful.

To avoid excess of posts you can send me your comments directly. I would make a resume at the end and post back to the list.

Mit freundlichen Grüßen / Best regards,

Rodney Krick
Senior Consultant
____________________________
aformatik Training & Consulting GmbH & Co. KG
Tilsiter Str. 8, D-71065 Sindelfingen, Germany
Tel.: +49 (0)7031-23 807-0
Fax: +49 (0)7031-23 807-19

mailto:[login to unmask email]
___________________________________________
ECIS, Education Center for IBM Software, Websphere and DB2--------------------------------------------------------------------------------- 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

Steen Rasmussen

Re: [DB2 V8 zOS] Converting Segmented Tablespaces into Partioned
(in response to Phil Grainger)
Hello Rodney,



One note in your description woke me up - you say you currently have TWO
UNIQUE indexes which could be defined as DPSI. Please remember a DPSI
can NOT be unique.



To add a comments to Phils suggestion ( wait for DB2 9 ). During the IBM
IOD conference, IBM talked about a new partitioned object type which
will remove the hassle of determining partition ranges / limit keys.
Unfortunately you will not be able to convert existing partitioned or
non-partitioned tablespaces to the new type - so Phil's suggestion to
postpone your exercise is a good point.



Steen Rasmussen
CA
Senior Consultant DB2 tools

Tel : +1-630-505-6673 (US direct)
Tel : +1-815-274-9589 (US mobile)

Tel : +45-22 15 44 98 (Europe mobile)
[login to unmask email]
< http://www.ca.com >

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Grainger, Phil
Sent: Thursday, December 07, 2006 8:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] [DB2 V8 zOS] Converting Segmented Tablespaces into
Partioned



Hi Rodney



A couple of points



- Don't forget indexes, views, aliases, synonyms

- You could use a ROWID (NOT NULL WITH DEFAULT) column to randomly
spread the data across partitions as you load it into TAB_NEW

- Make sure you are aware of the relationships between DSSIZE, PAGESIZE
and NUMPARTS. If you make the first one too big an the second one too
small, you will limit the number of partitions you can have WELL below
the possible 4,096 maximum

- Is this really REALLY urgent? If not, then DB2 9 may have a solution
for the "what do we partition on" question (but we won't know that for
certain until IBM formally announce DB2 9s GA!) - it might certainly be
worth waiting a month or few to see what your options may be later. It'd
be a shame to do this twice!



Phil Grainger

CA

Product Manager, DB2

Tel: +44 (0)161 928 9334

Fax: +44 (0)161 941 3775

Mobile: +44 (0)7970 125752

[login to unmask 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

---------------------------------------------------------------------------------
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

Paul Walters

Re: [DB2 V8 zOS] Converting Segmented Tablespaces into Partioned
(in response to Steen Rasmussen)
Under V7 we have unload the partitioning key (possibly one of your unique indexes). We have generally unloaded just a sample of the keys.

Sort and remove duplicates (incase you only selected the first x columns of a unique index)

Take this data (only keys) and load it into a new table with an identity column:
(REC_ID INTEGER NOT NULL
GENERATED BY DEFAULT
AS IDENTITY
( START WITH +1 ,
INCREMENT BY +1 ,
NO CACHE ,
NO CYCLE ,
MAXVALUE +2147483647 ,
MINVALUE +1 )
plus the remainder of your key columns.

If for example you loaded 1000 rows and you want the data partitioned 5 ways - your limit keys could be found with

Select * {your key columns}
from your table
where rec_id in (200,400,600,800,1000);





>>> [login to unmask email] 12/07/06 8:47 AM >>>



Hi,

we plan to convert a segmented tablespace into a partitioned one (for more
flexibility with our utilities and because we fear it could hit the 64 GB
in the future; now we have about 30 GB).
Here the steps we are planing:
(assume the old TS (segmented) being TSSEG (with one table, TAB) and the
new one TSPART)
- unload TSSEG
- define TSPART
- define a new table (TAB_NEW) in TSPART
- load TSPART with unloaded data from TSSEG
- rename TAB to TAB_OLD
- rename TAB_NEW TO TAB
- rebind all relevant packages
- check/test the system/application
- copy all
- and someday drop TSSEG

I believe this should do the job. One point where we're still uncertain is
the definition of the partitioning. We do not have a set of atributes that
allow us to distribute the data in optimized ranges. So we're brainstorming
a little around and I would be very grateful if someone could put some
spice in this soup.

One idea is to change the table (TAB still in TSSEG), add a new column
(TAB.part_key INTEGER) and populate the column with random numbers before
starting the process of changing the structure.
A kind of
- FETCH ROW
- SET column = MICROSECOND(CURRENT TIMESTAMP)
The functions the insert data into the table would use the same algorithm.
This column would be our "partitioned key" (and we hope we get a good
distribution of the data). At the present moment we just have two indexes
on this table, both unique. We could define them as DPSI in the new
structure.

If someone has already gone through this process (I'm sure some of you did
it) AND is willing to share his/her experiences (what I hope!) I would be
very thankful.

To avoid excess of posts you can send me your comments directly. I would
make a resume at the end and post back to the list.

Mit freundlichen Grüßen / Best regards,

Rodney Krick
Senior Consultant
____________________________
aformatik Training & Consulting GmbH & Co. KG
Tilsiter Str. 8, D-71065 Sindelfingen, Germany
Tel.: +49 (0)7031-23 807-0
Fax: +49 (0)7031-23 807-19

mailto:[login to unmask email]
___________________________________________
ECIS, Education Center for IBM Software, Websphere and DB2
---------------------------------------------------------------------------------
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

This E-Mail has been scanned for viruses.

---------------------------------------------------------------------------------
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

Walter Jani&#223;en

Re: [DB2 V8 zOS] Converting Segmented Tablespaces into Partioned
(in response to Paul Walters)
Rodney

I don't understand, why you cannot use one of your existing indexes to be
the partitioning key. Why can't you take the clustering index (if there is
one), to partition the table? The partitions need not be all of the same
size, they can vary.

The disadvantage with a surrogate key is, that nobody knows it, so the two
indexes (because they have to be unique) are NPSIs. Furthermore you change
the structure of the table and do you know, if all applications can cope
with that. May be there applications out there, with a SELECT * or with an
insert without naming the columns they have values for. Or Loads in some
jobs, which won't work after your change etc.

And remember, you can alter the limit keys without dropping the tablespace,
so you can adjust them later.

---------------------------------------------------------------------------------
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: [DB2 V8 zOS] Converting Segmented Tablespaces into Partioned
(in response to Walter Janißen)
And if you want to add an extra element of excitement to your life, you can use REORG REBALANCE to (possibly) rebalance the partitions to be almost equal - this should be easier than a manual update of the limit keys

But PLEASE test this MANY times where it doesn't matter before doing a REBALANCE in production.......


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

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: 08 December 2006 08:59
To: [login to unmask email]
Subject: Re: [DB2-L] [DB2 V8 zOS] Converting Segmented Tablespaces into Partioned

Rodney

I don't understand, why you cannot use one of your existing indexes to be the partitioning key. Why can't you take the clustering index (if there is one), to partition the table? The partitions need not be all of the same size, they can vary.

The disadvantage with a surrogate key is, that nobody knows it, so the two indexes (because they have to be unique) are NPSIs. Furthermore you change the structure of the table and do you know, if all applications can cope with that. May be there applications out there, with a SELECT * or with an insert without naming the columns they have values for. Or Loads in some jobs, which won't work after your change etc.

And remember, you can alter the limit keys without dropping the tablespace, so you can adjust them later.

---------------------------------------------------------------------------------
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

Horacio Villa

Re: [DB2 V8 zOS] Converting Segmented Tablespaces into Partioned
(in response to Phil Grainger)
In V8 you also have the option to REBALANCE the partition. The REORG
utility automatically adjusts partition limits, so that wouldn't be a
concern.

Horacio Villa



Walter Janißen <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
08/12/2006 05:58
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] [DB2 V8 zOS] Converting Segmented Tablespaces into Partioned






Rodney

I don't understand, why you cannot use one of your existing indexes to be
the partitioning key. Why can't you take the clustering index (if there is
one), to partition the table? The partitions need not be all of the same
size, they can vary.

The disadvantage with a surrogate key is, that nobody knows it, so the two
indexes (because they have to be unique) are NPSIs. Furthermore you change
the structure of the table and do you know, if all applications can cope
with that. May be there applications out there, with a SELECT * or with an
insert without naming the columns they have values for. Or Loads in some
jobs, which won't work after your change etc.

And remember, you can alter the limit keys without dropping the
tablespace,
so you can adjust them later.

---------------------------------------------------------------------------------
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

Walter Jani&#223;en

Re: [DB2 V8 zOS] Converting Segmented Tablespaces into Partioned
(in response to Horacio Villa)
Hi Phil

Nice to have you back on the list. Did you change your mailing application
or was it my remark at the info-exchange in Mannheim, that brought you back?

---------------------------------------------------------------------------------
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