Modifying Fragmentation Strategies for Dynamic Server

Using the ADD Clause

When you define a fragmentation strategy, you might need to add one or more fragments. You can use the ADD clause of the ALTER FRAGMENT statement to add a new fragment to a table. Suppose that you want to add a fragment to a table that you create with the following statement:

CREATE TABLE sales (acc_num INT, ...)
   FRAGMENT BY ROUND ROBIN IN dbspace1, dbspace2, dbspace3;

To add a new fragment dbspace4 to the table sales, execute the following statement:

ALTER FRAGMENT ON TABLE sales ADD dbspace4;

If the fragmentation strategy is expression based, the ADD clause of ALTER FRAGMENT contains options to add a dbspace before or after an existing dbspace.

Using the DROP Clause

When you define a fragmentation strategy, you might need to drop one or more fragments. With Dynamic Server, you can use the DROP clause of the ALTER FRAGMENT ON TABLE statement to drop a fragment from a table. Suppose you want to drop a fragment from a table that you create with the following statement:

CREATE TABLE sales (col_a INT), ...) 
   FRAGMENT BY ROUND ROBIN IN dbspace1, dbspace2, dbspace3;

The following ALTER FRAGMENT statement uses a DROP clause to drop the third fragment dbspace3 from the sales table:

ALTER FRAGMENT ON TABLE sales DROP dbspace3;

When you issue this statement, all the rows in dbspace3 are moved to the remaining dbspaces, dbspace1 and dbspace2.

Using the MODIFY Clause

Use the ALTER FRAGMENT statement with the MODIFY clause to modify one or more of the expressions in an existing fragmentation strategy.

Suppose that you initially create the following fragmented table:

CREATE TABLE account (acc_num INT, ...)
   FRAGMENT BY EXPRESSION
      acc_num <= 1120 IN dbspace1,
      acc_num > 1120 AND acc_num < 2000 IN dbspace2,
      REMAINDER IN dbspace3;

When you execute the following ALTER FRAGMENT statement, you ensure that no account numbers with a value less than or equal to zero are stored in the fragment that dbspace1 contains:

ALTER FRAGMENT ON TABLE account
   MODIFY dbspace1 TO acc_num > 0 AND acc_num <=1120;

You cannot use the MODIFY clause to alter the number of fragments that your distribution scheme contains. Use the INIT or ADD clause of ALTER FRAGMENT instead.