Primary InterBase Features

From InterBase

Go Up to Introduction to Operations

InterBase offers all the benefits of a full-featured RDBMS. The following table lists some of the key InterBase features:

Feature Description

Network protocol support

  • All platforms of InterBase support TCP/IP
  • InterBase servers and clients for Windows support NetBEUI/named pipes

SQL-92 entry-level conformance

ANSI standard SQL, available through an Interactive SQL tool and Embarcadero desktop applications.

Simultaneous access to multiple databases

One application can access many databases at the same time.

multigenerational
architecture

Server maintains older versions of records (as needed) so that transactions can see a consistent view of data.

Optimistic row-level locking

Server locks only the individual records that a client updates, instead of locking an entire database page.

Query optimization

Server optimizes queries automatically, or you can manually specify a query plan.

Blob data type and Blob filters

Dynamically sizeable data types that can contain unformatted data such as graphics and text.

Declarative referential integrity

Automatic enforcement of cross-table relationships (between ­FOREIGN and PRIMARY KEY constraints).

Stored procedures

Programmatic elements in the database for advanced queries and data manipulation actions.

Triggers

Self-contained program modules that are activated when data in a specific table is inserted, updated, or deleted.

Event alerters

Messages passed from the database to an application; enables applications to receive asynchronous notification of database changes.

Updatable views

Views can reflect data changes as they occur.

User-defined functions (UDFs)

Program modules that run on the server.

Outer joins

Relational construct between two tables that enables complex operations.

Explicit transaction
management

Full control of transaction start, commit, and rollback, including named transactions.

Concurrent multiple application access to data.

One client reading a table does not block others from it.

multidimensional arrays

Column data types arranged in an indexed list of elements.

Automatic two-phase commit

Multi-database transactions check that changes to all databases happen before committing (InterBase Server only).

InterBase API

Functions that enable applications to construct SQL/DSQL statements directly to the InterBase engine and receive results back.

gpre

Preprocessor for converting embedded SQL/DSQL statements and variables into a format that can be read by a host-language compiler; included with the InterBase server license.

IBConsole

Windows tool for data definition, query, database backup, restoration, maintenance, and security.

isql

Command-line version of the InterBase interactive SQL tool; can be used instead of IBConsole for interactive queries.

Command-line database administrator utilities

Command-line version of the InterBase database administration tools; can be used instead of IBConsole.

Header files

Files included at the beginning of application programs that define InterBase data types and function calls.

Example make files

Files that demonstrate how to invoke the makefiles to compile and link InterBase applications.

Example programs

C programs, ready to compile and link, which you can use to query standard InterBase example databases on the server.

Message file

interbase.msg, containing messages presented to the user.

SQL SUPPORT

InterBase conforms to entry-level SQL-92 requirements. It supports declarative referential integrity with cascading operations, updatable views, and outer joins. InterBase Server provides libraries that support development of embedded SQL and DSQL client applications. On all InterBase platforms, client applications can be written to the InterBase API, a library of functions with which to send requests for database operations to the server.

InterBase also supports extended SQL features, some of which anticipate SQL99 extensions to the SQL standard. These include stored procedures, triggers, SQL roles, and segmented Blob support.

For information on SQL, see the Language Reference Guide.

Multiuser Database Access

InterBase enables many client applications to access a single database simultaneously. A client applications can also access the multiple databases simultaneously. SQL triggers can notify client applications when specific database events occur, such as insertions or deletions.

You can write user-defined functions (UDFs) and store them in an InterBase database, where they are accessible to all client applications accessing the database.

Transaction Management

Client applications can start multiple simultaneous transactions. InterBase provides full and explicit transaction control for starting, committing, and rolling back transactions. The statements and functions that control starting a transaction also control transaction behavior.

InterBase transactions can be isolated from changes made by other concurrent transactions. For the life of these transactions, the database appears to be unchanged except for the changes made by the transaction. Records deleted by another transaction exist, newly stored records do not appear to exist, and updated records remain in the original state.

For information on transaction management, see the Embedded SQL Guide.

Multigenerational Architecture

InterBase provides expedient handling of time-critical transactions through support of data concurrency and consistency in mixed use – query and update – environments. InterBase uses a multigenerational architecture, which creates and stores multiple versions of each data record. By creating a new version of a record, InterBase allows all clients to read a version of any record at any time, even if another user is changing that record. InterBase also uses transactions to isolate groups of database changes from other changes.

Optimistic Row-level Locking

Optimistic locks are applied only when a client actually updates data, instead of at the beginning of a transaction. InterBase uses optimistic locking technology to provide greater throughput of database operations for clients.

InterBase implements true row-level locks, to restrict changes only to the records of the database that a client changes; this is distinct from page-level locks, which restrict any arbitrary data that is stored physically nearby in the database. Row-level locks permit multiple clients to update data that is in the same table without coming into conflict. This results in greater throughput and less serialization of database operations.

InterBase also provides options for pessimistic table-level locking. See the Embedded SQL Guide for details.

Database Administration

InterBase provides both GUI and command-line tools for managing databases and servers. You can perform database administration on databases residing on Local InterBase or InterBase Server with IBConsole, a Windows application running on a client PC. You can also use command-line database administration utilities on the server.

IBConsole and command-line tools enable the database administrator to:

  • Manage server security
  • Back up and restore a database
  • Perform database maintenance
  • View database and lock manager statistics

You can find more information on server security later in this chapter, and later chapters describe individual tasks you can accomplish with IBConsole and the command-line tools.

Managing Server Security

InterBase maintains a list of user names and passwords in a security database. The security database allows clients to connect to an InterBase database on a server if a user name and password supplied by the client match a valid user name and password combination in the InterBase security database (admin.ib by default), on the server.

Note:
Starting with version XE7 InterBase implements stronger password protection on InterBase databases. See Implementing Stronger Password Protection.

You can add and delete user names and modify a user’s parameters, such as password and user ID.

For information about managing server security, see Database User Management.

Backing Up and Restoring Databases

You can backup and restore a database using IBConsole or command-line gbak. A backup can run concurrently with other processes accessing the database because it does not require exclusive access to the database.

Database backup and restoration can also be used for:

  • Erasing obsolete versions of database records
  • Changing the database page size
  • Changing the database from single-file to multifile
  • Transferring a database from one operating system to another
  • Backing up only a database’s metadata to recreate an empty database

For information about database backup and recovery, see About InterBase backup and restore options.

Maintaining a Database

You can prepare a database for shutdown and perform database maintenance using either IBConsole or the command-line utilities. If a database incurs minor problems, such as an operating system write error, these tools enable you to sweep a database without taking the database off-line.

Some of the tasks that are part of database maintenance are:

  • Sweeping a database
  • Shutting down the database to provide exclusive access to it
  • Validating table fragments
  • Preparing a corrupt database for backup
  • Resolving transactions “in limbo” from a two-phase commit
  • Validating and repairing the database structure

For information about database maintenance, see Database Configuration and Maintenance.

Viewing Statistics

You can monitor the status of a database by viewing statistics from the database header page, and an analysis of tables and indexes. For more information, see Database Statistics and Connection Monitoring.

Advance To: