Interbase 2007

At the recent EuroDevCon 2006 in Frankfurt, Germany, the Developer Tools Group of Borland Software announced InterBase® 2007, a significant new version of the multi-platform, embeddable database that is used in hundreds of thousands of applications world wide. 

For readers of this magazine not familiar with InterBase, the cross-platform SQL 92 compliant database has a small footprint, is easy to install and configure, with a minimum administration overhead, self-tuning, automatic crash recovery and high performance. This makes InterBase ideal for both embedded and business-critical enterprise server applications.  Main themes for the InterBase 2007 release (codename TrailBlazer) are additional durability, improved performance and extended support for international use. More specific this translates to a number of new features, including

• Journaling and Journal Archiving improves VLDB management, OLTP performance, and facilitates disaster recovery
• Point-in-time-recovery allows journal based recovery based on timestamps for greater recovery flexibility
• Online Dump, provides backup for seamless incremental backup
• Batch Updates allow sending multiple SQL statements and reduce the network traffic resulting in improved performance, especially in LAN and WAN environments
• Extended International character support via Unicode UCS-2 and UTF-8

Increased durability and new disaster recovery options

Let’s look at the key enhancements of this new release and start with the increased durability for data protection and new disaster recovery options. Up to this release, the backup support in InterBase (using GBAK) only allowed you to do a full backup; all the rows of the database are fetched under transaction control and written to the backup files. This is referred to as a logical backup. Using this method provides many useful side-effects; a restore operation results in rebalanced indices and packed data pages, as well as resetting the database's next transaction ID. However, backing up large databases can take a very long time, made even longer when the database load is very heavy.

InterBase 2007 now also allows you to make an incremental backup, also called an online dump

To improve this situation, InterBase 2007 now also allows you to make an incremental backup, also called an online dump. Because of the way this feature is implemented, this mechanism is referred to as a physical backup, since it takes the physical pages of the database and writes these to a "dump" file. This output dump file represents the on-disk state of the database as of the moment the online dump was started. The question is what is the best backup strategy? Actually, both backup methods can be used Since the incremental backup makes it possible to fine-tune your logical backup, you can use the incremental backup as a staging area from which a logical GBAK can be performed, so that your production database is not affected. It will also allow you to run a database validation because validation requires exclusive database access, which cannot be obtained on a production database unless that database is shut down. The online dump files are marked as a read-only InterBase database, meaning that it can be accessed by read-only database applications. You can convert an online dump to read-write database, but this also changes the status from an online dump to a standalone database, so it cannot be used as a target for future incremental backups.

On to the new disaster recovery options with log-based journaling for short-term recovery and journal archiving for long-term recovery in the event of a system failure. First, let’s look at the log-based journal. The CREATE JOURNAL statement creates a journal file that is used to capture all the changes made to the database. This journal file I/O is performed using InterBase's careful write strategy and is always synchronous and cannot be altered. All transaction changes are safely recorded on durable storage before the transaction is committed. This guarantees the ACID properties of a transaction (the database industry standards for Atomicity, Consistency, Isolation, and Durability). The write operations on the database can now be done asynchronously. Using asynchronous I/O for database writes allows the operating system to optimize file I/O, such as by writing consecutive pages together, or by using scatter/gather techniques that write consecutive pages in discontinuous page buffers. This implies that database pages can be written back to the database in any order after their changes have been recorded in the journal.

You can also add Journal Archives to your disaster recovery strategy

You can also add Journal Archives to your disaster recovery strategy. A journal archive is the set of destination directories that will hold the current set of journal files for a particular database. The purpose of the Journal archive is to support long-term database recovery. This feature provides for disaster recovery in the event a database becomes unavailable due to hardware or software failures that may make the primary database permanently inaccessible.

The journal archive does not automatically copy journal files or perform online database dumps. There are no DDL clauses to declaratively specify when to backup journals to the journal archive. It is similar to logical database backup, GBAK, in that a separate utility must be run to effect the archiving of an archive.

The DDL syntax for creating a journal archive is:

CREATE JOURNAL ARCHIVE []

When using the CREATE JOURNAL ARCHIVE statement without specifying a location, the default location is used where the journal files are located. When an InterBase checkpoint occurs (the moment that InterBase writes the changes from the journal to the database) the journal files are no longer deleted, they are now archived to facilitate long term recovery.

Archived database dumps represents the starting point from which long-term database recovery is initiated. A set of archive journal files will be applied to a copy of the archive database in the same way that local journal files are applied to a production database during short-term recovery. Optionally, an InterBase timestamp can be specified (-until ) to indicate a point-in-time until which the journal files will be applied. This option is very interesting because it allows you to look at your data at a specific point in time, without disturbing the users on the production database.

To archive a database:

gbak -archive_database

To archive local journal files:

gbak -archive_journals

To recover a database (optionally to a point-in-time):

gbak -archive_recover [-until ]
 

Although this feature introduces new options, remember that the end goal is to provide point-in-time disaster recovery using the CREATE JOURNAL ARCHIVE statement to archive time-consistent database dumps and journal files.

Batch updates, CLOBs and UNICODE.

Batch updates allow you to send a group of SQL statements to a server in a single unit. Grouping SQL statements into batches reduces the amount of network traffic between the client and the database server. This results in improved performance, especially in LAN and WAN environments. You can send multiple INSERT, UPDATE, and DELETE statements to the server in a single batch update. In response, the server returns an array of ULONG values that reflect the number of affected rows per statement. In ISQL, SQL statements to be executed in batch mode must be surrounded by the new BATCH START and BATCH EXEXCUTE commands. For example:

BATCH START;
INSERT INTO t1(f1, f2) VALUES (0,1);
UPDATE t1 SET f1=1 WHERE f2=1;
BATCH EXECUTE;

InterBase 2007 also supports new SQL syntax that allows you to use BLOBs and VARCHAR data interchangeably. With BLOB SUB_TYPE 1, the BLOB is considered to have a character type, essentially making the BLOB a CLOB data type.

InterBase now also supports international deployments with 16-bit UNICODE_BE and UNICODE_LE as server character sets. A client can use the UTF8 (or other native) client character set to connect with a UNICODE database.

Enhanced Delphi support for InterBase

Of course most of these new features are also exposed to the developer with the IBX components. IBX now supports multi instances, a feature introduced in InterBase 7.5, the new incremental backup method, batch updates and journaling.

The InterBase 2007 enhancements are welcome additions to an already impressive feature list. Looking beyond InterBase 2007, a lot of interesting features can be expected. If you’re interested, visit the Borland Developer Network and look at the published roadmap for InterBase.