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]
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: RECONSTRUCT, RELOCATE, REORGANIZE, MERGE, ADD_EXTEND, and TABLE_DEBUG.
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 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 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 only to the partitioned table as a whole, not to individual partitions.
modify-action
Specifies how the table should be modified. The modify-action can be any one of the following 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 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]