ALTER FRAGMENT statement

Use the ALTER FRAGMENT statement to change the distribution strategy or the storage location of an existing table or index. This statement is an extension to the ANSI/ISO standard for the SQL language.

Syntax

Notes:

¹ Only with ATTACH, DETACH, or MODIFY on tables fragmented by interval
² See ATTACH Clause
³ See DETACH Clause
⁴ See INIT Clause
⁵ See ADD Clause
⁶ See DROP Clause
⁷ See MODIFY Clause


Element	Description	Restrictions	Syntax
`surviving _index`	Index on which to modify the distribution or storage	Must exist when the statement executes	Identifier
`surviving _table`	Table on which to modify the distribution or storage	Must exist. See Restrictions on the ALTER FRAGMENT Statement.	Identifier

Usage

The ALTER FRAGMENT statement applies only to table fragments or index fragments that are located at the current site. No remote information is accessed or updated.

You must have the Alter privilege or the DBA privilege to change the fragmentation strategy of a table. You must have the Index privilege or the DBA privilege to alter the fragmentation strategy of an index.

Attention: This statement can cause indexes to be dropped and rebuilt. Before undertaking alter operations, check corresponding sections in your HCL OneDB™ Performance Guide to review effects and strategies.

Clauses of the ALTER FRAGMENT statement support the following tasks.

Clause

Effect

ATTACH

Combines two or more tables that have the same schema into a single fragmented table

DETACH

Detaches one fragment from a fragmented table, and creates a new nonfragmented table to store the rows in the fragment.

INIT

Provides the following options:

Defines and initializes a fragmentation strategy on a nonfragmented table
Changes the order of evaluation of fragment expressions
Changes the fragmentation strategy of a fragmented table or index
Changes the storage location of an existing table
Moves data from an existing table fragment into a new nonfragmented table
Changes the storage location of fragments that the database generates for a table or index
Changes the fragmentation key or fragmentation expression for a table or index

ADD

Adds an additional fragment to an existing fragmentation list

DROP

Drops an existing fragment from a fragmentation list

Remove one or more dbspaces from the list of dbspaces where interval fragments are created.

MODIFY

Changes an existing interval, list, or expression-based fragmentation expression

Moves an existing fragment to a different dbspace

Replaces with a new list the current list of dbspaces where interval fragments are created.

Enables or disables automatic creation of interval fragments

Use the CREATE TABLE statement or the INIT clause of the ALTER FRAGMENT statement to create fragmented tables.

After a dbspace has been renamed successfully by the onspaces utility, only the new name can reference the renamed dbspace. The database server automatically updates existing fragmentation strategies for tables or indexes in the system catalog, however, to replace the old dbspace name with the new name. You do not need to take any additional action to update a distribution strategy or storage location that was defined using the old dbspace name, but you must use the new name if you reference the dbspace in an ALTER FRAGMENT or ALTER TABLE statement.

If you omit the optional ONLINE keyword, the ALTER FRAGMENT operation requires exclusive access and exclusive locks on all of the tables involved in the operation. If you enable the FORCE_DDL_EXEC session environment option, you can force out other transactions that have opened a table involved in an ALTER FRAGMENT ON TABLE operation, or that have placed locks on any of those tables. If the server is unable to get exclusive access and exclusive locks on the table, the server starts rolling back the transactions that are open or that have locks on the table, until the value specified with the FORCE_DDL_EXEC option is reached. (For more information, see FORCE_DDL_EXEC session environment option.)