You can use aliases instead of the full path to the primary database file. Aliases are defined in the databases.conf file in the following format:

alias = filepath

If you create a database on a remote server, you need to specify the remote server specification. The remote server specification depends on the protocol being used. If you use the TCP/IP protocol to create a database, the primary file specification should look like this:

host[/{port|service}]:{filepath | db_alias}

Firebird also has a unified URL-like syntax for the remote server specification. In this syntax, the first part specifies the name of the protocol, then a host name or IP address, port number, and path of the primary database file, or an alias.

The following values can be specified as the protocol:

inet

TCP/IP (first tries to connect using the IPv6 protocol, if it fails, then IPv4)

inet4

TCP/IP v4

inet6

TCP/IP v6

xnet

(Windows-only) local protocol (does not include a host, port and service name)

<protocol>://[host[:{port | service}]/]{filepath | db_alias}

If host is an IPv6 address, it must be enclosed in square brackets ([]), because a bare colon (:) is used as a port separator.

USER and PASSWORD

The username and the password of an existing user in the security database (security6.fdb or whatever is configured in the SecurityDatabase configuration). The user creating the database will become its owner, if the OWNER clause is not specified. This will be important when considering database and object privileges.

You do not have to specify the username and password if the ISC_USER and ISC_PASSWORD environment variables are set, or if trusted authentication is used.

Firebird Embedded

For Firebird Embedded, USER is optional and defaults to your OS user. The user does not need to exist in a security database. PASSWORD is optional, and is ignored if specified.

ROLE

The name of the role (usually RDB$ADMIN), which will be taken into account when creating the database. The role must be assigned to the user in the applicable security database.

OWNER

The owner of the database. If not specified, the user creating the database becomes its owner. This user does not need to exist in a security database.

PAGE_SIZE

The desired database page size. If you specify a database page size less than 8,192, it will be automatically rounded up to 8,192. Other values not equal to either 8,192, 16,384 or 32,768 will be changed to the closest smaller supported value. If the page size is not specified, a value of 8,192 is used.

ⓘ

Bigger Isn’t Always Better.

Larger page sizes can fit more records on a single page, have wider indexes, and more indexes, but they will also waste more space for blobs (compare the wasted space of a 3KB blob on page size 8192 with one on 32768: +/- 5KB vs +/- 29KB), and increase memory consumption of the page cache.

SET NAMES

The character set of the connection available after the database is successfully created. The character set NONE is used by default. The character set name can optionally be qualified with the SYSTEM schema. Notice that the character set — including the optional schema — should be enclosed in a pair of apostrophes (single quotes).

DEFAULT CHARACTER SET

The default character set for character data types. The character set NONE is used by default. The default will be used for the entire database except where the default is overridden on a schema, or an alternative character set is used explicitly for a field, domain, variable, cast expression, etc.

COLLATION

The default collation for the default character set. It is not possible to override the default collation on a schema, as this is a property of the character set itself.

The character set and collation name can optionally be qualified with the SYSTEM schema¹.

DIFFERENCE FILE

The path and name for the file delta that stores any mutations to the database file after it has been switched to the copy-safe mode by the ALTER DATABASE BEGIN BACKUP statement. For the detailed description of this clause, see Section 5.1.2, “ALTER DATABASE”.

Databases are created in Dialect 3 by default. For the database to be created in Dialect 1, you will need to execute the statement SET SQL DIALECT 1 from script or the client application, e.g. in isql, before the CREATE DATABASE statement.

The CREATE DATABASE statement can be executed by:

• Administrators

• Users with the CREATE DATABASE privilege

1. Creating a database in Windows, located on disk D with a page size of 16,384. The owner of the database will be the user wizard. The database will be in Dialect 1, and will use WIN1251 as its default character set.

   SET SQL DIALECT 1;
   CREATE DATABASE 'D:\test.fdb'
   USER 'wizard' PASSWORD 'player'
   PAGE_SIZE = 16384 DEFAULT CHARACTER SET WIN1251;

2. Creating a database in the Linux operating system with a page size of 8,192 (default). The owner of the database will be the user WIZARD. The database will be in Dialect 3 and will use UTF8 as its default character set, with UNICODE_CI_AI as the default collation.

   CREATE DATABASE '/home/firebird/test.fdb'
   USER 'wizard' PASSWORD 'player'
   DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;

3. Creating a database on the remote server baseserver with the path specified in the alias test that has been defined previously in the file databases.conf. The TCP/IP protocol is used. The owner of the database will be the user WIZARD. The database will be in Dialect 3 and will use UTF8 as its default character set.

   CREATE DATABASE 'baseserver:test'
   USER 'wizard' PASSWORD 'player'
   DEFAULT CHARACTER SET UTF8;

4. Creating a database and specifying an alternative owner. The owner of the database will be the user ALEX.

   CREATE DATABASE 'baseserver:test'
   USER wizard PASSWORD 'player' OWNER alex
   DEFAULT CHARACTER SET UTF8;

See alsoSection 5.1.2, “ALTER DATABASE”, Section 5.1.3, “DROP DATABASE”

The ALTER DATABASE statement can be executed by:

• Administrators

• Users with the ALTER DATABASE privilege

ADD DIFFERENCE FILE

Configures the filepath of the difference file (or, delta file) that stores any mutations to the database whenever it is switched to the copy-safe mode. This clause does not add a file, but it configures filepath of the delta file when the database is in copy-safe mode. To change the existing setting, you should delete the previously specified description of the delta file using the DROP DIFFERENCE FILE clause before specifying the new description of the delta file. If the filepath of the delta file is not configured, the file will have the same path and name as the database, but with the .delta file extension.

