FirstSQL/J ORDBMS

Overview (What is FirstSQL/J?)

FirstSQL/J is a Relational DBMS with integrated Object capabilities. Its full name is Object/Relational Database Management System (ORDBMS).

The primary function of FirstSQL/J is to be a DBMS, to manage the database. FirstSQL/J provides standard interfaces to the database -- SQL as the data access language and JDBC as the client API (Application Program Interface).

As a running system, FirstSQL/J has 3 components:

Physical Database, containing all system and user data.
DBMS Engine, implementing SQL and managing the database structure.
Client Interface, APIs for the client, including JDBC.

FirstSQL/J Physical Database

The physical database contains all system and user data. It may reside on disk or in main memory. The database structure implements the Relational Model of Data. All user data is stored as column values in database tables.

The FirstSQL/J database also extends the Relational Model with support for objects in the database. It provides for storage of user-defined classes in the database and for storage of objects as values in database columns. Java is the implementation language for database classes.

A set of system tables in definition_schema contain the definitions for the tables, classes, etc. in the database. All client access to data in the database is through SQL.

FirstSQL/J DBMS Engine

Written in 100% Java for maximal portability, the FirstSQL/J DBMS is a high performance SQL engine. It supports SQL92, Intermediate Level. The SQL Optimizer is an advanced implementation that is fundamentally sound, without the arbitrary limitations, incomplete optimizations and uneven performance found in other systems.

The DBMS Engine features tight integration with Java classes in the database. It extends SQL with powerful and intuitive capabilities for database classes, providing Inheritance, Polymorphism and Encapsulation. Database classes can function as data types for table columns. The value of such columns is an object.

The FirstSQL/J engine has a small footprint in memory and on disk. It dynamically loads components as needed, further reducing memory usage. User class loading and instantiation also takes place on demand. The disk image of the engine (a jar file) can be reduced in size by removing components not needed in deployment -- for example, Database Class Support, DDL Support, Main Memory Support and, conversely, Disk Support.

FirstSQL/J will run on JDK 1.1 or higher.

Client Interface

The Client Interface component of FirstSQL/J supports all access by client applications to the DBMS and the Database. There are several APIs in the FirstSQL/J Client Interface, but the primary one is JDBC (Java Database Connectivity). JDBC is the standard interface to relational databases using SQL. The other FirstSQL APIs support administrative access to the DBMS.

The client APIs for FirstSQL/J are:

JDBC -- API to SQL DBMSs defined by the java.sql package in the standard Java API.
Adm -- FirstSQL/J native API for remote administration, monitoring and controlling the server and shutting down the server.
Adm Immediate -- FirstSQL/J native API for local startup of the server.

JDBC provides:

Connecting and initiating a session with the DBMS.
Execution of SQL queries and retrieval of result sets.
Execution of database updates in SQL and changes to the database structure with DDL (Data Definition Language).
Calls to Stored Procedures.
Access to database structure information (Meta Data).

FirstSQL/J Configurations

FirstSQL/J is distributed in 2 configurations:

Professional Edition -- embedded database applications.
Enterprise Edition -- client/server database applications.

In the Professional Edition, applications directly connect through the Client Interface to the DBMS. The application, the client interface and the DBMS engine all run on the same physical machine and the same JVM (Java Virtual Machine).

In the Enterprise Edition, the Client Interface connects to the DBMS through TCP/IP. The application and the client interface run on their own (client) JVM, and the server runs on a separate (server) JVM, often on a different physical machine.

The DBMS Engine for Enterprise Edition includes server administrative capabilities.

Note: Client Application is a generic concept, more accurately described as a Database Client Application. It may actually be a middle tier component, such as, a servlet.

Extended Features

FirstSQL/J has a number of extended features that differentiate it from other systems.

Java Objects in the Database

FirstSQL/J supports full object capabilities in the database using Java classes. DDL commands catalog class codes (bytecodes) in the database. After a class is cataloged, SQL commands can call class methods as UDFs (User Defined Functions) and as Stored Procedures.

Columns in database tables can use database classes as their data type. The values for these columns are objects created from the specified database class (or sub-class). SQL commands can call instance methods in object columns, and client queries can retrieve the value of such columns as objects. Applications can also create objects on the client and send them through JDBC to the DBMS to set object columns.

Methods in database classes have direct access to the DBMS. They use a built-in connection for JDBC access. When used as Stored Procedures they can return the result set of a query or the result sets from multiple queries to the client.

Database classes can serve as Object/Relational wrappers, eliminating the need for Relational-to-Object mapping layers on the client. A query can encapsulate its results into a single object that it returns to the client. For O/R mappings that include multiple tables and nested queries, a Stored Procedure can construct more complex wrapper objects for returning to the client.

Main Memory Operation

The FirstSQL/J engine offers several modes of operation. In default mode, the DBMS uses disk for storage of data during the run and maintains it on disk between runs. This is disk mode. The other modes, memory and flash, both maintain the database in memory format during a run. The in-memory modes yield much higher performance in database access and modification.

The memory mode of operation uses disk for persistent storage of data between runs. The disk format is the same for disk and memory modes. During a run, memory mode reads tables into memory when first referenced and maintains them there for the remainder of the run. At the end of the run, the engine writes all changed data back to persistent storage on disk. For purposes of recovery, memory mode also writes a roll forward journal to disk.

The flash mode of operation uses no disk access at all. It maintains the database entirely in memory. In flash mode, there is no persistent storage of data; all data is transient. The database is empty at the beginning of a run, and the engine discards the entire database at the end of the run.

Backup, Replication and Fault Recovery

FirstSQL/J provides several facilities for replicating live data during DBMS runs:

Online Backup -- live replication data by creating a snapshot of the current database and writing a roll forward journal during DBMS runs. Restore returns the database to its latest state by restoring the snapshot data and then rolling forward all changes made since the snapshot.
Replication Servers -- a secondary server that runs in conjunction with the primary server. The primary server sends all committed changes to the replication server, which maintains a mirror of the database state for the primary server.

A special type of replication server, known as a standby server, supports live recovery from failure of the primary server. When a standby server detects the primary server has failed, it automatically switches to active mode, and clients can reconnect to it to continue processing.

Note: Replication Servers are only available for the Enterprise Edition.

FirstSQL/J Utilities

The FirstSQL/J distribution includes a set of utility programs. Each utility has 2 versions -- a GUI version and a console (command line) version.

The FirstSQL/J Utilities are:

IjdbcWin -- utilities for executing SQL commands and displaying formatted results. The Ijdbc utilities use JDBC to interface with the ORDBMS.
Adm -- utilities for remote administration: monitoring and controlling the server. The Adm utilities are only available for the Enterprise Edition. They use the Adm APIs to interface with the ORDBMS.
MaintGUI -- utilities for direct control of physical databases: backing up and restoring databases, copying databases and building new databases.