Sun Tech Days (London): Day 2, Report 2 - Derby DB

Sun Java DB, a Small, Easy to Use, Pure Java RDBMS - Oystein Grolven
[Less than half of the audience have heard about Java DB]

Introduction - Short Intro
What is it? Its the database for Java. For development it should be the natural chioce.

It is a Sun-supported distro of Apache Derby. Same binaries. It turns out that more people in teh audience have heard of Derby. The difference is that Sun provide support. It's bundled with Java SE 6

What are the main characteristics? Its a complete RDBMS engine. It can be embedded (in the same process as your application) or operate in client/server mode. It is designed to be easy to use - "no DBA".

It has a small footprint, is compact and mature and is written in pure Java

History
Originally it was called Cloudscape and was started in 1997(?). Then it was acquired by Informix and subsequently by IBM "where it was silent" but was used in >100 applications from IBM. In 2004 IBM donated Cloudscape to Apache and called it Derby. In 2005 Sun started to add (commit) to it. Today there are 20 developers in Sun working on the Derby project.

It graduated from the incubator (i.e. it had a strong enough community and more than one company behind it) in July 2006. In Deceber 2005 Sun announced Java DB. The current release is 10.2.2 and is bundled in JDK 6.

Within Sun it is used as the default DB in Glassfish, Portal Server and Service Registry as their data storages and is also in tools like Creator and NB.

Features

• Written in pure Java (therefore runs on the same multiple platforms your app may run on)
• Write once run anywhere - requires a J2SE 1.3 - Java SE6 VM
• Database on disk format. I.e. stop the app, copy the db files to a new platform and restart. It all just works again
• It's embeddable - then access is just a method call within the JVM. It's a single JAR file in your classpath and it is invisible to users and is easy to use with zero maintenance. NOTE: while embedded it can still act as a server
• Client / Server mode is also possible
• Small footprint (2MB engine footprint - inc. by ~150k for a network server and another ~200k for tools). Runtime memory cache dependant on app and data caching. It has been run on machies with 16MB memory
• It's based on standards - SQL and JDBC. There are drivers for JDBC 2.0 - 4.0
• It also runs on top end mobile phones with the CDC configuration (where it uses a sub set of JDBC defined by JSR 169).
• It is a complete relational engine - multi user, tx, isolation levels, deadlock detection and crash recovery
• You can use standard SQL
• It can be encrypted by using a single Java library
• It can use Java stored procedures and you may call Java functions from SQL. To call back into the DB engine, use standard SQL connection URL
• Security - on disk database encryption. Can authenticate vs. an LDAP DB, NIS+ and pluggable authentication. It also provides ANSI grant/revoke priviledges support. Java security manager can be enabled.
• Architecture: SQL is compiled into byte code and can be optimised by the JIT compiler. The downside is that compilation and class loading has to take place. (therefore prepare and pre compile your statements). There is also a pluggable storage layer - file system (all in a file in a dir.) or read only in a Jar file, or an experimental in-memory version. In the future: HTTP, email, etc.
• Tools - ij (interactive SQL scripting tool using JDBC), dblook (schema extraction tool which generates the DDL to be used to recreate DB scehma), SQuirreL, sysinfo

Configurations

• Embedded in the same JVM - easy to use, fast, zero admin. Can only be accessed by one app at a time (though this could be e.g. an app server). To start, there is no specific startup code - when the driver is loaded it starts. [Horiffic syntax though ("Class.forName...") - to be hidden in the DriverManager in SE 7. Shutdown by exiting the JVM. But this is not controlled. You will have to redothe logs from the last checkpoint. Therefore you should do a shutdown with jdbc:derby:[dbname]:shutDown=true; via a connection request. NOTE: This may throw an exception
• Client / Server - Network server is an added layer between the client and the embedded DB. Stadard protocol (DRDA). Allows you to use scripting.
• Security - enable user authentication. Might be useful to enable the Java 2 Security Manager. Therefore procedures and fuinctions will have no permissions. App code can be granted permissions.
• Embedded Network Server - A combination of both. Therefore both network and same-JVM clients can connect. This adds DB reporting and debuging. Done with "derby.drda.startNetworkServer"
• Read only DB in a JAR - use on e.g. a memory stick. Simple to distribute.
• DB in a browser - It is small enough to fit in a browser. See demo at http://developers.siun.com/prodetch/javadb. Good it you have AJAX and you can avoid the delay of talking to a server. Then you can synch up periodically. Also copes with browser crashes so you don't lose data

In short it could cover and benefit all three tiers of a Web Application

Performance
It performs well. It compares well to the competition. Comparisions are OK against MySQL and PostgreSQL (with a TPC-B like load , main memory DB, DB 10MB, page cache 50MB not so good, but when DB size inc. to 10Gb, page cache 64MB it tops MySQL)

To Avoid Performance Hits:

• Use and reuse prepared statements
• Put the DB log and data on separate disks (specify the logDevice attribute when creating the DB) which avoids the disk head contention
• Tune the page cache size (the default is 4MB) - derby.storage.pageCacheSize
• Use indexes to avoid table scans - check query plans with derby.query.logQueryPlan = true;

[Then there is a demo of the tools: ij and SQuirreL]

Features in this (last 10.2 release)

• XQuery support

Features in next (10.3) release

• DBA powers
• Secure Server by default
• SSL / TLS between client and server
• Language based ordering
• Alter table enhancements (drop / rename columns)
• More performance improvements
• BLOB / CLOB enhancements - support for the entire JDBC API

Disclosure: I used to work for Sun between 1998 and 2005.