Detach That Results in a Table with Hash Fragmentation (XPS)

The new table is a hash-fragmented table if the surviving_table had hybrid fragmentation and the detached dbslice has more than one fragment. In a hybrid-fragmented table, the dbslice is detached if you specify any dbspace in that slice. For example, see the sales_info table discussed in the Hybrid Fragmentation Distribution Scheme (XPS). Once the January 1997 data is available in sales_info, you might archive year-old sales_info data.

In this example, data from January 1996 is detached from the sales_info table and placed in a new table called jan_96.

INIT Clause

The INIT clause of the ALTER FRAGMENT statement can define or modify the fragmentation strategy or the storage location of an existing table or (for Dynamic Server) an existing index. The INIT clause has the following syntax.

INIT Clause:
 
|--INIT--+--------------------+--------------------------------->
         |  (1)               |
         '--------WITH ROWIDS-'
 
                                       (2)
>--+-| FRAGMENT BY Clause for Tables |-----------------------+--|
   +-+----------------------------+-- IN--+-dbspace--------+-+
   | |  (1)                       |       |  (3)           | |
   | '--------PARTITION--fragment-'       '--------dbslice-' |
   |  (1)                                      (4)           |
   '--------| FRAGMENT BY Clause for Indexes |---------------'

Notes:

Dynamic Server only
See ***
Extended Parallel Server only
See FRAGMENT BY Clause for Indexes (IDS)

Element	Description	Restrictions	Syntax
dbslice	Dbslice storing fragmented data	Must exist at time of execution	Identifier
dbspace	Dbspace storing fragmented data	Must exist at time of execution	Identifier
fragment	Partition within dbspace	No more than 2048 for same table	Identifier

The INIT clause can accomplish tasks that include the following:

Move a nonfragmented table from one partition (IDS only) or dbslice (XPS only) or dbspace to another partition or to another dbslice or dbspace.
Convert a fragmented table to a nonfragmented table.
Fragment an existing non-fragmented table without redefining it.
Convert a fragmentation strategy to another fragmentation strategy.
Fragment an existing index that is not fragmented without redefining the index (IDS only).
Convert a fragmented index to a nonfragmented index (IDS only).
Add a new rowid column to the table definition (IDS only).

With Extended Parallel Server, you cannot use the INIT clause to change the fragmentation strategy of a table that has a GK index.

When you use the INIT clause to modify a table, the tabid value in the system catalog tables changes for the affected table. The constrid value of all unique and referential constraints on the table also changes.

For more information about the storage spaces in which you can store a table, see Using the IN Clause.

Attention:

When you execute the ALTER FRAGMENT statement with this clause, it results in data movement if the table contains any data. If data values move, the potential exists for significant logging, for the transaction being aborted as a long transaction, and for a relatively long exclusive lock being held on the affected tables. Use this statement when it does not interfere with day-to-day operations.