Thursday Apr 18, 2013

New Derby feature release:

The Derby community voted to approve Apache Derby earlier this week, and now it has been released. Get it from and try it out for yourself!

This release contains a number of new features, as well as bug fixes and other improvements. Here are the headlights from the release announcement:

  • JDBC 4.2 - Derby supports the Java 8 enhancements to JDBC.
  • Small device profile - Derby runs on the small CP2 profile of Java 8. See the corresponding JEP at
  • User-defined aggregates - Applications can create their own aggregate operators. See the "CREATE DERBY AGGREGATE" section in the Derby Reference Manual.
  • Varargs routines - SQL routines can be bound to user-written Java methods which have variable length argument lists.
  • Optional tools - Derby has new, optional tools to support data migration and metadata queries. See the "Optional tools" section in the Derby Tools and Utilities Guide.
  • SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS - Derby has a new system procedure for invalidating stored prepared statements when you think that your metadata queries or triggers are misbehaving--for example if they throw a NoSuchMethodError on execution. See the section on this new system procedure in the Derby Reference Manual.
  • Faster query compilation - A number of changes have made SQL compilation faster.
  • Unencrypting databases - Encryption can be turned off on existing databases. See the section on the decryptDatabase attribute in the Derby Reference Manual.

Monday Jun 25, 2012

Derby released

Earlier today, the release of Apache Derby was announced.

In addition to the usual chunk of bug fixes, this release includes the following new features:

  • NATIVE authentication, a new authentication mechanism with better support for managing credentials. See this section of the developer's guide for an introduction.
  • JDBC 4.1 escape syntax completes Derby's support for JDBC 4.1.
  • Allow multi-column subqueries in EXISTS predicates (SQL:2003 Feature T501, Enhanced EXISTS predicate) to support auto-generated SQL from some persistence frameworks.

Download it now and try it out!

Wednesday Oct 26, 2011

Derby released

Apache Derby was released earlier today. From the release announcement:

The Apache Derby project is pleased to announce a new bug-fix release, 

Apache Derby is a subproject of the Apache DB project. Derby is a pure
Java relational database engine which conforms to the ISO/ANSI SQL and
JDBC standards. Derby aims to be easy for developers and end-users to
work with.

Derby can be obtained from the Derby download site:

Please try out this new release.

Sunday Oct 02, 2011

Java DB and Derby at JavaOne 2011

Java DB poster

If you're at JavaOne in San Francisco this week, and you're interested in Java DB and Apache Derby, be sure not to miss these events:

  • Monday October 3 16:00 at Hilton San Francisco - Yosemite A/B/C: New Java DB Features in JDK 7 by Rick Hillegas
  • Tuesday October 4 19:30 at Hilton San Francisco - Golden Gate 6/7/8: BOF - Meet the Java DB Team
  • Wednesday October 5 12:00 in the atrium at the corner of Mission and 2nd Street: Apache Derby community lunch (open for everyone, no JavaOne pass needed)

Rick and I can also be found at the Java DEMOgrounds at Hilton, in the Core Java Platform corner, at the following hours:

  • Monday 10:15 - 15:30
  • Tuesday 09:45 - 12:45
  • Wednesday 09:15 - 12:00

You'll probably bump into one or both of us there at other times too. Hope to see many of you there!

Friday May 06, 2011

Derby 10.8 is out

Apache Derby was released earlier this week. Read the full announcement here.

