MODIFY Clause

Use the MODIFY clause to change the fragmentation list or the fragment key of a table or of an index that is fragmented by list or by expression, or to define, modify, or disable a range-interval or rolling window fragmentation scheme.

Use the MODIFY clause to change the existing fragmentation list of a table or of an index. You can use this clause to accomplish one or more of the following tasks:

Move an existing fragment from one dbspace to a different dbspace
Change the expression associated with an existing list-based or expression-based fragment
Change the expression that defines the transition fragment in a range-interval fragmentation list
Rename one or more existing fragments
Enable or disable the automatic creation of interval fragments
Replace the list of dbspaces or the function specifying where to store new interval fragments
Change a rolling window table to a table fragmented by interval (with no purge policy)
Change a table that is fragmented by range interval to a rolling window table
Change the purge policy of a rolling window table by one or more of these actions:
- Resetting the limit on the number of interval fragments,
- Changing the allocated storage size limit for the table,
- Replacing the ATTACH or DISCARD keyword option,
- Replacing the ANY or INTERVAL FIRST or INTERVAL ONLY keyword option.

The MODIFY clause of the ALTER FRAGMENT statement has the following syntax:

MODIFY Clause

Fragment Expression

Notes:

¹ Range interval fragmentation only
² See Rolling Window clause
³ Use this path no more than once
⁴ List fragmentation only
⁵ Not valid for range interval fragmentation


Element	Description	Restrictions	Syntax
`const_expr`	Constant expression that defines the list of values for a fragment to store, or a new upper limit for the range-interval transition fragment	Must be a quoted string or a literal value. For fragmentation by list, each value must be unique among the expression lists for fragments of the same object.	Constant Expressions
`dbspace`	Dbspace that stores the `new` fragment	Must exist at time of execution. All of the dbspaces must have the same page size. If the table is in a tenant database, the `dbspace` must be a dedicated dbspace in the tenant database properties list. If the table is not in a tenant database, the `dbspace` cannot be the name of a dbspace that is dedicated to a tenant database.	Identifier
`dbspace _fun`	Name of a UDF that returns the name of a dbspace	The user-defined function and the returned dbspace must exist when the database server calls the UDR to allocate storage for a new fragment.	CREATE FUNCTION statement
`expression`	Modified expression	Can specify columns in current table only and data from only a single row	Condition; Expression
`new_dbspace`	Dbspace that stores system-generated range interval fragments	Must exist at time of execution. All of the dbspaces must have the same page size. If the table is in a tenant database, the `new_dbspace` must be a dedicated dbspace in the tenant database properties list. If the table is not in a tenant database, the `new_dbspace` cannot be the name of a dbspace that is dedicated to a tenant database.	Identifier
`new`	Name that you declare here for the modified fragment	Must be unique among fragment names in the fragmentation list. If a table and its index use the same range interval or list fragmentation strategy, each index fragment must have the same name as the corresponding table fragment.	Identifier
`old`	Name of an existing fragment	Must exist in the fragmentation list. For list or range interval fragments, the PARTITION keyword must precede this name.	Identifier
`range _expr`	Range expression. This constant expression defines the upper bound for fragment key values stored in the fragment	Must be a constant literal expression that evaluates to a numeric, DATETIME, or DATE data type compatible with the data type of the fragment key expression. See also Restrictions on the MODIFY clause for range interval fragments.	Constant Expressions

Usage

Here dbspace and old (or old and new) can be identical, if you are not changing the storage location. For tables or indexes that are fragmented by range interval, the specified list of dbspaces that follows the STORE IN keywords replaces the list of dbspaces that was in effect before you issued the ALTER FRAGMENT . . . MODIFY statement. Fragments in the former list of dbspaces are not relocated by this option.

Instead of a list of literal dbspace identifiers, the STORE IN clause can optionally specify a user-defined function that returns the name of an existing dbspace. The identifier that you declare for this UDF is arbitrary. For more information about this UDF and an example of how to create it, see the discussion of the STORE IN clause in the CREATE TABLE topic Interval fragment clause.

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.

You must declare a new fragment name if more than one fragment of the same table or index has the same identifier as the dbspace. The PARTITION keyword is required immediately before the new fragment name for range interval fragments and for list fragments, but it is optional for round-robin fragments and expression-based fragments.

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, DBINFO, SYSDATE, and TODAY expressions are not valid.

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. If the dbspace consists of only a single partition, however, you can specify the same name for old and for dbspace, as in the following example:

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

For list fragmentation strategies, the ALTER FRAGMENT MODIFY statement fails with an error if a new list expression overlaps any existing list expressions for another fragment of the same table or index.

When you use the MODIFY clause to move a fragment from one dbspace to another, old is the name of the dbspace where the fragment was previously located, and dbspace is the new location for the fragment, as in the following example:

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

The ALTER FRAGMENT statement above modifies the distribution scheme for the cust_acct table, so that the rows with values in column acct_num that are less than 35 (and that had previously been assigned to fragment part1 that was stored in the dbspace dbsp1) will now be assigned to the fragment part2 that is stored in the dbspace dbsp2.

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

Unless the fragmentation strategy is by range interval, if no remainder fragment already exists, you can redefine a nonremainder fragment as a remainder fragment for rows that do not match the fragment key values for any other fragment. You cannot change a REMAINDER fragment into a nonremainder fragment, however, if any rows already stored in the REMAINDER fragment do not satisfy the new expression.

An attached index has the same storage distribution as its table. If all the indexes on a table are attached indexes, and you use the MODIFY clause to modify the table fragments, the database server automatically modifies the storage distribution strategy for the index to match the new table fragmentation strategy.

The old specification cannot reference the transition fragment (the last range fragment) of any table that is fragmented by a range-interval storage distribution scheme. The only modification that is valid for that fragment is to use the TRANSITION TO const_expr clause to increase the transition value. For any other syntax that attempts to redefine the range expression of the transition fragment directly, the database server returns an error. For more information, see the topic Using the MODIFY INTERVAL TRANSITION option.