Using the REMAINDER Option

You cannot add a remainder fragment if one already exists. If you add a new fragment when a remainder exists, the database server retrieves and reevaluates all records in the remainder fragment; some records might move to the new fragment. The remainder fragment is always the last item in the fragment list.

DROP Clause (IDS)

Use the DROP clause to remove an existing fragment from the fragmentation list of a fragmented table or index.

DROP Clause:
 
|--DROP--+-----------+--fragment--------------------------------|
         '-PARTITION-'

Element	Description	Restrictions	Syntax
fragment	Name of a partition of the dbspace that stores the dropped fragment	Must exist when you execute the statement	Identifier

If the table is fragmented by expression, you cannot drop a fragment containing data that cannot be moved to another fragment. (If the distribution scheme has a REMAINDER option, or if the expressions overlap, you can drop a fragment that contains data.) You cannot drop a fragment if the table has only two fragments.

When you want to make a fragmented table nonfragmented, use either the INIT clause or the DETACH clause of the ALTER FRAGMENT statement.

If the fragment was not given a name when it was created or added then the name of the dbspace is also the name of the fragment.

When you drop a fragment, the underlying partition or dbspace is not affected. Only the fragment data values within that partition or dbspace are affected.

When you drop a fragment, the database server attempts to move all records in the dropped fragment to another fragment. In this case, the destination fragment might not have enough space for the additional records. If this happens, follow one of the procedures that ALTER FRAGMENT and Transaction Logging describes to increase your available space, and retry the procedure.

The following examples show how to drop a fragment from a fragmentation list. The first line shows how to drop an index fragment, and the second line shows how to drop a table fragment.

ALTER FRAGMENT ON INDEX cust_indx DROP dbsp2;

ALTER FRAGMENT ON TABLE customer DROP dbsp1;

MODIFY Clause (IDS)

Use the MODIFY clause to change an existing fragment expression on an existing partition in the fragmentation list of a table or of an index. You can also use the MODIFY clause to relocate a fragment corresponding to an expression from one dbspace to a different dbspace.

MODIFY Clause:
 
           .-,-----------------------------------------------------------------------------.
           V                                                                               |
|--MODIFY----+-----------+--old--TO--+----------------+--+-expression-------+--IN--dbspace-+--|
             '-PARTITION-'           '-PARTITION--new-'  |  (1)             |
                                                         '--------REMAINDER-'

Notes:

Use this path no more than once

Element	Description	Restrictions	Syntax
dbspace	Dbspace that stores the modified information	Must exist at time of execution	Identifier
expression	Modified expression	Can specify columns in current table only and data from only a single row	Condition ; Expression
new	Name that you declare here for a partition of dbspace	Must be unique in fragmentation list among names of partitions of dbspace	Identifier
old	Name of an existing fragment	Must exist at time of execution	Identifier

Here dbspace and old (or old and new) can be identical, if you are not changing the storage location.

You must declare a new partition (using the PARTITION keyword) if more than one fragment of the same table or index is named the same as the dbspace.

The expression must evaluate to a Boolean value (true or false).

No subqueries or aggregates are allowed in the expression. In addition, the built-in CURRENT, DATE, and TODAY functions are not valid.

When you use the MODIFY clause, the underlying dbspaces are not affected. Only the fragment data values within the partitions or dbspaces are affected.

You cannot change a REMAINDER fragment into a nonremainder fragment if records within the REMAINDER fragment do not satisfy the new expression.

When you use the MODIFY clause to change an expression without changing the storage location for the expression, you must use the same name for the old and the new fragment (or else the same name for old and for dbspace, if the dbspace consists of only a single partition, as in the following example):

ALTER FRAGMENT ON TABLE cust_acct 
   MODIFY dbsp1 TO acct_num < 65 IN dbsp1

When you use the MODIFY clause to move an expression from one dbspace to another, old is the name of the dbspace where the expression was previously located, and dbspace is the new location for the expression:

ALTER FRAGMENT ON TABLE cust_acct
   MODIFY dbsp1 TO acct_num < 35 IN dbsp2

Here the distribution scheme for the cust_acct table is modified so that all row items in column acct_num that are less than 35 are now contained in the dbspace dbsp2. These items were formerly contained in the dbspace dbsp1.

When you use the MODIFY clause to move an expression from one partition of a dbspace to another partition, old is the name of the partition where information fragmented by the expression was previously located, and new is the name of the partition that is the new location for the expression, as in the following example:

ALTER FRAGMENT ON TABLE cust_acct
   MODIFY PARTITION part1 TO PARTITION part2 (acct_num < 35) IN dbsp2

Here the distribution scheme for the cust_acct table is modified so that all row items in column acct_num that have a value less than 35 are now contained in the partition part2 of dbspace dbsp2. These items were formerly contained in the partition part1.

To use the MODIFY clause both to change the expression and to move its corresponding fragment to a new storage location, you must change the expression and you must also specify the name of a different dbspace or partition.

If the indexes on a table are attached indexes, and you modify the table, the index fragmentation strategy is also modified.