Derby is a new feature release, the first one in the 10.8 series. The highlights include:

  • Automatic calculation of index statistics: In earlier releases, index cardinality statistics had to be updated manually (for example by calling the SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure) if they got out of date. Otherwise, the optimizer might choose suboptimal, poorly performing query execution plans because of the outdated information. This typically happened if one of the tables accessed in the query had grown or shrunk significantly after the index cardinality statistics were calculated initially. As of this release, Derby will recalculate the statistics if it detects that the size of the table has changed so much that the statistics are no longer usable.
  • JDBC 4.1: The early access builds of Java SE 7 (available for download here) have added new methods to many of the interfaces in the java.sql and javax.sql packages. This Derby release implements all of these methods so that you can try them out.
  • Improved interrupt handling: Earlier releases of Derby didn't handle interrupts very well. If a user thread was interrupted while performing a database operation, it could often end up crashing the database engine and require a reboot of the database. Starting with this release, interrupting a user thread shouldn't take down the entire database. The documentation has also been improved by clarifying which behaviour to expect if a thread is interrupted.
  • XML operators out of the box: Older releases of Derby required some work setting up your environment before you could use the XML operators (XMLEXISTS, XMLPARSE, XMLQUERY, XMLSERIALIZE). In Derby 10.8, the implementation of these operators has been made more portable and should work out of the box on most implementations of Java SE 6 and newer.

The release also contains many bug fixes and documentation fixes, as well as some performance improvements. As always, make sure that you read the release notes before upgrading to see if any of the changes could affect your application.

Friday Dec 17, 2010

What's new in Derby 10.7?

Apache Derby was released earlier this week. You'll find the binaries and the release notes at the download page. This is the first release from the 10.7 branch, and it contains some useful new features that were not part of the 10.6 releases. I will present some of these features here. There's also an interview with Rick Hillegas, the release manager for Derby 10.7.1, posted on the Java Spotlight Podcast, where the new features are discussed.

Boolean data type

Starting with 10.7, Derby supports the Boolean data type (yay, finally!). In earlier releases you would have to work around this limitation by using a CHAR(1) column or an SMALLINT column. Now you can declare the columns as BOOLEAN directly:

ij> create table t(b boolean);
0 rows inserted/updated/deleted
ij> insert into t values true, false, null;
3 rows inserted/updated/deleted
ij> select \* from t;

3 rows selected
ij> select \* from t where b;

1 row selected

BOOLEAN may also be used for parameters and return values in stored procedures and functions, just like any other data type.

Plan exporter tool

Sometimes you want to know exactly how Derby executes a query. For example, you may have a big join that takes a very long time to complete, and you want to check if Derby uses the relevant indexes to get the best performance.

It has always been possible to get runtime statistics from Derby (see the Working with RunTimeStatistics chapter in Tuning Derby), but the statistics output has been hard to analyze (read: unstructured text format). The 10.6 release improved the situation somewhat by introducing XPLAIN style statistics, which puts the collected data into database tables that can be queried from SQL. Still, it can be difficult to dig up the information you want, especially for complex queries.

Derby 10.7 further improves this by adding a tool that processes the data in the XPLAIN tables for you, and produces an XML document representing the execution plan for a specific query. It can also do some simple rendering and output a graphical version of the plan in HTML format. This new tool is documented in the Derby Tools and Utilities Guide, in the PlanExporter chapter.

Let's take a look at an example. I wanted to inspect the query plan for a big and ugly join performed by OpenGrok to get the commit log for a version-controlled file. Enabling collection of statistics is easy. Just call one system procedure to enable it, and another one to say that you want XPLAIN style statistics and where to store them. I added this code in the method that executed the query I was interested in:

And for good measure I also disabled collection before returning from the method, so that I wouldn't get lots of statistics for queries I didn't care about:

Then I ran a JUnit test that I knew would exercise this code. After it had completed, I could connect to the database with the ij tool and verify that the statistics had been collected into the XPLAIN tables:

ij version 10.7
ij> connect 'jdbc:derby:/path/to/DB';
ij> select stmt_id from xplain.sysxplain_statements
>          where stmt_text like 'SELECT CS.REVISION%';

5 rows selected

The query I was interested in had been executed five times. I picked one of the STMT_ID values and passed it to the PlanExporter tool, asking it to produce both XML and HTML versions of the selected plan:

