Checkpoints

Not Supported

Concurrency Control

Two-Phase Locking (Deadlock Detection)

In SQL-92, there is not a BEGIN statement to explicitly start a new transaction. A new transaction begins with any SQL statement if there is not an active transaction already. During each transaction, locks are acquired at the row level. The locks are held until the end of a transaction, which can happen if : * The client commits the transaction through the action of a COMMIT statement * The client aborts the transaction through the action of a ROLLBACK statement * The client requesting the transaction terminates abnormally * The DBMS terminates the transaction through deadlock detection and resolution * The DBMS terminates abnormally

Data Model

Object-Relational

Columns can map to Java Objects. Java classes, methods, and inheritance are fully supported in the physical database. User can define Java objects and treat these objects as data types. They can be managed using JDBC client APIs and accessed using SQL. Methods can be part of SQL statements and are executed first and treated as part of the expression tree of the query.

Foreign Keys

Supported

The supported SQL foreign key features are cascaded Update and Delete, Self-referencing and Cross-referencing as part of the SQL-92 standard.

Indexes

B+Tree BitMap

Isolation Levels

Read Uncommitted Read Committed Serializable Repeatable Read

All four standard isolation levels supported by JDBC are supported by FirstSQL/J. The default transaction isolation level is Repeatable Read. The different isolation levels are implemented using different policies on how row-level locks are acquired.

Joins

Nested Loop Join

Logging

Logical Logging

A rolling log is maintained by the primary server to record the status of transactions in the form of SQL commands issued by the client. The number of concurrent transactions is assumed to be small. Replication servers are read-only servers which are copies of the primary server. When a transaction is committed, the primary server sends all changes to replication servers. A special type of replication server is called Standby Server. When a Standby Server detects that a primary server has failed, it switches to read-write mode to continue processing transactions.

Query Compilation

Not Supported

Query Execution

Tuple-at-a-Time Model

Query Interface

Custom API SQL Stored Procedures Command-line / Shell

All data access to the physical database is through SQL. However, the FirstSQL/J client interface support both SQL and Java (through JDBC). The client application may use Java code to make SQL queries or call Stored Procedure for retrieving data. The tool Ijdbc, a utility developed by Oracle and packaged in every FirstSQL/J distribution, allows SQL statements to be executed by through the command-line.

Storage Architecture

Disk-oriented In-Memory

Main Memory mode and Disk mode are supported. The two modes cannot be used simultaneously. The physical database either reside in main memory or disk. Disk mode persists data at whenever a transaction is committed whereas Main Memory mode persists data only after a client terminates a session, which can involved many transactions.

Storage Model

N-ary Storage Model (Row/Record)

Storage Organization

Heaps

Database elements are stored in the form of trees. A tree can be constructed from a single table or a join of tables in the database.

Stored Procedures

Supported

Stored Procedures need to be written in Java. They can be called directly using SQL commands or by other Java code in the client application. DDL commands stores Java class into the catalog. Once the class is stored in the catalog, the class methods can be called as Stored Procedures.

System Architecture

Embedded

The system architecture depends on the configuration being used. In FirstSQL/J Professional Edition, the client application and the DBMS run on the same Java Virtual Machine and thereby the same physical machine. All data is stored on the same machine as the DBMS. In FirstSQL/J Enterprise Edition, the client application and the DBMS engine run on different Java Virtual Machines and physical machines. The JVM running the DBMS engine is responsible for both execution and storage. In addition to a single primary server, there can be many replication servers. Replication servers are standalone read-only servers that mirror the primary server.

Views

Virtual Views

Database elements are stored in a tree structure. When constructing views, the tree structure is traversed to produce a set of matching tuples. This set of tuples is never materialized and needs to be recreated every time the view is needed. The supported clauses include GROUP, UNION, and WITH CHECK OPTION as specified in the SQL92 standard. Views can be created by interactive SQL tools called Ijdbc and IjdbcWin, provided as part of the database.