CREATE DATABANK

Creates a new databank.

create_databank.png

 

Usage

Embedded, Interactive, Module, ODBC, JDBC.

Description

A new databank is created, i.e. a physical file is created in the host file system and formatted for use as a Mimer SQL databank. Databank attributes that can be set are described below.

FILESIZE

The initial file size can be specified by using the FILESIZE option. A value of 2 000 kB is assumed if an initial file size is not specified.

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.

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 attribute may be useful when executing ALTER DATABANK DROP FILESIZE, to assure that the databank file is not shrunk too much. See ALTER DATABANK.

FILE

The filename-string specifies the name of the new databank file in the host file system and this is stored in the data dictionary as the location of the databank file. If a file name is not specified, it will be the same as databank-name (and the databank will be created in the database home directory.) The filename-string may be represented as character literal, national character literal, or unicode character literal.

OPTION

The databank is created with the transaction and logging options as specified:

LOG

All operations on the databank are performed under transaction control. All transactions are logged, i.e. it will be possible to restore the databank from a backup.

TRANSACTION

All operations on the databank are performed under transaction control. No transactions are logged. (This databank option is assumed if one is not explicitly specified.)

WORK

All operations on the databank are performed without transaction control (even if they are requested within a transaction) and they are not logged. Set operations (DELETE, UPDATE and INSERT on several rows) which are interrupted, will not be rolled back. All secondary indexes created 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, see UPDATE STATISTICS for information on how to ensure that secondary indexes are consistent.

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 the 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

CREATE DATABANK requires that the current ident has DATABANK privilege.

The databank name must not be the same as that of an existing databank or shadow.

The databank must be created with either the TRANSACTION or LOG option if any of the following are true:

the databank is to be shadowed

the databank will be used to store tables defined with foreign or unique keys

the databank will be used to store tables that are referenced in a foreign key context

the databank will be used to store tables holding UNIQUE indexes

the databank contains tables that will accept updates in their primary key column(s)

Notes

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.

The creator of the databank is granted TABLE privilege on the new databank, with the WITH GRANT OPTION.

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.

Example

CREATE DATABANK mimer_store SET FILE 'mimer_store.dbf',

   FILESIZE 10M, GOALSIZE 10M, MAXSIZE 100M,

   OPTION LOG;

Standard Compliance

Standard

Compliance

Comments

 

Mimer SQL extension

The CREATE DATABANK statement is a Mimer SQL extension.