ALTER TABLE SET OPTIONS
Sets table
options.
SYNTAX
| table_name |
Name
of the table you are changing options on. |
| number |
Value
to use for the fillfactor. |
DESCRIPTION
The ALTER
TABLE SET OPTIONS command modifies the definition of an existing table
and changes its options. To execute the ALTER TABLE SET OPTIONS command
on a table, you must be the table owner, have DBA security privilege,
or have the ALTER privilege for that table.
LOCK MODE
specifies the lock mode (lock level) DBMaker uses when accessing data
in a table. DBMaker has three lock modes: table, page, and row. Page lock
mode is used by default if no lock mode is explicitly specified when creating
a table. To determine the lock mode of a table, you can examine the LOCKMODE
column of the SYSTABLE system table.
LOCK MODE
TABLE locks an entire table. This mode decreases concurrency by preventing
other users from accessing the locked table at the same time, but it also
uses fewer lock resources and requires less memory in the System Control
Area (SCA).
LOCK MODE
PAGE locks a single data page. This mode is a trade-off between concurrency
and lock resources. It provides moderate concurrency since other users
may access data in other pages, but cannot access any data in the same
page.
LOCK MODE
ROW locks a single row. This mode increases concurrency by allowing other
users to access any data except the locked row at the same time, but it
also uses more lock resources and required more memory in the SCA.
FILLFACTOR
specifies the percentage of a data page that can be filled before new
records can no longer be inserted. This allows the database to optimize
the use of data pages by reserving space on a data page for updates to
existing records. The number parameter can have a value from 50 to 100,
which represents a fillfactor of 50% to 100%. To determine the fillfactor
of a table, you can examine the FILLFACTOR column of the SYSTABLE system
table.
NOCACHE
limits the number of page buffers used to cache data during a table scan.
DBMaker stores page buffers in a buffer chain with the most recently used
page at one end and the least recently used page at the other end. When
the NOCACHE option is turned on, data pages read during a table scan are
placed at the least recently used end of the buffer chain. Since the least
recently used end of the buffer chain will be flushed before the most
recently used end, subsequent data pages read during the table scan will
replace the previous page. This effectively limits the page buffers used
during a table scan to one page buffer. To determine the cache mode of
a table, you can examine the CACHEMODE column of the SYSTABLE system table.
UPDATE STATISTICS
EVERY N DAYS sets the time interval in days DBMaker will use to automatically
update statistics values. Keeping statistics information current allows
the DBMaker query optimizer to perform queries more efficiently. However,
database administrators may often forget to manually update statistics
values on a regular basis, leading to outdated statistics information
and inefficient queries. You can set the interval DBMaker will use to
update statistics from 1 day to 32768 days.
Using the
ALTER TABLE OPTIONS command has no effect on any views or synonyms based
on that table.
EXAMPLES
The following
example sets the LOCK MODE to table on the Customers table.
ALTER TABLE Customers SET LOCK MODE TABLE
|
The following
example sets the LOCK MODE to page on the Customers table.
ALTER TABLE Customers SET LOCK MODE PAGE
|
The following
example sets the LOCK MODE to row on the Customers table.
ALTER TABLE Customers SET LOCK MODE ROW
|
The
following example sets the FILLFACTOR to 90% on the Customers table.
ALTER TABLE Customers SET FILLFACTOR 90
|
The
following example turns on the NOCACHE option on the Customers table.
ALTER TABLE Customers SET NOCACHE ON
|
The
following example turns off the NOCACHE option on the Customers table.
ALTER TABLE Customers SET NOCACHE OFF
|
RELATED COMMANDS
<
ALTER TABLE PRIMARY KEY | Contents
| ALTER TABLESPACE >
|
