Modify

Valid in: SQL, ESQL

The Modify statement changes properties of a table or index.

Syntax

The Modify statement has the following format:

[EXEC SQL] MODIFY [schema.]table_name|[schema.]indexname |
              [ schema.]table-name PARTITION partition-name { . partition-name }
              TO modify-action [UNIQUE]
              [ON column_name [ASC|DESC]{, column_name [ASC|DESC]}]
              [with_clause]

The modify-action can be any one of the following action keywords:

• ISAM

  Modifies the table storage structure to the ISAM structure.

• HASH

  Modifies the table storage structure to the HASH structure

• HEAP

  Modifies the table storage structure to the HEAP structure

• HEAPSORT

  Modifies the table storage structure to the HEAP structure, and additionally sort the rows in the table as directed

• BTREE

  Modifies the table storage structure to the BTREE structure

• RECONSTRUCT

  Modifies the table storage structure to what it currently is (the table is physically rebuilt)

• TRUNCATED

  Truncates the table, deleting all data

• REORGANIZE

  Moves the data to a different location

• RELOCATE

  Moves the table to a different location

• MERGE

  Shrinks a btree index

• ADD_EXTEND

  Adds disk pages to the table

• [NO]READONLY

  Marks the table readonly or not readonly

• PHYS_[IN]CONSISTENT

  Marks the table physically consistent or inconsistent

• LOG_[IN]CONSISTENT

  Marks the table logically consistent or inconsistent

• TABLE_RECOVERY_[DIS]ALLOWED

  Allows or disallow table level rollforward

• [NO]PERSISTENCE

  Marks the index to be recreated automatically as needed (secondary indexes only)

• UNIQUE_SCOPE = ROW | STATEMENT

  Defines when uniqueness must be checked

• TABLE_DEBUG

  Displays internal table data structures

• PRIORITY=n

  Sets the table's buffer cache priority

The additional action_keywords CHEAP, CHASH, CISAM, and CBTREE are accepted. CHEAP is a synonym for HEAP with compression=(data), and the others similarly. These forms are depreciated; the WITH COMPRESSION= clause should be used instead.

The UNIQUE clause is only used with the ISAM, HASH, or BTREE modify-actions.

The ON column-name clause is only used with ISAM, HASH, BTREE, or HEAPSORT actions.

A with_clause consists of the word WITH followed by a comma-separated list of any number of the following items:

• ALLOCATION = n
• EXTEND = n
• FILLFACTOR = n  (isam, hash, and btree only)
• MINPAGES = n  (hash only)
• MAXPAGES = n  (hash only)
• LEAFFILL = n  (btree only)
• NONLEAFFILL = n  (btree only)
• BLOB_EXTEND = n  (btree only)
• NEWLOCATION = (locationname {, locationname})
• OLDLOCATION = (locationname {, locationname})
• LOCATION = (locationname {, locationname})
• COMPRESSION [= ([[no]key] [,[no|hi]data])] | nocompression
• [NO]PERSISTENCE
• UNIQUE_SCOPE = ROW | STATEMENT
• PAGE_SIZE = n
• PRIORITY = cache_priority
• NOPARTITION
• PARTITION = (partitioning-scheme)
• CONCURRENT_UPDATES

Use the syntax shown below to perform the listed operation:

• Reorganize a btree table's index:

  [EXEC SQL] MODIFY table_name|indexname TO MERGE

• Move a table:

  [EXEC SQL] MODIFY table_name|indexname TO RELOCATE
      WITH oldlocation = (locationname {, locationname}),
          newlocation = (locationname {, locationname}),

• Change locations for a table:

  [EXEC SQL] MODIFY table_name|indexname TO REORGANIZE
      WITH LOCATION = (locationname {, locationname})

• Delete all data in a table:

  [EXEC SQL] modify table_name|indexname  to truncated

• Add pages to a table:

  [EXEC SQL] MODIFY table_name|indexname TO ADD_EXTEND
      [WITH EXTEND = number_of_pages]

  where:

  number_of_pages is 1 to 8,388,607.

• Add pages to blob extension table:

  [EXEC SQL] MODIFY table_name|indexname WITH BLOB_EXTEND
      [WITH EXTEND = number_of_pages]

  where:

  number_of_pages is 1 to 8,388,607.

• Mark a table as physically consistent/inconsistent:

  [EXEC SQL] MODIFY table_name|indexname TO PHYS_CONSISTENT|PHYS_INCONSISTENT

• Mark a table as logically consistent/inconsistent:

  [EXEC SQL] MODIFY table_name|indexname  TO LOG_CONSISTENT|LOG_INCONSISTENT

• Mark a table as allowed or disallowed for table-level recovery:

  [EXEC SQL] MODIFY table_name|indexname TO
      TABLE_RECOVERY_ALLOWED|TABLE_RECOVERY_DISALLOWED

• Defer the uniqueness check until the end of statement execution:

  [EXEC SQL] MODIFY table_name TO UNIQUE_SCOPE = statement

