MODIFY
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The MODIFY statement changes properties of a table or index.
The syntax of MODIFY varies, depending on the modify action.
The MODIFY statement has the following general format:
[EXEC SQL] MODIFY [schema.]tablename |[schema.]indexname |
[ schema.]tablename PARTITION partition-name { . partition-name }
[, PARTITION partition-name { . partition-name }
TO modify-action [UNIQUE]
[ON column_name [ASC|DESC]{, column_name [ASC|DESC]}]
[with_clause]
X100: When modify-action is COMBINE, the format is:
MODIFY [schema.]tablename [[UNION ut] [EXCEPT et] ...] TO COMBINE
tablename | indexname
Specifies the table or index whose properties are to be modified.
PARTITION partition-name
Allows the modify action to be applied specifically to the named partitions.
The PARTITION partition-name clause can be used with the following modify actions only: ADD_EXTEND, DROP, MERGE, RECONSTRUCT, RELOCATE, REORGANIZE, SPLIT, and TABLE_DEBUG and is required when splitting, merging and dropping partitions.
Use of TABLE_DEBUG against a partitioned table requires the PARTITION clause and a logical partition name for each dimension. (That is, the table debug operation can operate only 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 a dimension is omitted, it is translated as “all logical partitions in this dimension.”
Note: All other variants of the MODIFY statement can be applied to the entire table only, not to individual partitions.
modify-action
Specifies how the table should be modified. The modify-action can be any one of the following keywords:
COMBINE
X100 only. Merges updates buffered in memory and performs bulk DML operations on any form of a table. It writes the data from all completed, cached, INSERT/UPDATE/DELETE statements against a table that reside in memory to disk and optimizes the table layout on disk.
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 (physically rebuilds the table)
REORGANIZE
Moves the data to a different location
RELOCATE
Moves the table to a different location
TRUNCATED
Truncates the table, deleting all data
SPLIT
Splits the specified partition into two or more partitions. Requires the PARTITION partition-name clause and WITH PARTITION clause.
MERGE
Merges the specified partitions into fewer partitions. Requires the PARTITION partition-name clause and WITH PARTITION clause.
MERGE shrinks a btree index when used without the WITH PARTITION clause and without a partition list.
DROP
Drops a partition and deletes its data. Requires the PARTITION partition-name clause.
ADD_EXTEND
Adds disk pages to the table
[NO]READONLY
Marks the table read only or not read only
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 buffer cache priority of the table
ENCRYPT
Enables or disables encryption for the table. Must be used with the WITH PASSPHRASE clause. For more information on data encryption, see the Security Guide.
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 deprecated; the WITH COMPRESSION= clause should be used instead.
One of the storage structure actions (HEAP, HASH, ISAM, BTREE) can be used instead of RECONSTRUCT.
UNIQUE
Requires each key value in the restructured table to be unique. This clause is used only with the ISAM, HASH, or BTREE modify-actions.
ON column-name
Determines the storage structure keys of the table. This clause is used only with ISAM, HASH, BTREE, or HEAPSORT actions.
with_clause
Specifies WITH clause parameters consisting 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
• PASSPHRASE = encryption passphrase
[NEW_PASSPHRASE = new encryption passphrase]