MODIFY
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The MODIFY statement changes properties of a table or index.
The MODIFY statement has the following format:
[EXEC SQL] MODIFY [schema.]tablename|[schema.]indexname |
[ schema.]tablename PARTITION partition-name { . partition-name }
TO modify-action [UNIQUE]
[WITH PASSPHRASE = encryption passphrase [NEW_PASSPHRASE = new encryption passphrase]]
[ON column_name [ASC|DESC]{, column_name [ASC|DESC]}]
[with_clause]
X100 format:
MODIFY [schema.]tablename TO modify-action [WITH PASSPHRASE = encryption passphrase [NEW_PASSPHRASE = new encryption passphrase]]
When modify-action is COMBINE, the format is:
MODIFY [schema.]tablename [[UNION ut] [EXCEPT et] ...] TO COMBINE
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:
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.
MODIFY tablename UNION ut EXCEPT et TO COMBINE is the equivalent of deprecated command CALL X100(COMBINE 'tablename+ut-et').
The user must have at least SELECT permission for the union and except tables.
Using COMBINE is the most efficient way to perform updates and deletes to a large percentage of the data in a table. However, depending on the volume of outstanding changes on a table and whether a table is indexed, COMBINE can be a lengthy operation.
For more information, see MODIFY...TO COMBINE statement.
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)
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]