IBConsole - How to Create a Database
To create a database and its components, InterBase uses an implementation of SQL which conforms to the ANSI SQL-89 entry-level standard and follows SQL-92 and SQL3 beta specifications for advanced features.
Building a database involves defining the data. For this purpose InterBase provides a set of statements called the Data Definition Language (DDL).
An InterBase database is a single file comprising all the metadata and data in the database. To create a new database for the Local InterBase Server, use Windows ISQL. Create a database in ISQL with an interactive command or with the CREATE DATABASE statement in an ISQL script file.
Prerequisites
Before creating the database, you should know:
- Where to create the database. Users who create databases need to know only the logical names of the available devices in order to allocate database storage. Only the system administrator needs to be concerned about physical storage (disks, disk partitions, operating system files).
- The tables that the database will contain.
- The record size of each table, which affects what database page size you choose. A record that is too large to fit on a single page requires more than one page fetch to read or write to it, so access could be faster if you increase the page size.
- How large you expect the database to grow. The number of records also affects the page size because the number of pages affects the depth of the index tree. Larger page size means fewer total pages. InterBase operates more efficiently with a shallow index tree.
- The number of users that will be accessing the database.
Although you can create, alter, and drop a database interactively, it is preferable to use a data definition filedata_def_file because it provides a record of the structure of the database. It is easier to modify a source file than it is to start over by retyping interactive SQL statements.
The following topics are included in this section:
- Using CREATE DATABASE
- Creating a Single-file database
- Creating a Multi-file Database
- Specifying User Name and Password
- Specifying Database Page Size
- Specifying the Default Character Set
Contents
Using CREATE DATABASE
CREATE DATABASE establishes a new database and populates its system tables, or metadata, which are the tables that describe the internal structure of the database. CREATE DATABASE must occur before creating database tables, views, and indexes.
CREATE DATABASE optionally allows you to do the following:
- Specify a user name and a password
- Change the default page size of the new database
- Specify a default character set for the database
- Add secondary files to expand the database
CREATE DATABASE must be the first statement in the data definition file. You cannot create a database directly from the ISQL command line.
In DSQL, CREATE DATABASE can only be executed with EXECUTE IMMEDIATE. The database handle and transaction name, if present, must be initialized to zero prior to use.
The syntax for CREATE DATABASE is:
CREATE {DATABASE | SCHEMA} "<filespec>"
[USER "username" [PASSWORD "password"]]
[PAGE_SIZE [=] int]
[LENGTH [=] int [PAGE[S]]]
[DEFAULT CHARACTER SET charset]
[<secondary_file>];
<secondary_file> = FILE "<filespec>" [<fileinfo>] [<secondary_file>]
<fileinfo> = LENGTH [=] int [PAGE[S]] | STARTING [AT [PAGE]] int [<fileinfo>]
The complete syntax of CREATE DATABASE is in the online SQL Reference.
Creating a Single-file Database
Although there are many optional parameters, CREATE DATABASE requires only one parameter, "<filespec>", which is the new database file specification. The file specification contains the device name, path name, and database name.
By default, a database is created as a single file, called the primary file. The following example creates a single-file database, named employee.gdb, in the current directory.
CREATE DATABASE "employee.gdb";
Specifying file size for a single-file database
You can optionally specify a file length, in pages, for the primary file. For example, the following statement creates a database that is stored in one 10,000-page long file:
CREATE DATABASE "employee.gdb" LENGTH 10000;
If the database grows larger than the specified file length, InterBase extends the primary file beyond the LENGTH limit until the disk space runs out. To avoid this, you can store a database in more than one file, called a secondary file.
- Note: Use LENGTH for the primary file only if defining a secondary file in the same statement.
Creating a Multi-file Database
A multi-file database consists of a primary file and one or more secondary files. You can create one or more secondary files to be used for overflow purposes only; you cannot specify what information goes into each file because InterBase handles this automatically. Each secondary file is typically assigned to a different disk than that of the main database. When the primary file fills up, InterBase allocates one of the secondary files that was created. When that secondary file fills up, another secondary file is allocated, and so on, until all of the secondary file allocations run out.
Whenever possible, the database should be created locally; create the database on the same machine where you are running ISQL. If the database is created locally, secondary file names can include a full file specification, including both host or node names, and a directory path to the location of the database file. If the database is created on a remote server, secondary file specifications cannot include a node name, as all secondary files must reside on the same node.
Specifying file size of a secondary file
Unlike primary files, when you define a secondary file, you must declare either a file length in pages, or a starting page number. The LENGTH parameter specifies a database file size in pages.
If you choose to describe page ranges in terms of length, list the files in the order in which they should be filled. The following example creates a database that is stored in four 10,000-page files, Starting with page 10,001, the files are filled in the order employee.gdb, employee.gd1, employee.gd2, and employee.gd3.
CREATE DATABASE "employee.gdb"
FILE "employee.gd1" STARTING AT PAGE 10001
LENGTH 10000 PAGES
FILE "employee.gd2"
LENGTH 10000 PAGES
FILE "employee.gd3"
LENGTH 10000 PAGES;
- Note: Because file-naming conventions are platform-specific, for the sake of simplicity, none of the examples provided include the device and path name portions of the file specification.
When the last secondary file fills up, InterBase automatically extends the file beyond the LENGTH limit until its disk space runs out. You can either specify secondary files when the database is defined, or add them later, as they become necessary, using ALTER DATABASE. Defining secondary files when a database is created immediately reserves disk space for the database.
Specifying the starting page number of a secondary file
If you do not declare a length for a secondary file, then you must specify a starting page number. STARTING AT PAGE specifies the beginning page number for a secondary file.
The primary file specification in a multi-file database does not need to include a length, but secondary file specifications must then include a starting page number. You can specify a combination of length and starting page numbers for secondary files.
InterBase overrides a secondary file length that is inconsistent with the starting page number. In the next example, the primary file is 10,000 pages long, but the first secondary file starts at page 5,000:
CREATE DATABASE "employee.gdb" LENGTH 10000
FILE "employee.gd1" STARTING AT PAGE 5000
LENGTH 10000 PAGES
FILE "employee.gd2"
LENGTH 10000 PAGES
FILE "employee.gd3";
InterBase generates a primary file that is 10,000 pages long, starting the first secondary file at page 10,001.
Specifying User Name and Password
For access to InterBase databases on a server, a valid user name and password is required. This is validated against the security database, ISC4.GDB. Every InterBase server comes with SYSDBA user and password “masterkey”. SYSDBA must authorize all other users on a server. For servers that require secure databases, it is strongly recommended that the database administrator change the “masterkey” password for SYSDBA as soon as possible and assign authorized users to the database. Passwords are restricted to 8 characters in length.
The following statement creates a database with a user name and password:
CREATE DATABASE "employee.gdb" USER "SALES" PASSWORD "mycode";
If you are using Windows ISQL or Server Manager, specify the user name and password in the appropriate dialog box. If you are using command-line ISQL, a user name and password can be specifed on the command line.
Specifying Database Page Size
You can optionally override the default page size of 1024 bytes for database pages by specifying a different PAGE_SIZE. PAGE_SIZE can be 1024, 2048, 4096, or 8192. The next statement creates a single-file database with a page size of 2048 bytes:
CREATE DATABASE "employee.gdb" PAGE_SIZE 2048;
When to increase page size
Increasing page size can improve performance for several reasons:
- Indexes work faster because the depth of the index is kept to a minimum.
- Keeping large rows on a single page is more efficient. (A row that is too large to fit on a single page requires more than one page fetch to read or write to it.)
- BLOB data is stored and retrieved more efficiently when it fits on a single page. If an application typically stores large BLOB columns (between 1K and 2K), a page size of 2048 bytes is preferable to the default (1024).
If most transactions involve only a few rows of data, a smaller page size may be appropriate, since less data needs to be passed back and forth and less memory is used by the disk cache.
Changing page size for an existing database
To change a page size of an existing database, follow these steps:
- Back up the database.
- Restore the database using the PAGE_SIZE option to specify a new page size.
Specifying the Default Character Set
DEFAULT CHARACTER SET allows you to optionally set the default character set for the database. The character set determines:
- What characters can be used in CHAR, VARCHAR, and BLOB text columns.
- The default collation order that is used in sorting a column.
Choosing a default character set is useful for all databases, even those where international use is not an issue. Choice of character set determines if transliteration among character sets is possible. For example, the following statement creates a database that uses the ISO8859_1 character set, typically used in Europe to support European languages:
CREATE DATABASE "employee.gdb"
DEFAULT CHARACTER SET "ISO8859_1";
Using CHARACTER SET NONE
If you do not specify a default character set, the character set defaults to NONE. Using CHARACTER SET NONE means that there is no character set assumption for columns; data is stored and retrieved just as you originally entered it. You can load any character set into a column defined with NONE, but you cannot load that same data into another column that has been defined with a different character set. No transliteration will be performed between the source and destination character sets, so in most cases, errors will occur during the attempted assignment.
For example:
CREATE TABLE MYDATA (PART_NUMBER CHARACTER(30) CHARACTER SET NONE);
SET NAMES LATIN1;
INSERT INTO MYDATA (PART_NUMBER) VALUES ("à");
SET NAMES DOS437;
SELECT * FROM MYDATA;
The data ("à") is returned just as it was entered, without the à being transliterated from the input character (LATIN1) to the output character (DOS437). If the column had been set to anything other than NONE, the transliteration would have occurred.