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:
(explicit id mod001) mod001 (explicit id mod002) mod002 (explicit id mod003) mod003 (explicit id mod004) mod004

MODIFY Clause

(explicit id mod003c) mod003c
MODIFY { [PARTITION] old TO PARTITIONnew <Fragment Expression> [ INdbspace ] | []INTERVAL { [ { ENABLED | DISABLED } ] [STORE ] IN( { dbspace | dbspace_fun( ) } ) | { ENABLED | DISABLED } | TRANSITION TO const_expr | <Rolling Window clause> [] } }

Fragment Expression

{ expression | [] { VALUES < range_expr | []VALUES IS NULL } | [] VALUES { const_expr | [] [IS] NULL } | [] []REMAINDER }
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 ids_sqs_2095.html#ids_sqs_2095.

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.

1 Range interval fragmentation only
2 Use this path no more than once
3 Not valid for range interval fragmentation
4 List fragmentation only
5 See Rolling Window clause