On August 5, 2005, the Apache Software Foundation released Derby 10.1.1 as a sub-project of the Apache Database Project. This came just over a week after Derby graduated from incubation status on July 26. In conjunction with this release, IBM has launched Cloudscape 10.1, its supported version of the open source Apache Derby. This is good news for existing Cloudscape developers and even better news for database gurus of all stripes looking for an economical, small footprint 100 percent Java-based solution.

After taking a look at all the nifty changes between versions 10.0 and 10.1, I picked out a few of my favorites. Cloudscape 10.1 gives you many more updates than you'll see here, but these are some highlights. For more information, read "What's New in Cloudscape 10.1" by Susan Cline.

Derby Network Client Driver
At last! A new JDBC driver for Java developers building non-embedded database applications with Derby. One of the biggest complaints about Derby to date has been the lack of an open source network client driver, giving Java developers an extra hurdle to leap when trying to establish remote access to the Derby network server. IBM has dealt with the problem by offering a DB2 Universal JDBC driver for use with Cloudscape. Prior to the Derby network client driver, Derby developers were required to download the DB2 Universal JDBC (type 4) driver or the complete DB2 Run-Time Client (which includes application drivers for C applications and the complete DB2 Universal JDBC driver). If you download and use Cloudscape 10.1 you will still find the DB2 Universal JDBC driver in the package, but the new Derby network client driver is now a great choice. Note, however, that the connection syntax differs between using the DB2 JDBC driver and the Derby driver.

If you develop non-Java applications, you should seriously consider Cloudscape. Derby alone is great for pure Java apps, embedded or non-embedded, but if you want to use Derby for C or PHP projects simply download the DB2 Run-Time client. Only Cloudscape offers supports for C and PHP applications, including ODBC support for Windows-based apps. Purchasing support for IBM Cloudscape has even been simplified with this new release and at $499 US per year, easily within reach for most deployments.

J2ME/CDC Support
On a different note, Cloudscape 10.1 now supports JSR 169, the JDBC API for the J2ME Connected Device Configuration (CDC) / Foundation Profile (FP). In other words…PDAs! Using the Java class org.apache.derby.jdbc.EmbeddedSimpleDataSource, you can create highly mobile apps that take advantage of Derby's small 2MB footprint. The CLDC Profile, for cell phones, still gives the Derby community just too small a target to shoot for with a full-featured version of the database. But for the hardware with the capacity for it, which includes most modern PDAs at this point, 10.1 makes an excellent database component for distributed or wireless apps, especially when managed through DB2 Everyplace.

New SQL Syntax
10.1 has added a few new notes to play with for the SQL musicians among us. These include:

• INTERSECT - Combine two SQL statements and return the ResultSet they share in common (consider it an "AND" to the UNION statement's "OR").
• EXCEPT - Combine two SQL statements and return the ResultSet they DO NOT share in common.
• SYNONYM - Create an alternative name for an existing database object, like a table or a view.

New Date/Time SQL Functions
Along those same lines, some new SQL syntax allows you to perform Date/Time calculations that were previously too much of a pain to enjoy, in the least. If you've gone through the process of creating your own timestamp calculation, you'll like these two new functions:

• TIMESTAMPADD - Add a quantity of a specified interval to a timestamp value.
• TIMESTAMPDIFF - Subtract a quantity of a specified interval from a timestamp value.

Below is a basic example of TIMESTAMPADD, to show you how it works.

SELECT DISTINCT {fn TIMESTAMPADD(SQL_TSI_MONTH, 4,
	timestamp('2005-05-31 12:00:00'))}
FROM TestDate

This returns the value 2005-09-30 12:00:00. A couple of things to note: the first part of the function defines the unit based on SQL constants. Here, I'm adding 4 months to the specified date. Also, the great thing about this function is that it automatically adjusts for differences in month length. Instead of returning September 31, it returned the correct date, September 30.

Updateable ResultSets
Prior to 10.1, you had updatable cursors available using the FOR UPDATE syntax in your SELECT statement. Now, the same syntax produces updatable ResultSets, as well. Performing updates using the ResultSet JDBC interface can save you development time and reduce the need declare and use cursors directly in your application. Note that only FORWARD_ONLY ResultSets can be updatable. All other requirements are the same as they are for updatable cursors:

• The underlying query must be a SELECT query.
• The SELECT statement cannot include ORDER BY, DISTINCT, GROUP BY, HAVING, or aggregates.
• The table source must be only one table, one table name, and no subqueries.

Online Space Reclamation
If your app does a lot of INSERTs, UPDATEs, and DELETEs, then you might notice some fragmentation and bloat in your database. Fortunately, 10.1 gives you a new way to trim the fat. Use the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure to perform table compression, which returns unused disk space to the operating system. It provides three levels of compression: PURGE_ROWS, DEFRAGMENT_ROWS, and TRUNCATE_END. And unlike the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure, which already existed, the new procedure does its work in-place in the existing table/index.

Soft Upgrades
If you've already deployed a 10.0 database and don't want to do a hard upgrade just yet, you can take advantage of the new features offered in 10.1 by doing a soft upgrade. Simply connect to your database using the new version of Derby, as in the following example ("olddatabase" is the 10.0 database):

connect 'jdbc:derby:olddatabase'

Note that you won't be able to do certain things until you perform a hard upgrade. This includes use of Synonym functionality, using the GENERATED BY DEFAULT option for identity columns when you create tables, and using the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure to reclaim unused disk space.

Look over the Apache Derby 10.1 Developer's Guide for more information on soft upgrades

Soon to Come: GUI
Right now, if you want to go beyond Derby's ij tool and use a graphic interface to manage your databases, you can use something like Squirrel or go with DB2's existing Eclipse plug-in.

But I just heard from someone in the know that IBM is coming out with something even better. You heard it here first! Sometime in the near future, look for the Cloudscape Workbench. This free download, built on Eclipse but independent of it, allows you to create, query, and administer your database with ease. With only a 20M footprint, it's built on Eclipse's Rich Client Platform, and a set of plug-ins for existing Eclipse gurus are in the works. As an added bonus, it will allow you to migrate your Cloudscape database to IBM DB2 Universal Database if and when the time comes to go full bore into an enterprise-level back-end.

Why Cloudscape?
Developers who are new to Derby and Cloudscape might wonder what the difference is between the two. As mentioned above, Cloudscape is IBM's commercial version of Derby and is also a free download. Though the core database is exactly the same, IBM has packaged it with several tools and services that developers will find especially helpful.

• Bundled installers to simplify setup and configuration.
• IBM build of the 1.4 JRE for Windows and Linux included. (Note that the 1.5 JRE is also fully supported with Cloudscape)
• Extensive documentation.
• DB2 JDBC client driver.
• An ODBC driver for use with Windows-based applications. (Separate download)
• IBM DB2 plug-ins for Eclipse, including a database management tool. (Separate download)
• Tech Support (available for a per machine license) from the experts at IBM

If you liked Cloudscape 10.0, then you're going to love 10.1. It gives you enough new features to make an upgrade worth your while and continues raising the bar for its competitors. If you're looking for a low-footprint, highly functional, 100 percent Java database, it's time to take a close look at this one.

Resources

IBM Cloudscape Version 10.1

A compendium of Cloudscape frequently asked questions

Apache Derby 10.1 Getting Started

Apache Derby 10.1 Developer's Guide

Apache Derby 10.1 Documentation (HTML)

Apache Derby 10.1 Documentation (PDF)