Syntax for MODIFY Operations
Use the syntax shown below to perform the listed operation:
• Reorganize a btree table's index:
[EXEC SQL] MODIFY tablename|indexname TO MERGE
• Move a table:
[EXEC SQL] MODIFY tablename|indexname TO RELOCATE
WITH OLDLOCATION = (locationname {, locationname}),
NEWLOCATION = (locationname {, locationname}),
• Change locations for a table:
[EXEC SQL] MODIFY tablename|indexname TO REORGANIZE
WITH LOCATION = (locationname {, locationname})
• Delete all data in a table:
[EXEC SQL] modify tablename|indexname TO TRUNCATED
• Add pages to a table:
[EXEC SQL] MODIFY tablename|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 tablename|indexname TO ADD_EXTEND
[WITH BLOB_EXTEND = number_of_pages]
where number_of_pages is 1 to 8,388,607.
• Mark a table as physically consistent/inconsistent:
[EXEC SQL] MODIFY tablename|indexname TO PHYS_CONSISTENT|PHYS_INCONSISTENT
• Mark a table as logically consistent/inconsistent:
[EXEC SQL] MODIFY tablename|indexname TO LOG_CONSISTENT|LOG_INCONSISTENT
• Mark a table as allowed or disallowed for table-level recovery:
[EXEC SQL] MODIFY tablename|indexname TO
TABLE_RECOVERY_ALLOWED|TABLE_RECOVERY_DISALLOWED
• Defer the uniqueness check until the end of statement execution:
[EXEC SQL] MODIFY tablename TO UNIQUE_SCOPE = statement
• Mark a table as readonly:
[EXEC SQL] MODIFY tablename TO [NO]READONLY
• Assign a table fixed cache priority:
[EXEC SQL] MODIFY tablename TO PRIORITY = cache_priority
• Change a table's partitioning scheme:
[EXEC SQL] MODIFY tablename TO RECONSTRUCT
WITH PARTITION = ( partitioning-scheme )
• Enable a table modification to be performed online:
[EXEC SQL] MODIFY tablename TO HASH ON col WITH CONCURRENT_UPDATES
• Enable access to a table with encrypted columns:
[EXEC SQL] MODIFY tablename TO ENCRYPT
WITH PASSPHRASE='encryption passphrase'
• Disable access to a table with encrypted columns:
[EXEC SQL] MODIFY tablename TO ENCRYPT
WITH PASSPHRASE=''
• Change the passphrase for a table with encrypted columns:
[EXEC SQL] MODIFY tablename TO ENCRYPT
WITH PASSPHRASE='encryption passphrase',
NEW_PASSPHRASE='new encryption passphrase'
Last modified date: 01/30/2023