|
|
|
To delete all the rows in the table and release the file space back to the operating system, specify MODIFY...TO TRUNCATED. For example, the following statement deletes all rows in the acct_payable table and releases the space:
modify acct_payable to truncated;
Using truncated converts the storage structure of the table to heap. The with_clause options cannot be specified when using modify…to truncated.
|
|
|
To add pages to a table, specify MODIFY...TO ADD_EXTEND. To specify the number of pages to be added, use the extend=number_of_pages option. If the with extend=number_of_pages option is omitted, the DBMS Server adds the default number of pages specified for extending the table. To specify the default, use the modify...with extend statement. If no default has been specified for the table, 16 pages are added.
The modify...to add_extend option does not rebuild the table or drop any secondary indexes.
|
|
|
To add pages to a blob extension table, specify MODIFY...WITH BLOB_EXTEND. To specify the number of pages to be added, use the extend=number_of_pages option. If the with extend=number_of_pages option is omitted, the DBMS Server adds the default number of pages specified for extending the table. To specify the default, use the modify...with extend statement. If no default has been specified for the table, 16 pages are added.
The modify...with blob_extend option does not rebuild the table or drop any secondary indexes.
|
|
|
A physically inconsistent table has some form of physical corruption. A table is marked physically inconsistent if rollforwarddb of that table fails for some reason, or if the table is a secondary index and point-in-time recovery has occurred only on the base table.
The MODIFY...TO PHYS_CONSISTENT command marks the table as physically consistent but does not change the underlying problem.
|
|
|
A logically inconsistent table is out-of-step in some way with one or more logically related tables. A table is marked logically inconsistent if the table is journaled and the user enters rollforwarddb +c -j on the table, or if the table is journaled and the user rolls forward to a point-in-time. For example, if two tables are logically related through referential constraints, and only one is moved to a specific point-in-time, the table is marked logically inconsistent.
The MODIFY TO LOG_CONSISTENT command marks the table as logically consistent but does not fix the underlying problem.
|
|
|
To prevent the possibility of logically or physically inconsistent tables due to table-level recovery, table-level recovery can be disallowed for any table with the MODIFY...TO TABLE_RECOVERY_DISALLOWED command.
|
|
|
To defer the uniqueness check until the end of statement execution (for unique indexes or base table structures), specify the MODIFY...TO UNIQUE_SCOPE = STATEMENT|ROW option. This statement allows more than one row to contain the same value for a declared unique column during the processing of an update statement, as long as the column values are unique at the end of the statement. These semantics are required to support ANSI uniqueness constraints.
This modify statement is necessary when using a pre-existing secondary index or the base table structure for a unique constraint. When an index is constructed specifically for a unique constraint definition (even when the definition includes a constraint with clause), the unique_scope attribute is set automatically.
|
|
|
This option marks the table READONLY and returns an error if insert, delete, or update operations are performed. Additionally, all transactions that use the readonly table take a shared table lock.
|
|
|
This modify statement permits the assignment of a cache priority without having to also change the storage structure. This must be an integer between 0 and 8, with 0 being the lowest priority and 8 being the highest. A specification of 0 causes the table to revert to a normal cache management algorithm and is the default value. If an explicit priority is not set for an index belonging to a base table to which an explicit priority has been assigned, the index inherits the priority of the base table.