MODIFY--Change Table or Index Properties
Valid in: QUEL, EQUEL
Changes properties of a table or index.
This statement has the following format:
[##] modify tablename|indexname
to storage_structure [unique]
[on columnname [asc|desc]{, columnname [asc|desc]}]
[with_clause]
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)
newlocation=(location_name {, location_name})
oldlocation=(location_name {, location_name})
location=(location_name {, location_name})
compression [= ([[no]key] [,[no]data])] | nocompression
[no]persistence
unique_scope = row | statement
To move a table:
[##] modify tablename|indexname to relocate
with oldlocation = (locationname {, locationname}),
newlocation = (locationname {, locationname}),
To change locations for a table:
[##] modify tablename|indexname to reorganize
with location = (locationname {, locationname})
To delete all data in a table:
[##] modify tablename|indexname to truncated
To reorganize a btree table's index:
[##] modify tablename|indexname to merge
To add pages to a table:
[##] modify tablename|indexname to add_extend
[with extend = number_of_pages]
number_of_pages
Is 1 to 8,388,607
The modify statement enables you to perform the following operations:
• 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.
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 you modify a table, 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 you are modifying the table to reorganize its index).
(The modify statement does not fire rules defined for the specified tables. For details about rules, see the SQL Reference Guide.)
Last modified date: 01/30/2023