Adding another partition

Louise Reid

Adding another partition
How do you add another partition to a partitioned tablespace in V5? Can I
just alter or create the new partition? I do not have to change the key
limit in any other partition. If yes - does someone have sample syntax they
have used?
TIA, Louise



Michael Ebert

Re: Adding another partition
(in response to Louise Reid)
You have to unload (REORG UNLOAD EXTERNAL), drop, recreate, reload data. If
you do this in parallel (on a partition level), you can handle big TSs
within a rather short time. NPIs have to be recreated (with DEFER YES) and
rebuilt after all data has been reloaded. I've done this several times for
TSs up to about 50 GB with no problem.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




From: "Reid, Louise" <[login to unmask email]>@RYCI.COM on 26/12/2001
09:38 CST

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>



To: [login to unmask email]


cc:






Subje Adding another partition
ct:





How do you add another partition to a partitioned tablespace in V5? Can I
just alter or create the new partition? I do not have to change the key
limit in any other partition. If yes - does someone have sample syntax they
have used?
TIA, Louise








Michael Ebert

Re: Adding another partition
(in response to Michael Ebert)
You have to unload data (REORG UNLOAD EXTERNAL), drop & recreate the TS,
reload Data. NPIs should be recreated after all data has been loaded, with
DEFER YES; then rebuild them. If you do this in parallel (on a partition
level), you can handle quite big TSs in a rather short time. I've done this
several times with TSs up to about 50 GB with no problem.
Using DSN1COPY should also be possible, but I would not recommend it (I've
used it only once, when changing the partitioning index) - with the
strategy above you've got the data in an external format, which is safer.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



How do you add another partition to a partitioned tablespace in V5? Can I
just alter or create the new partition? I do not have to change the key
limit in any other partition. If yes - does someone have sample syntax they
have used?
TIA, Louise



Bayard Tysor

Re: Adding another partition
(in response to Michael Ebert)
Louise,

Unfortunately, in Version 5 you must drop and recreate
the tablespace. Yes, this is a big pain (unload, drop,
create, load, image copy). In the future you might
want to create more partitions than you need.

Tink
--- "Reid, Louise" <[login to unmask email]> wrote:
> How do you add another partition to a partitioned
> tablespace in V5? Can I
> just alter or create the new partition? I do not
> have to change the key
> limit in any other partition. If yes - does someone
> have sample syntax they
> have used?
> TIA, Louise
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can


=====
B.L. "Tink" Tysor
Bayard Lee Tysor, Inc
(401)965-2688
www.BLTysor.com

__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com



Max Scarpa

Re: Adding another partition
(in response to Bayard Tysor)
Hi

No in V5 you cannot add a new partition.

In V6 you cannot add a new partition as well but you can redistribute data
altering partitioning keys (done 3 days ago) + reorganizing altered
partitions. I suspect this is the first feature for a future 'ADD
PARTITION' statement (V8 ? Roger ?).

In V5 the only (safe) method is UNLOAD/DROP+DEFINE NEW PARTITIONS/RELOAD as
suggested in a previous reply.

Sorry, but life is a hard game to play.

Max Scarpa
The most useless and the most expensive DB2 sysprog in the Earth

PS
If you will use tape to unload data remember that tapes can broke (It
happened twice to me), expecially in a STK silo :-((.