Defining a Fragmentation Strategy on a Nonfragmented Table

The INIT clause can define a fragmentation strategy on a nonfragmented table, regardless of whether the table was created with a storage option.

In Dynamic Server, when you use the INIT clause to fragment an existing nonfragmented table, all indexes on the table become fragmented in the same way as the table.

In Extended Parallel Server, when the INIT clause fragments an existing nonfragmented table, any indexes retain their existing fragmentation strategy.

FRAGMENT BY Clause for Indexes (IDS)

The INIT FRAGMENT BY clause for indexes of the ALTER FRAGMENT statement can fragment an existing nonfragmented index by an expression-based distribution scheme without redefining the index.

FRAGMENT BY Clause for Indexes:
 
|--+-FRAGMENT--+--BY EXPRESSION--------------------------------->
   '-PARTITION-'
 
   .-,--------------------------------------------.
   V                                              |
>----+-----------------+--(--expr--)--IN--dbspace-+------------->
     '-PARTITION--part-'
 
>--,--+-----------------+--+-REMAINDER--+--IN--dbspace----------|
      '-PARTITION--part-'  '-(--expr--)-'

Element	Description	Restrictions	Syntax
dbspace	Dbspace that contains the fragmented information	Must specify at least two, but no more than 2,048 dbspaces	Identifier
expr	Expression defining an index fragment	Must return a Boolean value	Condition ; Expression
part	Name that you declare here for a partition of the specified dbspace	Required for any partition in the same dbspace as another partition of the same index	Identifier

The keywords FRAGMENT BY and PARTITION BY are synonyms in this context. You can convert an existing fragmentation strategy to another expression-based fragmentation strategy. Dynamic Server discards the existing fragmentation strategy and moves the data records to fragments that you define in the new fragmentation strategy. (To convert an existing fragmented index to a nonfragmented index, you can use the INIT clause to specify IN dbspace or else PARTITION partition IN dbspace as the only storage specification for a previously fragmented index.)

The expression can contain only columns from the current table and data values from only a single row. No subqueries nor aggregates are allowed. The built-in CURRENT, DATE, and TODAY functions are not valid here.