Database configuration

<< Databases | Firebird SQL Server 2.x Administration Handbook | Backup >>

Database configuration

  1. Editing mode
    1. gfix: general syntax
    2. Enable forced writes (no buffering)
    3. Disable forced writes (Buffering)
  2. Database sweeps
    1. Specifying the sweep interval
    2. Deactivating the automatic sweep
    3. Forcing a sweep
  3. SQL dialect
  4. Multi-file databases
  5. Database shutdown
    1. Shutdown
    2. Shutdown from NORMAL to SINGLE
    3. Restart

Database configuration

Editing mode

Editing operations on the database file can be buffered. Buffering is quicker, but can be unreliable in the case of a crash. This should therefore be disabled on productive systems.

Buffering is specified in gfix or using an administration tool such an IBExpert. The user must be SYSDBA or the database owner.

gfix: general syntax

 gfix <datenbank> -user <benutzername> -password <passwort>
    -write {sync|async}

Enable forced writes (no buffering)

 gfix c:\mydb.fdb -user SYSDBA -password masterkey -write sync

Disable forced writes (buffering)

 gfix c:\mydb.fdb -user SYSDBA -password masterkey -write async

back to top of page

Database sweeps

Firebird performs a garbage collection ("sweep") at irregular intervals, cleaning up open transactions. This is necessary due to Firebird and InterBase's multi-generational architecture. This stores certain data sets in a series of generations, to allow all open transactions a consistent data view.

An automatic sweep is executed when a certain number of incomplete transactions has been reached. This number in the "sweep interval". The sweep interval can be specified at any wished number.

A sweep interval of 0 (zero) switches off automatic sweeping.

A sweep can also be executed at a specified time (e.g. at night).

Only the SYSDBA or the database owner may specify the sweep interval.

Specifying the sweep interval

Specification of the sweep interval at 20,000 transactions: 

 gfix c:\test.fdb -user SYSDBA -password masterkey -housekeeping 20000

Deactivating the automatic sweep

 gfix c:\test.fdb -user SYSDBA -password masterkey -housekeeping 0

Forcing a sweep

 gfix c:\test.fdb -user SYSDBA -password masterkey -sweep

See also:
Firebird for the database expert: Episode 4 - OAT, OIT and Sweep

back to top of page

SQL dialect

Firebird emanates from Borland InterBase. For legacy reasons two SQL dialects, with marginal differences, are supported (Dialect 1, Dialect 3).

Dialect 3 is the preferred choice for new databases. This offers separate datatypes for DATE, TIME and TIMESTAMP (only TIMESTAMP is a combination comprising date and time).

Databases created by the CREATE DATABASE statement however have a default dialect 1. They need to be subsequently altered to dialect 3: 

 gfix c:\test.fdb -user SYSDBA -password masterkey -sql_dialect 3

The current specified SQL dialect for a database can be determined using ISQL: 

 isql
 SQL> connect mydb.fdb user SYSDBA password masterkey;
 SQL> show sql dialect;
         Client SQL dialect is set to: 3 and database SQL dialect is: 3

Multi-file databases

A database can be split across multiple files. However it is not possible to specify which parts of the database are stored in which file. As the old 4 GB limit (up to and including version InterBase 6.0), we do not consider a distribution across multiple files recommendable. Therefore this is not documented here any further.

Please refer to the InterBase® 6.0 Operations Guide or the relevant Firebird documentation.

See also:
Firebird Internals: Multiple file databases
Multi-file database

back to top of page

Database shutdown

A database can be in a variety of states:

  • NORMAL: The database is active and online: the normal state, allowing you to work with the database.
  • MULTI: Only connections from the SYSDBA and the database owner are allowed.
  • SINGLE: Only one single connection by the SYSDBA is allowed.
  • FULL: Exclusive shutdown: the database is completely offline, no connections are allowed. In this state the database file (.fdb file) can be accessed (e.g. copied).

gfix can be used to start or shutdown a database to these levels. So that connected users are not simply "thrown out" for the shutdown, there are various options to specify a certain shutdown time.

Shutdown

To shut down to the next level use the gfix option –shut, followed by the name of the level.

Using the option –force the number of seconds can be specified, that the service should wait, until all other users have disconnected. If any connections still exist following this period, they are automatically disconnected. Open transactions are rolled back.

Alternatively the options –attach can be used to specify a certain number of seconds that should be waited until all users have disconnected. Following this period if there are any users that have still not disconnected, the shutdown is aborted and an error message published.

Shutdown from NORMAL to SINGLE

 gfix-user sysdba -password masterkey localhost:mydb -shut single -force 0

The –force 0 option ensures here that all users except the SYSDBA are disconnected immediately (0 seconds waiting period).

Restart

To boot up use the –online option instead of –shut. The level name needs to be specified here as well. The options –force or –attach cannot be used here, as the restart begins immediately. 

 gfix -user sysdba -password masterkey localhost:mydb -online normal

See also:
Forced Writes
Database shutdown
Active users
Firebird Database Housekeeping Utility

back to top of page
<< Databases | Firebird SQL Server 2.x Administration Handbook | Backup >>