⚠

Caution

If only a filename is specified, the delta file will be created in the current directory of the server. On Windows, this will be the system directory — a very unwise location to store volatile user files and contrary to Windows file system rules.

DROP DIFFERENCE FILE

Deletes the current difference file configuration. This does not delete a file, but DROP DIFFERENCE FILE clears (resets) the filepath of the delta file from the database header. Next time the database is switched to the copy-safe mode, the default value will be used (i.e. the same path and name as those of the database, but with the .delta extension).

BEGIN BACKUP

Switches the database to the copy-safe mode. ALTER DATABASE with this clause freezes the main database file, making it possible to back it up safely using file system tools, even if users are connected and performing operations with data. Until the backup state of the database is reverted to NORMAL, all changes made to the database will be written to the difference file.

☝

Important

Despite its name, the ALTER DATABASE BEGIN BACKUP statement does not start a backup process, but only freezes the database, to create the conditions for doing a task that requires the database file to be read-only temporarily.

END BACKUP

Switches the database from the copy-safe mode to the normal mode. A statement with this clause merges the difference file with the main database file and restores the normal operation of the database. Once the END BACKUP process starts, the conditions no longer exist for creating safe backups by means of file system tools.

🛑

Warning

Making a safe backup with the gbak utility remains possible at all times, although it is not recommended running gbak while the database is in LOCKED or MERGE state.

SET DEFAULT CHARACTER SET

Changes the default character set of the database. The character set name can optionally be qualified with the SYSTEM schema¹. This change does not affect existing data or columns, or schemas with an explicit default character set. The new default character set will only be used in subsequent DDL commands.

To modify the default collation, use ALTER CHARACTER SET on the default character set of the database.

ENCRYPT WITH

See Encrypting a Database in the Security chapter.

DECRYPT

See Decrypting a Database in the Security chapter.

SET LINGER TO

Sets the linger-delay. The linger-delay applies only to Firebird SuperServer, and is the number of seconds the server keeps a database file (and its caches) open after the last connection to that database was closed. This can help to improve performance at low cost, when the database is opened and closed frequently, by keeping resources warm for the next connection.

☞

Tip

This mode can be useful for web applications — without a connection pool — where connections to the database usually live for a very short time.

🛑

Warning

The SET LINGER TO and DROP LINGER clauses can be combined in a single statement, but the last clause wins. For example, ALTER DATABASE SET LINGER TO 5 DROP LINGER will set the linger-delay to 0 (no linger), while ALTER DATABASE DROP LINGER SET LINGER to 5 will set the linger-delay to 5 seconds.

DROP LINGER

Drops the linger-delay (sets it to zero). Using DROP LINGER is equivalent to using SET LINGER TO 0.

ⓘ

Note

Dropping LINGER is not an ideal solution for the occasional need to turn it off for once-only operations where the server needs a forced shutdown. The gfix utility now has the -NoLinger switch, which will close the specified database immediately after the last attachment is gone, regardless of the LINGER setting in the database. The LINGER setting is retained and works normally the next time.

The same one-off override is also available through the Services API, using the tag isc_spb_prp_nolinger, e.g. (in one line):

fbsvcmgr host:service_mgr user sysdba password xxx
       action_properties dbname employee prp_nolinger

🛑

Warning

The DROP LINGER and SET LINGER TO clauses can be combined in a single statement, but the last clause wins.

SET DEFAULT SQL SECURITY

Specifies the default SQL SECURITY option to apply at runtime for objects without the SQL Security property set. See also SQL Security in chapter Security.

ENABLE PUBLICATION

Enables publication of this database for replication. Replication begins (or continues) with the next transaction started after this transaction commits.

DISABLE PUBLICATION

Disables publication of this database for replication. Replication is disabled immediately after commit.

EXCLUDE …​ FROM PUBLICATION

Excludes tables from publication. If the INCLUDE ALL TO PUBLICATION clause is used, all tables created afterward will also be replicated, unless overridden explicitly in the CREATE TABLE statement.

INCLUDE …​ TO PUBLICATION

Includes tables to publication. If the INCLUDE ALL TO PUBLICATION clause is used, all tables created afterward will also be replicated, unless overridden explicitly in the CREATE TABLE statement.

1. Specifying the path and name of the delta file:

   ALTER DATABASE
     ADD DIFFERENCE FILE 'D:\test.diff';

2. Deleting the description of the delta file:

   ALTER DATABASE
     DROP DIFFERENCE FILE;

3. Switching the database to the copy-safe mode:

   ALTER DATABASE
     BEGIN BACKUP;

4. Switching the database back from the copy-safe mode to the normal operation mode:

   ALTER DATABASE
     END BACKUP;

5. Changing the default character set for a database to WIN1251

   ALTER DATABASE
     SET DEFAULT CHARACTER SET WIN1252;

6. Setting a linger-delay of 30 seconds

   ALTER DATABASE
     SET LINGER TO 30;

7. Encrypting the database with a plugin called DbCrypt

   ALTER DATABASE
     ENCRYPT WITH DbCrypt;

8. Decrypting the database

   ALTER DATABASE
     DECRYPT;

See alsoSection 5.1.1, “CREATE DATABASE”, Section 5.1.3, “DROP DATABASE”

The DROP DATABASE statement can be executed by:

• Administrators

• Users with the DROP DATABASE privilege

Deleting the current database

DROP DATABASE;

See alsoSection 5.1.1, “CREATE DATABASE”, Section 5.1.2, “ALTER DATABASE”