Change the storage distribution strategy

Use the ALTER FRAGMENT statement to change the storage distribution strategy of the data rows that are being loaded into an existing database table.

You should adjust the storage distribution strategy when the volume or distribution of the data is different than what was originally expected when the storage distribution plan was first implemented. The ALTER FRAGMENT can also be used as part of the workflow of a data warehouse. If a table is partitioned with a fragment key that is based on values in a DATE or DATETIME column, the fragments can be detached from the table. As new fragments are added to the table older fragments, that store rows from earlier time periods, can be detached from the table.

The ALTER FRAGMENT statement supports the following six options for table fragments. Some ALTER FRAGMENT options are valid for nonfragmented tables or for index fragments.
Note: The following summary ignores tables that are fragmented by ROUND ROBIN, because other fragmentation strategies are more often used in data warehousing applications.
ADD
Adds a new fragment in the list of fragments that are part of a table that has been fragmented by any fragmentation scheme.

For LIST or EXPRESSION fragments, you can add a NULL fragment or a REMAINDER fragment, if none of these types of fragments have already been defined. You can use the BEFORE or AFTER keyword to specify the ordinal position of the new fragment in the list of expressions or list of fragments.

For a table that has been fragmented with the INTERVAL option, you can use the ADD option can add new storage spaces to the list of dbspaces where the database server creates new INTERVAL fragments.

ATTACH
Combines two or more tables that have identical structures into a fragmentation strategy. All of the consumed tables specified in the ALTER FRAGMENT ATTACH statement must have the same structure as the surviving table. The number, names, data types, and relative positions of the columns must be identical. The consumed tables must be nonfragmented, and must be stored in a different dbspace from the surviving table. The ATTACH option does not support index fragments.

For LIST or EXPRESSION fragments, you can attach a NULL fragment or a REMAINDER fragment, if none of these types of fragments have already been defined. You can use the BEFORE or AFTER keyword to specify the ordinal position of the new fragment in the list of fragments.

For a table that has been fragmented by INTERVAL, the ATTACH option can attach new RANGE fragments. However you cannot attach new INTERVAL fragments, and you cannot use the BEFORE or AFTER keyword to specify the ordinal position of the new RANGE fragment.

When a new EXPRESSION fragment is attached to table that is fragmented by LIST or by INTERVAL, the rows from the consumed table and the affected fragments in the surviving table are scanned and moved into appropriate partitions. These strategies are not overlapping.

You can also include the ONLINE keyword in ALTER FRAGMENT ATTACH statements with interval partitioning. Specifying this keyword can improve concurrency for other sessions that attempt to access the tables on which the ALTER FRAGMENT ONLINE statement operates.

DETACH
Removes a table fragment from a distribution scheme and places the contents into a new, nonfragmented table. The DETACH option does not support index fragments.

The table from which the fragment was detached remains fragmented, unless it is fragmented by LIST or by EXPRESSION and had only two fragments before the DETACH operation.

The new table does not inherit any indexes, constraints, or discretionary access privileges of the table from which it was detached. The new table has the default access privileges of any new table.

The ALTER FRAGMENT DETACH statement cannot remove a fragment from a table that is the parent of a referential constraint, or from a table on which a ROWID column is defined.

You can also include the ONLINE keyword in ALTER FRAGMENT DETACH statements with interval partitioning.

DROP
Drops a fragment from a table or index that is fragmented by LIST or by EXPRESSION. Using the DROP option requires, however, that any rows currently stored in the fragment can be moved to another existing fragment. For LIST fragments, the existing fragment can only be the REMAINDER fragment, because of the uniqueness requirement for LIST fragment expressions.

For a table or index that is fragmented by INTERVAL, you can use the DROP option to drop one or more dbspaces from the list of dbspaces that store INTERVAL fragments. No new INTERVAL fragments will be created in the specified dbspaces.

ALTER TABLE DROP operations that result in moving a large number of rows can fail if insufficient log space or disk space is available. You might be able to complete the operation by dividing it into a series of smaller operations. If insufficient log space causes the failure, an alternative is to temporarily turning off logging. Then retry the ALTER TABLE operation and turn transaction-logging back on after the operation completes. To perform ALTER TABLE DROP operations requires a backup of the root dbspace.

INIT
Defines, modifies, or replaces the fragmentation strategy or the storage location of an existing table or an existing index.
For an index, you can accomplish these tasks:
  • Change an existing fragmented index to a nonfragmented index.
  • Change the interval value of the interval distribution scheme for a fragmented index.
  • Change the interval fragment key of the interval distribution scheme for a fragmented index.
  • Fragment an existing index that is not fragmented without redefining the index.
  • Change the distribution scheme of an existing fragmented index to another distribution scheme of the same expression, list, or interval type, or to a different type of distribution scheme.
For a nonfragmented table, you can accomplish these tasks:
  • Move a nonfragmented table from one dbspace to another dbspace.
  • Move a nonfragmented table from one dbspace to a named fragment.
  • Change a nonfragmented table to a fragmented table.
For a fragmented table, you can accomplish these tasks:
  • Convert a fragmented table to a nonfragmented table.
  • Replace the current fragmentation scheme with a different fragmentation scheme of the same type or of a different type
  • Change the expression associated with an existing list-based or expression-based fragment
  • Add a new rowid column to a fragmented table. This column stores a unique integer that cannot be updated. The database server automatically creates an index on the rowid column. With this column, the database server can find the physical location of any row.

If the table is not empty when you convert an existing storage fragmentation strategy to another strategy, the database server discards the existing fragmentation strategy and moves data rows to fragments that you define in the new fragmentation strategy. Data movement also occurs when you convert a nonfragmented index to a fragmented index, and when you convert a fragmented index to an nonfragmented index. For large tables, data movement can cause significant logging, or the transaction might approach the long-transaction high-watermark, and a relatively long exclusive lock might be held on the affected tables. Use these ALTER FRAGMENT INIT options when they do not interfere with day-to-day operations.

MODIFY
Change the current fragmentation list of a table or of an index.
For LIST or EXPRESSION fragments, the MODIFY option can accomplish these 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.
  • Rename one or more existing fragments.
For a table that is fragmented by INTERVAL, the MODIFY option can accomplish these tasks:
  • Modify the expression that defines a range fragment.
  • Increase the value of the expression that defines the transition value of the table.
  • Enable or disable the automatic creation of interval fragments.
  • Replace the list of dbspaces where system-generated interval fragments will be created. Existing fragments in the old dbspaces are not moved, and new rows that match their fragment expressions will be inserted into those fragments.
  • Move a range fragment or an interval fragment to a different dbspace.
  • Rename one or more existing fragments.

When you change the expression that defines a range fragment, the replacement expression cannot cross adjacent fragment boundaries.

You cannot modify the system-generated expression for any INTERVAL fragment, and you cannot decrease the transition value of a table that is fragmented by INTERVAL.

You can also include the ONLINE keyword in ALTER FRAGMENT ON TABLE INTERVAL TRANSITION statements.