ALTER DATABANK

Alters the file location, transaction control option or size of a databank.

 

Usage

Embedded, Interactive, Module, ODBC, JDBC.

Description

The SET clause is used to set or change various characteristics for the specified databank.

The DROP clause is used to remove databank attributes, like file size limitations and REMOVABLE. The DROP FILESIZE option will shrink the file size as much as possible.

FILESIZE

The databank’s physical file size is set by using the SET FILESIZE option.

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.

The DROP FILESIZE option will shrink the file size as much as possible.

GOALSIZE

By specifying a GOALSIZE value, the system will always try to keep the databank size limited to the value specified.

MAXSIZE

It is possible to specify the maximum file size by using the MAXSIZE option.

MINSIZE

It is possible to specify the minimum file size by using the MINSIZE option.

This option is used to assure ALTER DATABANK DROP FILESIZE does not shrink the databank file too much.

FILE

If the FILE clause is specified, the databank location stored in the data dictionary is changed to the location given in the filename-string parameter. The file specified by filename-string must exist when the ALTER DATABANK statement is executed. The filename-string may 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 System Management Manual, Databank Check Functionality, for details on the DBC functionality.

The ALTER DATABANK statement 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 DATABANK statement 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 DATABANK statement without any verification. In this case the file is validated when the databank is next set ONLINE and the SET DATABANK statement will fail if the file does not verify correctly against the checks performed.

ADD FILE

This command may also be done concurrently with other operations on the databank. As this is typically a fast operation, it is not possible to cancel.

DROP FILE

Drop file can be done concurrently with other operations on the databank. The operation may itself take a long time, especially when there is a lot of data in the file that needs to be reallocated to the remaining file(s). In addition, the system will wait for concurrent transactions for TRANSDB. I.e. any long running transaction will block the completion of the drop of a TRANSDB file command. For other databanks, long running large object operations may also block completion.

It is possible to cancel the command. When this happens any wait for transaction or large object operations is interrupted. If the command is in the process of moving data this operation is interrupted and the system will continue working with the state where the drop file command was interrupted.

OPTION

If the SET OPTION clause is specified, the transaction control option of the databank is changed. The possible options are:

•LOG

All operations on the databank are performed under transaction control. All transactions are logged.

•TRANSACTION

All operations on the databank are performed under transaction control. No transactions are logged.

•WORK

All operations on the databank are performed without transaction control (even if they are requested within a transaction) and are not logged. Set operations (DELETE, UPDATE and INSERT on 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 consistent will not offer optimal performance when used in a query).

•READ ONLY

Only read only operations are allowed, i.e. DELETE, UPDATE and INSERT operations 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.

REMOVABLE

When a databank is set to the REMOVABLE attribute, the database system does not signal an error when a SELECT, UPDATE, or DELETE operation 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 REMOVABLE attribute, an open file error is returned whenever it is accessed and the file cannot be accessed.) INSERT operations 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 AUTOUPGRADE.

Restrictions

Only the creator of the databank may alter all of its attributes. An ident with BACKUP privilege may alter the databank’s different file size attributes.

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 UNIQUE index has been created, must have databank option set to TRANSACTION or LOG.

There can only be one ADD FILE and/or DROP FILE command active for a single databank. If a command is in progress for the selected databank, a return code with databank locked is returned.

A maximum of 15 files per databank is allowed.

Notes

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 FILESIZE can help avoid disk fragmentation.

For databanks with option TRANSACTION and LOG the 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 FILE statement 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 DATABANK statement to correct the location stored in the data dictionary.

The value of filename-string must 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 filename-string.

When the databank option is altered to WORK, all secondary indexes contained in the databank will be flagged as not consistent.

It is not possible to update primary key columns if the table is located in a databank with the WORK option.

Tip:It is possible to alter the location of a databank by first doing an ADD FILE operation, followed by a DROP of the original file. This may be done when data is accessed by other applications.

Examples

ALTER DATABANK usrdb SET GOALSIZE 100 M, MAXSIZE 1 G;

ALTER DATABANK usrdb ADD FILE 'usrdb_pt2', GOALSIZE 100 M, MAXSIZE 1 G;

ALTER DATABANK usrdb DROP FILE 'usrdb_pt1';

Standard Compliance