• Mark a table as readonly:

  [EXEC SQL] MODIFY table_name TO [NO]READONLY

• Assign a table fixed cache priority:

  [EXEC SQL] MODIFY table_name TO PRIORITY = cache_priority

• Change a table's partitioning scheme:

  [EXEC SQL] MODIFY table_name TO RECONSTRUCT

  WITH PARTITION = ( partitioning-scheme )

• Enable a table modification to be performed online:

  [EXEC SQL] MODIFY table_name WITH CONCURRENT_UPDATES

One of the storage structure actions (HEAP, HASH, ISAM, BTREE) can be used instead of RECONSTRUCT.

Description

The Modify statement enables the following operations to be performed:

• Change the storage structure of the specified table or index
• Specify the number of pages allocated for a table or index, and the number of pages by which it grows when it requires more space
• Add pages to a table
• Reorganize a btree index
• Move a table or index, or portion thereof, from one location to another
• Spread a table over many locations or consolidate a table onto fewer locations
• Delete all rows from a table and release its file space back to the operating system
• Specify whether an index is recreated when its base table is modified
• Specify how unique columns are checked during updates: after each row is inserted or after the update statement is completed
• Mark table as physically or logically consistent or inconsistent
• Mark table as allowed/disallowed for table-level recovery
• Defer uniqueness check until the end of statement execution
• Mark a table as readonly
• Assign a table fixed cache priority
• Change a table's partitioning scheme
• Enable modify table to be performed online

You can change a table's location and storage structure in the same modify statement.

The Modify statement operates on existing tables and indexes. When modifying a table to change, truncate, or reconstruct the storage structure, the DBMS Server destroys any indexes that exist for the specified table (unless the index was created with persistence, or the table is a btree and the table being modified to reorganize its index).

The partition partition-name clause after the modify table name allows the modify action to be applied specifically to the named partition(s). The partition partition-name clause can be applied to the following modify statement variants only: modify to:, reconstruct, relocate, reorganize, merge, add_extend, and table_debug. The table debug variant against a partitioned table requires the partition clause, and a logical partition name for each dimension is required. (In other words, the table debug operation can only operate on one specific physical partition.)

For a partitioned table with multiple dimensions, partition names are listed in the same order that dimensions were defined. It is not necessary to name a logical partition for every dimension; if any dimension is omitted it is translated as "all logical partitions in this dimension."

Note: All other variants of the modify statement can be applied only to the partitioned table as a whole, not to individual partitions.

Storage Structure Specification

Changing the storage structure of a table or index is most often done to improve the performance of access to the table. For example, to improve the performance of copy change, the structure of a table to heap before performing a bulk copy into the table.

The storage_structure parameter must be one of the following:

An index cannot be modified to HEAP, HEAPSORT, or RTREE.

The DBMS Server uses existing data to build the index (for isam and btree tables), calculate hash values (for hash tables) or for sorting (heapsort tables).

To optimize the storage structure of heavily used tables (tables containing data that is frequently added to, changed, or deleted), modify those tables periodically.

The optional keyword UNIQUE requires each key value in the restructured table to be unique. (The key value is the concatenation of all key columns in a row.) If unique is specified on a table that contains non-unique keys, the DBMS Server returns an error and does not change the table's storage structure. For the purposes of determining uniqueness, a null is considered to be equal to another null.

Use unique only with isam, hash, and btree tables.

The optional ON clause determines the table's storage structure keys. This clause can only be specified when modifying to one of the following storage structures: isam, hash, heapsort, or btree. When the table is sorted after modification, the first column specified in this clause is the most significant key, and each successive specified column is the next most significant key.

If the ON clause is omitted when modifying to isam, hash, or btree, the table is keyed, by default, on the first column. When a table is modified to heap, the ON clause must be omitted.

When modifying a table to heapsort, specify the sort order as ASC (ascending) or DESC (descending). The default is ASC. When sorting, the DBMS Server considers nulls greater than any nonnull value.

In general, any MODIFY.. TO <storage_structure> ... of a table or index assigned to a raw location must include WITH LOCATION=(...) syntax to move the table or index to another set of locations because modify semantics involve a create, rename, and delete file, which works efficiently for cooked locations, but does not adapt to raw locations.

If UNIQUE is used with a partitioned table, the new storage structure must be compatible with the table's partitioning scheme. This means that given a value for the unique storage structure key columns, it must be possible to determine that a row with that key will be in one particular physical partition. In practice, this rule means that the partitioning scheme columns must be the same as (or a subset of) the storage structure key columns. It also means that unique cannot be used with AUTOMATIC partitioning. A modify to unique that violates the partitioning rule will be rejected with an error.

Note that it is still possible to enforce an arbitrary uniqueness constraint on a partitioned table, regardless of the partitioning scheme, by adding a UNIQUE or PRIMARY KEY constraint, or a unique secondary index, to the table.