MODIFY
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The MODIFY statement changes the properties of a table or index.
This statement has the following 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
tablename
Specifies the name of the table. The user must own the table or have MODIFY permission for the table.
modify-action
Specifies how the table should be modified. The modify-action can be any one of the following keywords:
COMBINE
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.
RECONSTRUCT
Rewrites all data of the table. All in-memory updates are merged to disk and empty disk space is reclaimed.
The RECONSTRUCT operation can repartition the table with the WITH PARTITION clause or remove partitioning with the WITH NOPARTITION clause.
MODIFY tablename TO RECONSTRUCT is the equivalent of deprecated command CALL X100(REWRITE 'tablename').
TRUNCATED
Deletes all data from a table and its indexes.
This statement deletes all rows and releases the file space back to the operating system. MODIFY...TO TRUNCATED performs the same operation as deprecated command CALL X100(COMBINE 'a-a'). Unlike COMBINE, however, MODIFY takes an exclusive lock on the table so concurrent operations against the table being modified must wait until the operation is completed and the lock is released when the transaction is committed or rolled back.
The MODIFY...TO TRUNCATED operation fails under the following conditions:
• If removing all rows will result in a foreign-key constraint violation
• If a concurrent DML or truncate operation on the same table commits in the meantime
• If a background update-propagation operation on the same table commits in the meantime
• If the table is referenced by another table's foreign key, and a concurrent DML operation on the referencing table commits in the meantime
ENCRYPT
Enables or disables access to an encrypted table by supplying or retracting the passphrase for the table. Must be used with the WITH PASSPHRASE clause
WITH PASSPHRASE = encryption passphrase [NEW_PASSPHRASE = new encryption passphrase]
PASSPHRASE specifies an encryption passphrase used to access a table with one or more encrypted columns. The passphrase must match the latest one defined for the table. WITH PASSPHRASE must be used with the ENCRYPT modify action.
NEW_PASSPHRASE lets you change the passphrase. (The passphrase is initially defined on the CREATE TABLE statement.) It must be preceded by the WITH PASSPHRASE clause. The passphrase must be at least eight characters, can contain spaces, and must be enclosed in single quotes.
After the new passphrase is defined using NEW_PASSPHRASE, access to the encrypted table is disabled until an additional MODIFY … ENCRYPT WITH PASSPHRASE statement is issued, which verifies that the new passphrase was typed correctly.
If the second command succeeds you can then issue a COMMIT, confident that you have changed the passphrase correctly. If this series of MODIFY … ENCRYPT … NEW_PASSPHRASE and MODIFY … ENCRYPT commands fails because of a password mismatch, you can retry the second MODIFY (if you mistyped the passphrase) or ROLLBACK the passphrase change attempt to the old passphrase and try again.
Last modified date: 06/28/2024