Overview of FirstSQL/J
Let us introduce you to FirstSQL/J, an extremely fast 100% Java Object-relational database. FirstSQL/J supports the SQL92 Standard at the Intermediate Level with full Java object capabilities in the database using Java Classes. This is a world class database product that confirms the value Java database developers place on using both SQL and Java Objects for developing Java applications - but without the need for special treatment of Java classes and O/R Mapping tools. A full version, along with documentation, is available with excellent technical support for free development during a 90 day evaluation period.

FirstSQL/J surpasses both leading Java and C-based databases in both speed, SQL, and Object-oriented sophistication. Java application developers needing anything from a simple file to complex object-relational designs are taking advantage of FirstSQL/J on platforms ranging from 64-bit and multi-CPU servers to handheld devices to real-time fault tolerant embedded systems. FirstSQL/J databases can be run using traditional disk-based or true In-Memory operation.

FirstSQL/J provides a J2EE class client/server version and an embedded version that are 100% compatible at the database level, regardless of OS or HW platform. FirstSQL/J Professional is a basic, standalone, single user version designed for general desktop Java applications.

FirstSQL/J Enterprise Server - J2EE class ORDBMS allows unlimited client access for scaleable client/server database applications.

FirstSQL/J Professional - for general desktop database applications requiring single-user access.

Our customers are typically corporate and commercial database application developers that want to build and distribute fast and sophisticated information management software products. They don't want their products (or customers) to be limited to a single operating platform. They want a single platform independent jar file that can be created once, installed and run the same anywhere. From advanced 64-bit servers to small handheld devices to printers, we haven't seen a Java platform yet that FirstSQL/J won't run on.

Important Features:

. Written in 100% Java - Available for any platform where a JDK 1.1 or greater JVM resides.
- Type 4 JDBC 3.00 driver included, JNDI, JCA 1.0 & 1.5 support
- J2EE version for enterprise class server platforms supports XA/JTA, JCA.
- Embeddable - Zero administration.
- Scaleable - Unlimited Enterprise class client/server version.
- ODBC 3.00 driver available for server.

. SQL92 - Supports Intermediate Level - SQL 92 ANSI Standard.
- Full Transaction processing reliability. The excellent documentation includes a complete SQL92 Tutorial and appendix to all SQLException Java error codes.

. Object-oriented - Full object capabilities using Java Classes.
- SQL access to persistent data, objects, and methods.
- Maps database types with Java types - NO O/R mapping tool is needed.
- Stored Procedures in Java.

. In-Memory (IMDB) Option
- Main Memory mode and conventional Disk mode are provided in the same database. In Main Memory mode, the ORDBMS stores all data (tables, classes, indexes) in memory for ultra fast direct access and manipulation. Persistent data is written to magnetic media and provides complete recovery in the case of a database restart. FirstSQL/J provides both modes of execution to give users a choice in database performance from a single database product.

. Replication & Fault Tolerance
- Replication Servers are read-only server(s) that run 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 continuous availability in the case of failure of the primary server. When a Standby Server detects the primary server has failed or been shut down, it automatically switches to read-write mode, and clients can continue processing transaction on it immediately without outside intervention. Sophisticated failover plans can be implemented using the Arbiter API.

. Tools and Ultilities Provided with FirstSQL/J
- Ijdbc and IjdbcWin - Interactive SQL tool for working with statements and results. A detailed tree view of all database elements provides additional developer productivity.
- GUI Administrator - full package of server administrative capabilities in one application, including API level server startup, shutdown, and server session control.
- AdmOptions Utility - application for creating and editing property files for the GUI Administrator.
- Monitor - dynamic charting of Enterprise Server activity, plots a wide variety of elements including connects, SQL statements, open tables, TPS, commits, row access, physical page read/write, memory usage, and much more.
- MaintGUI - utilities for maintaining physical databases, Maint and MaintGui utilities provide capability to:
+ copy a physical database,
+ backup a physical database, including online backup,
+ restore a physical database from backup,
+ build a new physical database including; Image, Standby, Replica, Backup, Readonly.

. Import/Export - utility for bulk loading data into and out of FirstSQL/J databases.

. Remote Logger - a rolling log of database activity.

. Documentation - includes an SQL Tutorial and complete listing of SQL Exception codes by:
- SQL State code number for type of exception
- Text message describing exception
- FirstSQL/J specific error number

FirstSQL/J provides a full-featured SQL with extensive support for subqueries, nested queries, and recursive queries. These capabilities greatly empower and simplify query formulation. The query engine is highly optimized for Prepared Statement execution.

However, FirstSQL/J support goes much deeper than that. Its advanced optimization techniques mean that even the most complicated and deeply nested queries are processed efficiently. Usually, use of temporary tables can be replaced by nested queries. With FirstSQL/J, nested queries give a double boost in performance, by:

. Eliminating the overhead of multiple queries, writing and reading the temporary tables.

. Optimizing the entire operation holistically.

Business intelligence systems need to perform complex queries efficiently, and some of these queries must access hierarchical data. For example, an employee table may hold an organization's hierarchy information by listing each employee's name and manager. FirstSQL/J can readily perform the necessary hierarchical query with the CONNECT BY clause. The CONNECT BY clause specifies the relationship between parent rows and child rows in the hierarchy and the starting point of the hierarchy. The CONNECT BY construct supports a recursive query that traverses a tree structure to produce a flat result set. A single table or a join of tables can represent a tree in the database.

Java Objects in the Database

FirstSQL/J provides integrated support for objects. In FirstSQL/J, table columns can have an object class as their datatype. The value of an object column can be an instance of its defined class or one of its subclasses. Java capabilities enhance the power of SQL92. Object extensions allow Java methods to be called anywhere in SQL commands - SELECT List, WHERE Clause, SET Clause, etc.

