Examples of ALTER FRAGMENT ON INDEX statements

This first example creates an index stored in dbsp1:

CREATE INDEX item_idx ON items (stock_num) IN dbsp1;

The following statement modifies the index to add fragmentation by expression. Values up to 50 are stored in dbsp1, values between 51 and 80 in dbsp2, and the remainder in dbsp3:

ALTER FRAGMENT ON INDEX item_idx INIT
   FRAGMENT BY EXPRESSION
   stock_num <= 50 IN dbsp1,
   stock_num > 50 AND stock_num <= 80 IN dbsp2,
   REMAINDER IN dbsp3;

The following statement adds a new fragment to the index:

ALTER FRAGMENT ON INDEX item_idx
   ADD stock_num > 80 AND stock_num <= 120 IN dbsp4;

The following statement changes the first fragment of the index:

ALTER FRAGMENT ON INDEX item_idx
   MODIFY dbsp1 TO stock_num <= 40 IN dbsp1;

The following statement drops the fragment in dbsp4 from the index:

ALTER FRAGMENT ON INDEX item_idx
   DROP dbsp4;

The following statement defines an index that is fragmented by expression, with the fragments stored in named partitions of the dbspaces dbsp1 and dbsp2:

ALTER FRAGMENT ON INDEX item_idx INIT
   PARTITION BY EXPRESSION
   PARTITION part1 stock_num <= 10 IN dbsp1,
   PARTITION part2 stock_num > 20 AND stock_num <= 30 IN dbsp1,
   PARTITION part3 REMAINDER IN dbsp2;

The following statement adds a new named fragment:

ALTER FRAGMENT ON INDEX item_idx ADD
   PARTITION part4 stock_num > 30 AND stock_num <= 40 IN dbsp2
   BEFORE part3;

ALTER FRAGMENT ON INDEX idx2 INIT
   FRAGMENT BY RANGE(c2) 
      INTERVAL (NUMTOYMINTERVAL(1,'MONTH') 
         PARTITION part0 VALUES <  DATE('01/01/2007') IN dbs0,
         PARTITION part1 VALUES <  DATE('07/01/2007') IN dbs1,
         PARTITION part2 VALUES <  DATE('01/01/2008') IN dbs2

ALTER FRAGMENT ON INDEX idx2 INIT
   FRAGMENT BY LIST(state)
      PARTITION part0 VALUES ('KS','IL') IN dbs0,
      PARTITION part1 VALUES ('CA','OR') IN dbs0,
      PARTITION part2 VALUES (NULL) IN dbs1,
      PARTITION part3 REMAINDER IN dbs2;