DB2 V8 Z/OS ADDing partitions

Balachandran Chandrasekaran

DB2 V8 Z/OS ADDing partitions
Hello List,

I am having Db2 v8 CM still and could not try this new feature yet.

Does the ALTER TABLE <> ADD PARTITION has the restriction that it could
add a partition ONLY IF NUMPARTS is already declared considering this
new count ? Meaning, if NUMPARTS is 10 but, at the time of CREATE TABLE
, PARTITION n ENDING AT () had been given for all the 10 partitions,
can't we add more partition ? OR ,does this need a ALTER TABLESPACE
NUMPARTS 11 statement beforehand ?

Sorry, the question is very basic... But, lacking an installation to
test this.


Regards,
Bala.


---------------------------------------------------------------------------------
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 Z/OS ADDing partitions
(in response to Balachandran Chandrasekaran)
Bala

Regardless of how you created the table/tablespace you can STILL add
more partitions with ADD PARTITION

The only issues to be aware of are:

1. The limit key of the new partition MUST be beyond the limit key of
the current highest partition. If the current last limit key is
X'FFFF....' for example, you cannot add a new partition until you have
altered this to be a lower value
2. There are limits to the number of partitions that you are allowed,
based on PAGESIZE and DSSIZE. The maximum MAY be 4,096 but it may also
be as low as 256. When you have reached the limit for your particular
tablespace, you cannot add any more partitions. See pg 511 of the V8 SQL
Reference Guide

Hope this helps

Phil Grainger
CA
Product Manager
Phone: +44 (0)161 929 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Chandrasekaran, Balachandran
Sent: 23 January 2007 08:40
To: [login to unmask email]
Subject: [DB2-L] DB2 V8 Z/OS ADDing partitions


Hello List,

I am having Db2 v8 CM still and could not try this new feature yet.

Does the ALTER TABLE <> ADD PARTITION has the restriction that it could
add a partition ONLY IF NUMPARTS is already declared considering this
new count ? Meaning, if NUMPARTS is 10 but, at the time of CREATE TABLE
, PARTITION n ENDING AT () had been given for all the 10 partitions,
can't we add more partition ? OR ,does this need a ALTER TABLESPACE
NUMPARTS 11 statement beforehand ?

Sorry, the question is very basic... But, lacking an installation to
test this.


Regards,
Bala.

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

Balachandran Chandrasekaran

Re: DB2 V8 Z/OS ADDing partitions
(in response to Phil Grainger)
Crystal Clear. Thanks, Phil. I just read a IBM Red book : "IBM DB2 V8 on
Z/OS : Everything you wanted to know and more"... Remember read a line
"adding partition however is restricted to parameters specified on the
CREATE TABLESPACE statement".... PAGESIZE DSSIZE did not strike my mind
immediately.. I thought NUMPARTS being referred subtly.
Thanks once again !

Regards,
Bala.


________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Grainger, Phil
Sent: Tuesday, January 23, 2007 3:05 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V8 Z/OS ADDing partitions



Bala

Regardless of how you created the table/tablespace you can STILL
add more partitions with ADD PARTITION

The only issues to be aware of are:

1. The limit key of the new partition MUST be beyond the limit
key of the current highest partition. If the current last limit key is
X'FFFF....' for example, you cannot add a new partition until you have
altered this to be a lower value
2. There are limits to the number of partitions that you are
allowed, based on PAGESIZE and DSSIZE. The maximum MAY be 4,096 but it
may also be as low as 256. When you have reached the limit for your
particular tablespace, you cannot add any more partitions. See pg 511 of
the V8 SQL Reference Guide

Hope this helps

Phil Grainger
CA
Product Manager
Phone: +44 (0)161 929 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Chandrasekaran, Balachandran
Sent: 23 January 2007 08:40
To: [login to unmask email]
Subject: [DB2-L] DB2 V8 Z/OS ADDing partitions


Hello List,

I am having Db2 v8 CM still and could not try this new feature
yet.

Does the ALTER TABLE <> ADD PARTITION has the restriction that
it could add a partition ONLY IF NUMPARTS is already declared
considering this new count ? Meaning, if NUMPARTS is 10 but, at the time
of CREATE TABLE , PARTITION n ENDING AT () had been given for all the 10
partitions, can't we add more partition ? OR ,does this need a ALTER
TABLESPACE NUMPARTS 11 statement beforehand ?

Sorry, the question is very basic... But, lacking an
installation to test this.


Regards,
Bala.


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


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