Alters the file location, transaction control option or size of a databank.
SETclause is used to set or change various characteristics for the specified databank.
DROPclause is used to remove databank attributes, like file size limitations and
DROP FILESIZEoption will shrink the file size as much as possible.
The databank's physical file size is set by using the
When specifying sizes,
K(kilo) means that the size (in bytes) is multiplied by 1 024,
M(mega) means the size is multiplied by 1 048 576, and
G(giga) means that the size is multiplied by 1 073 741 824.
DROP FILESIZEoption will shrink the file size as much as possible.
By specifying a
GOALSIZEvalue, the system will always try to keep the databank size limited to the value specified.
It is possible to specify the maximum file size by using the
It is possible to specify the minimum file size by using the
This option is used to assure
ALTER DATABANK DROP FILESIZEdoes not shrink the databank file too much.
FILEclause is specified, the databank location stored in the data dictionary is changed to the location given in the
filename-stringparameter. The file specified by
filename-stringmust exist when the
ALTER DATABANKstatement is executed. The
filename-stringmay be represented as character literal, national character literal, or unicode character literal.
The new file must be identifiable as a copy of the databank created for the current Mimer SQL database. The first page of the databank file is read to verify that the data in the databank can be accessed and that the file was closed correctly the last time it was used.
If the file is flagged internally as not being closed correctly, a full databank check is effectively done on it, see the Mimer SQL System Management Handbook, Databank Check Functionality, for details on the DBC functionality.
ALTER DATABANKstatement will fail if the new file does not verify correctly against the checks performed.
If the timestamp information in the databank file indicates that additional information must be restored to it to bring it up to date, an information message is written to the database server log file (this message will be returned to the user if the database is being accessed in single user mode).
This situation will not cause the
ALTER DATABANKstatement to fail, but any attempt to subsequently access the databank will raise an error indicating that additional information must be restored to the databank. Once the additional information has been restored, the databank can be used normally.
If the databank is
OFFLINE, however, the new file will be accepted by the
ALTER DATABANKstatement without any verification. In this case the file is validated when the databank is next set
SET DATABANKstatement will fail if the file does not verify correctly against the checks performed.
SET OPTIONclause is specified, the transaction control option of the databank is changed. The possible options are:
All operations on the databank are performed under transaction control. All transactions are logged.
All operations on the databank are performed under transaction control. No transactions are logged.
All operations on the databank are performed without transaction control (even if they are requested within a transaction) and are not logged. Set operations (
INSERTon several rows) which are interrupted will not be rolled back. All secondary indexes contained in the databank are flagged as
not consistent(a secondary index that is flagged as
not consistentwill not offer optimal performance when used in a query).
Only read only operations are allowed, i.e.
INSERToperations are not allowed, nor it's possible to create indexes or altering tables.
Note: Secondary indexes for tables in a databank that is altered from WORK option will still be flagged as not consistent after the ALTER DATABANK operation. Use the UPDATE STATISTICS statement to make the indexes consistent, see UPDATE STATISTICS.
When a databank is set to the
REMOVABLEattribute, the database system does not signal an error when a
DELETEoperation is performed on a table in an inaccessible databank. Instead, the system behaves as if the table is empty and signals an end-of-table condition. (If the databank does not have the
REMOVABLEattribute, an open file error is returned whenever it is accessed and the file cannot be accessed.)
INSERToperations will always signal an error if the databank is inaccessible.
This functionality is useful, for example, if the databank is located on a flash memory card.
Note: A database can be set in AUTOUPGRADE mode, which has precedence for REMOVABLE, meaning that for a databank having both AUTOUPGRADE and REMOVABLE enabled a missing databank and/or table will be created. I.e. the file is created whenever it is accessed. If the create fails, the REMOVABLE attribute is used.
- See ALTER DATABASE for more information about
Only the creator of the databank may alter it.
The databank option may only be set to
LOGfor a databank that is shadowed or contains a table defined with foreign or unique keys, a table referenced in a foreign key context, or a table on which a
UNIQUEindex has been created.
If the extension of the databank exceeds the available disk space, the databank is extended as much as possible.
A databank will be extended automatically on operating systems supporting dynamic file extension (provided that there is free space on the disk). However, such incremental extensions may lead to the disk becoming fragmented, so the use of explicit
ALTER DATABANK ... SET FILESIZEcan help avoid disk fragmentation.
For databanks with option
LOGthe system treats the maximum size as an advisory limit. This limit may be temporarily exceeded. The reason for this is that the actual updating of the databank files are performed in the background while the detection of the maximum size is performed when the applications perform insert operations during transaction buildup. In addition, when several concurrent users are inserting data the actual space is not reserved until the background updates are made.
Changing the location of a databank with the
ALTER DATABANK... SET FILEstatement only changes the file location stored in the data dictionary, it does not move any physical files in the host operating system. You must first copy or move the databank file to its new location using operating system commands and then use the
ALTER DATABANKstatement to correct the location stored in the data dictionary.
The value of
filename-stringmust always be enclosed in string delimiters. The maximum length of the filename string is 256 characters.
Refer to Specifying the Location of User Databanks for details concerning the specification of path-name components in
When the databank option is altered to
WORK, all secondary indexes contained in the databank will be flagged as
It is not possible to update primary key columns if the table is located in a databank with the
ExampleALTER DATABANK usrdb SET GOALSIZE 100 M, MAXSIZE 1 G;
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40