The remaining WITH clause options for the MODIFY statement are described below.

Use the WITH ALLOCATION option to specify the number of pages initially allocated to the table or index. By pre-allocating disk space to a table, runtime errors that result from running out of disk space can be avoided. If the table is spread across multiple locations, space is allocated across all locations.

The number of pages specified must be between 4 and 8,388,607 (the maximum number of pages in a table). If the specified number of pages cannot be allocated, the modify statement is aborted.

A table can be modified to a smaller size. If the table requires more pages that you specify, the table is extended and no data is lost. A table can be modified to a larger size to reserve disk space for the table.

If not specified, a modify does not change a table's allocation.

To specify the number of pages by which a table or index grows when it requires more space, use the WITH EXTEND clause. The number of pages specified must be between 1 and 8,388,607 (the maximum number of pages in a table). If the specified number of pages cannot be allocated when the table must be extended (for example, during an insert operation), the DBMS Server aborts the statement and issues an error. By default, tables and indexes are extended by groups of 16 pages.

If not specified, a modify does not change a table's extend attribute.

WITH FILLFACTOR specifies the percentage (from 1 to 100) of each primary data page that must be filled with rows, under ideal conditions. For example, if you specify a fillfactor of 40, the DBMS Server fills 40% of each of the primary data pages in the restructured table with rows. You can specify this option with the isam, hash, or btree structures. Take care when specifying large fillfactors because a non-uniform distribution of key values can later result in overflow pages and thus degrade access performance for the table.

WITH MINPAGES specifies the minimum number of primary pages a hash table must have. WITH MAXPAGES specifies the maximum number of primary pages a hash table can have. Minpages and maxpages must be at least 1. If both minpages and maxpages are specified in a modify statement, minpages must not exceed maxpages.

For best performance, the values for minpages and maxpages must be a power of 2. If a number other than a power of 2 is chosen, the DBMS Server can change the number to the nearest power of 2 when the modify executes. To ensure that the specified number is not changed, set both minpages and maxpages to that number.

By default, modify to storage-structure resets these attributes back to their defaults (listed below). The modify to reconstruct operation does not affect these attributes.

Default values for fillfactor, minpages and maxpages are listed in this table:

For btree tables, the WITH LEAFFILL clause specifies how full to fill the leaf index pages. Leaf index pages are the index pages that are directly above the data pages. NONLEAFFILL specifies how full to fill the non-leaf index pages; non-leaf index pages are the pages above the leaf pages. Specify leaffill and nonleaffill as percentages. For example, if you modify a table to btree, specifying NONLEAFFILL=75, each non-leaf index page is 75% full when the modification is complete.

The LEAFFILLand NONLEAFFILL parameters can assist with controlling locking contention in btree index pages. If some open space is retained on these pages, concurrent users can access the btree with less likelihood of contention while their queries descend the index tree. Strike a balance between preserving space in index pages and creating a greater number of index pages. More levels of index pages require more I/O to locate a data row.

By default, modify to storage-structure resets these attributes back to their defaults.

Default: LEAFFILL=70; NONLEAFFILL=80

The MODIFY TO RECONSTRUCT operation does not affect these attributes.

To specify the number of pages to be added to a blob extension table, add the WITH BLOB_EXTEND=n clause to MODIFY...TO ADD_EXTEND. For more information, see the description of WITH EXTEND.

To change the location of a table when modifying its storage structure, specify the WITH LOCATION clause. This option specifies one or more new locations for the table. The locations specified must exist when the statement executes and the database must have been extended to those locations. For information about areas and extending databases, see the Database Administrator Guide.

To specify data and key compression, use the WITH COMPRESSION clause. Compression removes the string trim from variable character data. The following table lists valid compression options:

To specify an uncompressed storage structure, specify WITH NOCOMPRESSION.

To compress both key and data for tables where this is valid (primarily btree), specify WITH COMPRESSION, omitting the KEY and DATA clause. To compress data or keys independently of one another, specify WITH COMPRESSION = (KEY|DATA). To compress data using bit compression, specify WITH COMPRESSION = HIDATA. To explicitly suppress compression of data or keys, specify WITH COMPRESSION = (NOKEY | NODATA).

If not specified, modify to storage-structure removes compression, unless the c-prefix variants are used (cbtree and so on). Other variants of MODIFY preserve the table's compression type.

If a secondary index is compressed (with KEY compression), the non-key columns will also be compressed.

The WITH [NO]PERSISTENCE option specifies whether an index is recreated when its related base table is modified. This option is valid only for indexes.

There are two options:

Default: A MODIFY to a storage structure sets an index to NOPERSISTENCE.

Other MODIFY actions (including MODIFY TO RECONSTRUCT) do not change an index's persistence.

The WITH UNIQUE_SCOPE option specifies, for tables or indexes with unique storage structures, how uniqueness is checked during an update option.

There are two options:

Default: UNIQUE_SCOPE = ROW, when first imposing uniqueness on a table.

Specify the UNIQUE_SCOPE option only when modifying to a unique storage structure. For example:

If not otherwise specified, a MODIFY does not change the UNIQUE_SCOPE setting.

Specify page size using WITH PAGE_SIZE = n where n can be the page size in the following table:

The default page size is 8192. The tid size is 8. The buffer cache for the installation must also be configured with the page size you specify in CREATE TABLE or an error occurs.

The WITH PARTITION= clause allows the partitioning scheme of a table to be changed. The table does not have to be partitioned initially. The WITH NOPARTITION clause removes partitioning from a table. For the syntax of a PARTITION= specification, see Partitioning Schemes.

The default for the MODIFY statement is to not change the partitioning scheme of a table.

The with_clause options PARTITION= or NOPARTITION are permitted if the MODIFY statement specifies a storage structure that includes the RECONSTRUCT action.

The WITH PARTITION clause is required when managing partitions with modify actions DROP, MERGE, and SPLIT. For more information, see:

The WITH CONCURRENT_UPDATES option specifies that a table modify is to be performed online. Unlike a regular modify, which locks out all other access to the table for the entire duration, an online modify permits normal read and update access to the table for most of the modify.

Syntax to enable a table modification to be performed online:

When the online modify completes, however, exclusive access to the table is still required during a brief period to apply any updates that were made to the table during the online modify. The length of this period depends on the number of updates that must be applied.

Online modification of tables cannot be accomplished in the following:

A table cannot be modified if it destroys indexes needed for constraints. The operation can be forced, however, by using the WITH NODEPENDENCY_CHECK option.

WITH 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.

The syntax of the WITH PASSPHRASE clause is:

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.