Creating a Database - Firebird

You can create a database interactively in isql. Some other database administration tools can meet the API requirements enumerated in this section and let you create databases interactively, while others require a script.

In any case, it is preferable to use a data definition file (DDL script) because it provides an easy way to “replay” your statements if a statement fails. It is easier to start over from a source file than to retype interactive SQL statements.

Dialect

Firebird creates a dialect 3 database by default. If you wish to create a dialect 1 database, the first statement in the script (or the prior action in your admin tool) should be SET SQL DIALECT 1;

The next statement—or the first, for a dialect 3 database—must be the CREATE DATABASE or CREATE SCHEMA statement, using the following syntax:

“DATABASE” or “SCHEMA”?

CREATE DATABASE and CREATE SCHEMA are the same statement. It is merely a question of preference which you use.

Mandatory and Optional Attributes

The only mandatory attribute for the CREATE statement is the file specification—the name of the primary database file and the filesystem path to its location.

Database Path and Name

The file specification must be a fully qualified, absolute path to the file. The path must be in a valid format for the operating system platform. For POSIX:

CREATE DATABASE '/opt/databases/mydatabase.fdb'

For Win32:

CREATE SCHEMA 'd:\databases\mydatabase.fdb'

You can use either forward slashes (/) or backslashes (\) as directory separators. Firebird automatically converts either type of slash to the appropriate type for the server operating system.

The enclosing single quotes for file_specification are not optional. All elements of the file specification are case sensitive on POSIX platforms.

Creating a Database Remotely

When creating a database from a client workstation, or locally on Linux Superserver, either interactively or using a script, you must include the host name: For POSIX:

CREATE DATABASE 'myserver:/opt/databases/mydatabase.fdb'

For Linux SS Local, as previously, or

CREATE DATABASE 'localhost:/opt/databases/mydatabase.fdb'

For Win32:

CREATE SCHEMA 'NTServer:d:\databases\mydatabase.fdb'

Ownership

If you are logged in as SYSDBA, then SYSDBA will own the new database unless you include the clause specifying the USER and PASSWORD. Although it is optional to designate an owner, it is highly desirable to do so. However, for security reasons, you will probably wish to remove the user’s password from the script before archiving it with other system documentation.

CREATE DATABASE '/opt/databases/mydatabase.fdb' USER 'ADMINUSR' PASSWORD 'yyuryyub';

Page size

The optional PAGE_SIZE attribute is expressed in bytes. If you omit it, it will default to 4096 bytes with isql. Some other tools apply their own defaults, so there is a strong argument for specifying it explicitly in the script. The page size can be 1024, 2048, 4096, 8192, or 16384. Any other numbers will be resolved back to the next lowest number in this range. For example, if you specify 3072, Firebird will create a database with a page size of 2048.

Factors Influencing Choice of Page Size

Choosing a page size is not a question of applying some “rule.” It will do no harm to begin with the default size of 4K. When the time comes to tune the database for performance improvements, you can experiment by backing up the database and restoring it with different page sizes.

The page size you choose can benefit performance or affect it adversely, according to a number of factors having mostly to do with the structures and usage of the most frequently accessed tables. Each database page will be filled to about 80 percent of its capacity, so think in terms of an actual page size that is around 125 percent of the size you determine to be the minimum.

The row size of the most frequently accessed tables may have an effect. A record structure that is too large to fit on a single page requires more than one page fetch to read or write to it, so access can be optimized by choosing a page size that can comfortably accommodate one row or simple row multiples of these high-volume tables.

The number of rows that your main tables can be predicted to accommodate over time may have an influence. If multiple rows can be accommodated in a single page, a larger page size may reduce the overall tally of data and index pages that need to be read for an operation.

Default Character Set

This is strongly recommended unless all—or nearly all—of your text data will be in U.S. ASCII.

Getting Information About the Database

Once you have created and committed the database, you can display its details in isql using the SHOW DATABASE command:

Sweep Interval and Transactions

For information about sweeping and sweep interval, refer to the section “Database ‘Hygiene’” later in this chapter. The values of the oldest (“oldest interesting”), oldest active, and next transactions are important for performance and server behavior.

Forced Writes

Forced writes is synonymous with synchronous writes. On platforms that support asynchronous writes, Firebird databases are created with forced writes enabled by default. The phrase “disabling forced writes” means switching the write behavior from synchronous to asynchronous.

  • With forced writes enabled, new records, new record versions, and deletions are physically written to disk immediately upon posting or, at the latest, upon committing.
  • Asynchronous writes cause new and changed data to be withheld in the file -system cache, relying on the flushing behavior of the operating system to make them permanent on disk.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics