Administration tasks

The Firebird DBA really does have an easy job as their are no administration tasks which have to be performed! And when the application is programmed well, no maintenance is necessary at all! However databases do occasionally encounter problems, usually due to poor programming. So here are a few things the Firebird DBA should be aware of.

Downloading and installing the various Firebird versions

Please refer to the IBExpert documentation chapter, Download and Install Firebird.

Automating the database backup and restore

It is not necessary for users to logout during a Firebird backup. A consistent backup is performed, regardless of whether users are working on the database at the time. A database backup can be performed using the IBExpert Services menu item, Backup Database, or the Firebird command-line tool, GBAK.

For obvious reasons, should you need to perform a database restore, it is vital that no users are working on the database during the restore. A database restore can be performed using the IBExpert Services menu item, Restore Database, or the Firebird command-line tool, GBAK. Please note that if you run the GBAK restore in verbose mode, it can take an awful long time.

When performing a backup only the index definitions are stored, then when the database is restored, data is restored into the tables, and right at the end the indices are newly generated. A backup and restore also resets all the transaction parameters, that can be viewed in the Database Statistics.

Alway backup onto another machine. Check that the file stamp is different and do a test restore regularly to confirm that the backup files are fine.

It is possible to automate the database backup in a batch file in the Windows Scheduled Tasks. Although a great tool for automating your backups and restores is the IBExpert Server Tool, IBExpertBackupRestore. This enables you to automate backups and restores, and can send you an email to inform you of any errors or confirming that there were no errors.

Garbage collection

Garbage collection is the ongoing cleaning of the database and is performed in the background around the clock. This constantly reorganizes the memory space used by the database. If you don't clean up, database performance will slowly but surely degrade. Garbage collection works for both data pages and index pages (if you have created 100,000 new data sets and deleted another 100,000 data sets, an index won't help much, if the 100,000 deleted pages are still there and being searched through.

The Firebird garbage collector does not require administrative commands or manual maintenance as certain other database environments do. Whether the garbage collector works efficiently or not depends on how the application works.

For further information regarding garbage collection, please refer to Garbage collection.

Setting up protocols

Your database is full of information. Sometimes it is helpful to log certain aspects of the information manipulation (selects, inserts, update, deletes), to gain an insight what is really happening in your database.

Manual: Create a trigger on each table where you want to have a protocol
Almost automatically: take a look at the script db2.sql found in the IBExpertDemoDB folder, which creates a fully functional transaction log just by executing the procedure INITLOG.
Automatically: Open the table you wish to log in the IBExpert Table Editor and click on the Logging page. Confirm the generation of IBE$ System tables if required, and then select Prepare table for logging.
Other tools with advanced log functions can be found in the IBExpert Tools menu item, Log Manager.

Administrating users

Only the SYSDBA can create, alter and delete user accounts as they are created for all databases on the server. Rights are then granted for individual users, roles or database objects for the individual databases, where the SYSDBA and the database owner can grant users the right to grants permissions to other users. Further information regarding this subject can be found in the IBExpert documentation chapters, User Manager, Grant Manager and Password.

Setting up and testing the ODBC driver

If you need an ODBC driver, it can be downloaded from https://www.firebirdsql.org. Then use the Windows menu: Settings / System Control / Administration / Data Source and select fbodbc. This now allows you to access Firebird data from non-Firebird applications such as, for example, OpenOffice Base.

Should you wish to import data from other data sources, please refer to the IBExpert Tools menu item, ODBC Viewer and the IBEBlock chapter, ODBC access

Importing and exporting data

The Firebird core only offers import and export using external files, which requires a setting in and restarting the server.

The files can be defined by declaring a table:

 create table external file

This function is extremely quick; 100,000 data sets can be imported or exported every second. It is however limited for certain datatypes, particularly those of a variable length, such as blobs. The best solution is to define the table using the above instruction, and defining as far as possible all fields as CHAR.

You can alternatively use the Firebird ODBC driver with any ODBC-capable tool, the IBExpert ODBC Viewer, or IBExpert's IBEBlock, ODBC support. You can even automate your import/export using IBEBlock.

Results of SQL queries can be exported from the IBExpert SQL Editor.

Transferring data to a new table or another database

When you create a new table in IBExpert using the insert into command, for example:

 insert into employee_bak
 select * from employee

a table is created with the same structure as the original, with field names and data etc., but not the primary and foreign keys, constraints etc. etc. When you are creating a table based on this command and you ignore this, you will see the table warning in the SP/Triggers/Views Analyzer:

For a more detailed explanation of this feature, please refer to the IBExpert documentation, SQL Editor - Creating a table from query results.

To transfer data from one registered database to another, open the SQL Editor for the db1 database, and

 select * from customer

To transfer this data to another database, employee.fdb (with the IBExpert alias name, EMPLOYEE_2_1) simply write the following SQL when connected to the source (db1) database:

 insert into [employee_2_1].customerimport
 select * from customer

If the table doesn't already exist, IBExpert will ask if you wish to create it:

and you have copied 10,000 customer records into another database without having to worry about first creating a new table of the same structure or performing some complicated export routine!