$ java \\
    jdbc:derby:/path/to/DB XPLAIN \\
    f93441e2-012c-e092-02e5-000003bb1070 \\
    -xml plan.xml -html plan.html

The above command produced the following two files: plan.xml and plan.html. If you open the XML version, you'll see that most modern web browsers allow you to expand and collapse the nodes, which is neat when you analyze a large query and you want to focus on one specific part of it. For comparison, I'm also including plan.txt, which shows what the plan would look like in the old format (which is still supported, by the way).

Definer's rights for routines

A Derby stored function or stored procedure may execute SQL statements against the database using a nested connection. If you have SQL authorization enabled, the user that invokes the routine must have been granted privileges for all the SQL that is executed by the routine. So if the routine deletes rows from a table, you would have to grant the user delete privileges on that table, even if you don't really want that user to be allowed to delete rows except via the routine.

To address this concern, Derby 10.7 adds an optional clause (EXTERNAL SECURITY { DEFINER | INVOKER }) to the CREATE FUNCTION and CREATE PROCEDURE statements. This clause lets you specify whose privileges the routine should use when executing SQL statements on a nested connection, and could be used to delegate more fine-grained privileges to users.

For example, to allow a user to execute a procedure that performs some insert and select statements, you would previously have to give the user select and insert privileges on the tables involved, giving him carte blanche to insert whatever he wants into the tables. The code would look something like this, with multiple GRANT statements:

ij> create procedure add_item(id int, name varchar(10), category int)
>          external name 'MyProcedures.addItem'
>          language java parameter style java;
0 rows inserted/updated/deleted
ij> grant execute on procedure add_item to user1;
0 rows inserted/updated/deleted
ij> grant insert on items to user1;
0 rows inserted/updated/deleted
ij> grant select on categories to user1;
0 rows inserted/updated/deleted

If you use the new EXTERNAL SECURITY clause introduced in Derby 10.7 and specify that the procedure should execute with the definer's privileges, you only need to grant the user privileges to execute the procedure, and he will no longer have privileges to insert data directly into the table:

ij> create procedure add_item(id int, name varchar(10), category int)
>          external name 'MyProcedures.addItem'
>          language java parameter style java
>          external security definer;
0 rows inserted/updated/deleted
ij> grant execute on procedure add_item to user1;
0 rows inserted/updated/deleted

Less code, easier to maintain, and more secure.


New in 10.7 is also the TRUNCATE TABLE statement, which is handy if you want to delete all rows in a table. It is faster than DELETE FROM <TABLE>, especially on large tables, because it wipes the entire table and its indexes in one go instead of deleting one row at a time.

ij> select count(\*) from t;

1 row selected
ij> truncate table t;
0 rows inserted/updated/deleted
ij> select count(\*) from t;

1 row selected

Unicode database names

The embedded driver has always supported all characters in the Unicode character set in database names, as long as the underlying file system allows those characters in file names. But the network protocol has only worked with ASCII characters in the database names until now, so such databases couldn't be created or accessed from the client driver. In this release, the network protocol switched to UTF-8 encoding in the fields that hold the database name, so that you now, for example, can create and use a database with Japanese characters:

ij version 10.7
ij> connect 'jdbc:derby://localhost/データベース;create=true';

This only affects the database names. The network protocol used UTF-8 encoding for SQL queries and results even before this improvement, so inserting non-ASCII text values into the database, or using non-ASCII characters in table and column names, is also supported, just like it was in all previous releases.

Wednesday Oct 13, 2010

Derby 10.6.2 is out

Apache Derby has just been released. See the announcement from the release manager, Lily Wei, here. This is a maintenance release which contains mostly bug fixes, including the fix for one potential data corruption (DERBY-4677) and one regression in (DERBY-4671). It also adds Chinese, French, German, Italian, Japanese, Korean and Spanish translations for the messages that were new in

The downloads and the release notes can be found at

Wednesday May 19, 2010

Derby 10.6.1 has been released