FirstSQL/J extends SQL to allow you to call the methods of object columns. A simple query example:

SELECT shape FROM shape_table WHERE shape.getHeight() < 14;

Object columns have data (fields in the object) *and* behavior (methods in the object). Extended object capabilities enhance the strengths of SQL.

Complex relationships can be stored in object columns, but we encourage the use of relational facilities for modeling complex relationships. As OODBMS proponents have discovered, accessing complex relationships using object structures is too difficult. (see Tutorial on Java Objects in the Database)

The relational model excels at modeling complex relationships.

The CREATE CLASS command catalogs a Java class into the database. Once cataloged, the Java Class has multiple uses, for:

• User Defined Functions – the static methods of the class can be called as functions in SQL.
• Stored Procedures – the static methods of the class can be called as stored procedures. The class methods have direct access to the current database connection and can return multiple resultsets.
• Data Wrappers – instances of the class can be used by the client application to pass data to and from the ORDBMS. See the Tips section for example.
• Column Data Types – table columns can use the class as their data type. Values for the columns in table rows are then instances of the class. SQL statements construct instance values for the column with the NEW operator and utilize the object column values by calling their methods.

Database clients using JDBC (Java DataBase Connectivity) can retrieve and send database objects and they can then execute the retrieved objects in the client environment, in the client JVM (Java Virtual Machine).

Execution Sandbox Flexibility

FirstSQL/J supports execution of methods from user-defined classes cataloged in the database (with CREATE CLASS). SQL commands can reference both instance and static methods. Static methods also serve as stored procedures. The internal database methods execute in a special sandbox that restricts access to portions of the standard API. The list of classes that are accessible from database methods can be expanded to all of the standard API plus packages outside the standard API. This powerful facility enhances the flexibility and range of FirstSQL/J applications. Examples of capabilities now available to database methods can include classes for communication with external devices, other databases, preserving basic elements in AWT such as Color and Point classes, email, and storage of XML data.

Technical Features of FirstSQL/J Database Systems:

SQL Features Supported

• Primary Keys (Entity Integrity)
• Foreign Keys (Referential Integrity)
  • Cascaded Update and Delete
  • Self-referencing and Cross-referencing Foreign Keys
• CHECK Constraints
• Transactions - normal and Distributed (XA/JTA)
  • Full Transaction Support (Commit, Rollback, Recovery)
  • Row Level Locking
• Privileges – table and column access
• SQL Support – SQL92 Intermediate Level (see docs for additional info)
  • Complete Subqueries
  • Outer Join
  • GROUP BY, HAVING, Set Functions (SUM, AVG, MAX, …), UNION
  • Views (with GROUP BY, UNION), WITH CHECK OPTION
  • Stored Procedures, can return multiple resultsets
  • Triggers
  • UDFs (User Defined Functions)
  • Sequence Keys
  • Data Types
    • String – CHAR, VARCHAR (CHAR and VARCHAR can be used for binary data with no loss of information and no overhead - 1 byte takes 1 byte of space.)
    • Numeric – TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE
    • Date-Time – DATE, TIME W/TIMEZONE, TIMESTAMP W/TIMEZONE, YEAR-MONTH Interval, DAY-TIME Interval
    • Objects – User Defined (Java Classes)
    • CONNECT BY – Recursive Queries on hierarchical data
    • BLOBS, CLOBS
  • Functions, plus full set of Math Functions
  • CASE and CAST Operators
  • Advanced NULL processing
  • Quoted Names – Special characters in table, column, user, schema, class, … names using “
• JDBC w/JNDI /JCA 1.0 & 1.5(see docs for additional info)
• Supports SQL DML, DCL and DDL
• Full Escape Processing -- {d ‘2002-02-28’}

System Details and Capabilities

• Names – 128 characters
• Tables in Database – unlimited **
• Columns per Table – 2G
• Column Size – 2G
• Row Size – 16M (excluding Blobs, Clobs, long VARCHAR and Objects)
• Rows Per Table – unlimited **
• Block Sizes – 512, 2K, 8K, 32K, 128K, 512K, 2M, 8M, 16M (excluding long VARCHAR and Objects) – automatically selected
• Objects in Database – unlimited **
• Classes in Database – unlimited **
• Schemas in Catalog – unlimited **
• Total Database Space – unlimited **

** Note: actual limit is 64 bit value (128 bit value for Total Database Space), but in reality limited by environmental resources.

FirstSQL/J is designed to run on any computing platform that has a 1.1 JDK, or greater, JVM. The full database engine foot print is <830k but dynamically links in classes so actual foot print will depend on the application requirements. Use of the component model means functionality can easily be removed to create a footprint as small as 400k.

Additional FirstSQL/J overview information.

Technical Support
FirstSQL puts their customers first. These products both come with free Technical support via email or fax during your evaluation period. Annual support agreements that include email and phone contact and free updates are available for 20% of the license price for Primary technical support. Additional Premium and 24x7 Premium Technical Support plans are available as well. Full source code is available as well as consulting services by contacting FirstSQL. Technical Support Programs and Rates

Licensing
FirstSQL database products are licensed as a development system per development computer. FirstSQL/J Enterprise Server is $1,195 for client/server database applications with unlimited client access. Professional is $495 for single-user and single access database application development. Products require a small runtime fee for commercial application distribution. FirstSQL/J-based application demos and evaluation systems can be distributed with a runtime at no cost. You may download a full version for a 60 day evaluation period for any platform that supports a JVM.

Download any FirstSQL/J today from the Download section.

Contact FirstSQL at 1-510-232-6800 or sales@firstsql.com if you have any questions.