Hi Team,

I made a range partitioned table as below;-

db2 "create table test.part_test (a int, b int) partition by range (b)(starting 1 ending 4 every 2)"

now I will be able to enter values from 1 to 4 and every two will go in different partitions by name PART0 and PART1

now the issue is I want to extend this partition from ending at 4 to suppose 10 so that the table keeps on generating a partition after every 2 values till 10, please suggest me the best way to do it, which would have minimum outage and also minimum effort.

Would be really thank ful to your help

there is no alter possible
you have to alter table detach .. and alter attach .. with new limits
or add a new partition
if indexes are not partitioned this can take a certain time related to the size of table

Thanks for the reply,

can you please elaborate a bit and also can u tell me some way to automate the same ?

If you want to add new partitions with new ranges (that don't overlap with any existing ranges), you can do that with an alter table to add the new partitions. If you need to change the range of an existing partition, you will have to detach the partition and recreate it with the new range. The commands are documented in the Alter Table SQL section of the SQL Reference manual Vol 2.

in case you don't have the address of infocenter
Adding data partitions to partitioned tables
we use a script to rotate partitions each/day/month depending on the table and as we have 9.5 and only global indexes this requires some time.. because the partitions are very large..

Even if you have 9.7 (which supports partitioned indexes) you have to make sure that the partitioning column for a table is included in every unique index for that table, otherwise the indexes will not be partitioned. This sometimes results in "unusual" multi-column PK designs.

Guys, Thanks for the help. Can you please suggest me how the capacity planning for tablespace shall be done for range partitioned tables.

Yep. And when you think: "a well, I change the index in 'not unique' and drop the PK constraint." Beware that all the FK-contraints pointing that table are also dropped without any warning. So, when you're dealing with an existing table the PK-index can not be partitioned and that is too bad for all the performance gains you thought you could achieve....