Apache Derby has just been released. This is a new feature release, and the first release in the 10.6 series. See the full announcement here. The downloads and the release notes can be found at this URL:

Make sure that you read the release notes carefully before you upgrade. Pay special attention to the note about the security issue CVE-2009-4269, which details how to protect your database from a vulnerability in Derby's BUILTIN authentication and password substitution in previous releases. (The vulnerable code is still there to make it possible to access old databases, but the defaults have changed so that new databases will not have the vulnerability.)

The new features in this release include:

  • Sequence Generators - Named generators for allocating successive, evenly spaced numbers. See feature T176 of the SQL Standard.
  • User-defined types - Named types bound to serializable Java objects.
  • Restricted table functions - Limits on the columns and rows returned by table functions.
  • XPLAIN statistics collection - Query plan statistics stored in tables for later analysis.
  • GROUP BY ROLLUP - A subset of the SQL Standard ROLLUP functionality on the GROUP BY clause. See feature T431 of the SQL Standard.
  • CROSS JOIN - CROSS JOIN syntax. See feature F401-04 of the SQL Standard.
  • Named columns join - USING clauses in joins.
  • SHOW FUNCTIONS - IJ command that lists stored functions.
  • In-memory back end enhancements - Numerous improvements, including the ability to delete in-memory databases.
  • ORDER BY in subqueries - Syntax for explicitly ordering rows returned by subqueries. See features F851, F852, and F855 of the SQL Standard.
  • OFFSET, FETCH FIRST/NEXT in subqueries - Generalized syntax for retrieving row subsets. See features F856, F857, F858, F859, F861, F862, F863, and F864 of the SQL Standard.
  • NATURAL JOIN - Support for NATURAL JOIN. See feature T431 of the SQL Standard.
  • Qualified identifers in ij - Ability to reference cursors and prepared statements in other connections.
  • Configurable hash algorithm - Ability to customize the hash algorithm used by BUILTIN authentication.
  • Context-sniffing scripts - Ability of shipped scripts to locate Derby jars when DERBY_HOME isn't set.
  • Case-insensitive strings - Ability to ignore case in string comparisons and sorts.

See also my earlier preview entry, which describes some of these features in more detail. Most of them are also described in the Derby 10.6 manuals.

Saturday Feb 06, 2010

We're still here...

Some of you may be alarmed after last week's announcement and no mentioning of Java DB in the Oracle + Sun webcast. Don't be! The Java DB team is still here, working as hard as ever, in the Derby community and on Java DB. :)

See also Rick's message to derby-user.

Thursday Dec 10, 2009

An early look at Derby 10.6

The Derby community is currently working on the next feature release, Derby 10.6, and a number of new features have already been implemented. If you haven't already done so, now would be a good time to download the development sources and kick the tires on your favourite new feature! Early feedback to the community (either on the mailing lists or in the JIRA bug tracker) increases the likelihood of getting improvements and bug fixes implemented before the final release.

If you don't feel like building jar files from the development sources yourself, you can download the binaries used in the nightly regression testing from Make sure that you understand all the warnings about the difference between a production build and a development build before you start using it!

The release page on the wiki lists some of the features that are planned for 10.6. Let's take a quick look at some of those that are mostly done and ready for testing.

Store Java objects in columns

DERBY-651 tracks the ongoing work that enables storing of Java objects directly in the columns of a table. The objects must belong to a class that implements the interface.

For example, if you want to store java.util.List objects in a table, you first need to declare a user-defined type that maps to the Java type, and create a column with that type:

stmt.execute("CREATE TYPE JAVA_UTIL_LIST " +
             "EXTERNAL NAME 'java.util.List' " +
             "LANGUAGE JAVA");
stmt.execute("CREATE TABLE T(C1 JAVA_UTIL_LIST)");

Next, you prepare a statement and use setObject() to insert the Java object that you want to store:

PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO T(C1) VALUES (?)");

ArrayList lst = new ArrayList();
lst.add("First element");
lst.add("Second element");

ps.setObject(1, lst);

Finally, you execute a SELECT query and use getObject() to restore the object from the database:

ResultSet rs = stmt.executeQuery("SELECT C1 FROM T");
while ( {
    List list = (List) rs.getObject(1);
    System.out.println("Size of list: " + list.size());

You can also use your object types as arguments to user-defined functions or procedures:

             "RETURNS INTEGER " +
             "EXTERNAL NAME 'MyListFunctions.listSize' " +
ps = conn.prepareStatement("values list_size(?)");
ps.setObject(1, lst);

Multi-level grouping

Derby's GROUP BY syntax has been extended with the ROLLUP keyword, which allows for multi-level grouping. There's a fine write-up about the feature on this wiki page. The example below shows how you can ask for the total amount of sales of each product in each state, the total amount of sales in each state, the total amount of sales in each region, and the amount of sales in all regions, in one single query:

REGION    |STA&|PRODUCT   |4          
East      |MA  |Boats     |10     <-- all boat sales in Massachusetts
East      |MA  |Cars      |190        
East      |MA  |NULL      |200    <-- sum of all sales in Massachusetts
East      |NY  |Boats     |570        
East      |NY  |Cars      |10         
East      |NY  |NULL      |580        
East      |NULL|NULL      |780    <-- sum of all sales in region East
West      |AZ  |Boats     |40         
West      |AZ  |Cars      |300        
West      |AZ  |NULL      |340        
West      |CA  |Boats     |570        
West      |CA  |Cars      |750        
West      |CA  |NULL      |1320       
West      |NULL|NULL      |1660       
NULL      |NULL|NULL      |2440   <-- sum of all sales in all regions

15 rows selected

Richer JOIN syntax

Derby already supports INNER JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN. In 10.6, the syntax will be enhanced with support for

  • the CROSS JOIN operation
  • named column joins
  • sub-queries in ON clauses

CROSS JOIN is the simplest of the join operations, and SELECT \* FROM T1 CROSS JOIN T2 is just another way of writing SELECT \* FROM T1, T2. It may also be combined with other join operations:

A          |B          |A          |B          |A          |B          
5          |2          |2          |7          |NULL       |NULL       

1 row selected

Named columns join may save you some typing when the columns in the join key have the same name in the two tables being joined. In the example below, where two tables COUNTRIES and CITIES are joined, you no longer need to write the full join condition ON COUNTRIES.COUNTRY = CITIES.COUNTRY. Instead, you just say that the tables should be joined on the COUNTRY column with a USING clause:

COUNTRY                   |2          
Afghanistan               |1          
Argentina                 |1          
Australia                 |2          
United Kingdom            |1          
United States             |37         
Venezuela                 |1          

45 rows selected

Explain plan

The XPLAIN functionality makes it possible to access run-time statistics with SQL queries. See this section in the development version of Tuning Derby for details about how to enable it and use it. Here's one of the more advanced examples from the manual, which shows how to get all queries that performed a table scan on the COUNTRIES table, and the number of pages and rows visited in the scan:

STMT_TEXT                     |PAGES      |ROWS       
SELECT \* FROM COUNTRIES       |2          |114        

1 row selected

ORDER BY in sub-queries

Up till now, Derby has only accepted ORDER BY clauses in top-level SELECT queries. The ongoing work on DERBY-4397 will allow ORDER BY clauses in sub-queries as well. This is going to resolve the very old request for ordering of inserts (DERBY-4). There's also a request for OFFSET/FETCH in sub-queries (DERBY-4398), which would be very useful in combination with ORDER BY.

Dropping of in-memory databases

Derby 10.5 added support for in-memory databases, but there was no convenient API for deleting the in-memory databases and reclaiming the memory without taking down the Java process. In Derby 10.6, in-memory databases can be destroyed with an API similar to the one used for creating and shutting down databases. Simply add drop=true to the JDBC URL.

ij> connect 'jdbc:derby:memory:mydb;create=true';
ij> create table t (x int);
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:memory:mydb;drop=true';
ERROR 08006: Database 'memory:mydb' dropped.

Very nice if you're running JUnit tests and want to clear the database and free up the memory between each test case.


Derby's interactive tool for running scripts or queries against a database, ij, lacked a command to show the functions that are stored in the database. Derby 10.6 adds SHOW FUNCTIONS to ij's list of SHOW commands, so now you can issue the following command to view all the functions in the APP schema:

FUNCTION_SCHEM|FUNCTION_NAME               |REMARKS                            
APP           |MY_FUNC                     |MyFunctions.f                      

1 row selected

Restricted table functions

In Derby 10.6 it will be possible to push predicates into table functions and process the restrictions before the function returns the results to the SQL engine. This can speed up execution of some table function queries significantly. Take this example:


If MY_FUNCTION() returns thousands of rows, and only a handful of them match the restriction X = 5, a lot of work is wasted generating and scanning rows that are just thrown away. With restricted table functions, Derby's SQL engine will pass information about the restriction down to the table function, and the table function may use this information to produce and return only the rows that are actually needed.

Details about how to use restricted table functions can be found in the functional spec attached to DERBY-4357.

ROW_NUMBER improvements

Previous releases of Derby only allowed the ROW_NUMBER function to appear in the select list of a query, and it could not be used to build up more complex expressions. Now these limitations have been removed, and you can write queries like this one without getting syntax errors:


Saturday Sep 12, 2009

Java DB 10.5.3 is ready for download

Java DB, Sun's supported distribution of Apache Derby, is now ready for download on the Java DB site. This release includes all the bug fixes and the new localized messages found in Derby 10.5.3. Also new in this release is a native installer for Mac OS X, which comes in addition to the existing Java DB installers for Solaris, Linux and Windows and the platform-independent zip archive.

Thursday Aug 27, 2009

Derby 10.5.3 has been released

Apache Derby has been released. It's a maintenance release which fixes a number of bugs found in the latest feature release (10.5.1), and it contains updated localizations for the new messages introduced in 10.5.1. If you haven't already upgraded to Derby 10.5, now is a good time to do it!

See the full release announcement here.

You may have noticed that Derby was also released recently. That release did however suffer from a wrong results regression that was discovered just as the release was posted on the website. Derby is essentially the same as Derby, but with the regression and a couple of other bugs fixed.

Wednesday Jul 01, 2009

Accessing Derby from Perl

Bernt found out that There is More Than One Way To Do It (TMTOWTDI) is true for Derby too, and wrote a wiki page which explains how to access a Derby database from Perl using the DBD::JDBC module. Very cool! :) See the wiki page here.

Friday May 29, 2009

Java DB has been released

Java DB has just been posted on the Java DB site. This release contains the exact same jar files as, and is functionally equivalent to, the recently released Apache Derby In addition, you get

  • installers for Solaris, Linux and Windows
  • the option to register and track your Java DB installations in Sun Inventory
  • the option to buy support from Sun
For more information about what's new in Java DB, see my previous blog entries about Apache Derby, the Apache Derby site and the Java DB site.

Sunday May 03, 2009

Derby is out

Apache Derby was released two days ago. See the full announcement here.

In addition to lots of bug fixes and documentation fixes, there are a number of new features in this release, including

SQL roles
Read more in my previous blog entry
SQL generated columns
Read more in my previous blog entry
LOB improvements
Many performance and usability improvements for BLOBs and CLOBs
Replication improvements
Replication is now also possible with encrypted databases
SQL OFFSET/FETCH (aka limit functionality)
Read more in my previous blog entry
In-memory storage engine
Read more in my previous blog entry
Update Statistics Stored Procedure
Refresh outdated index cardinality statistics in an easier fashion. More info here and here

Also, the index split deadlock frequently seen in previous releases has been fixed.




« February 2017