- 15 Non-Certified IT Skills Growing in Demand
- How 19 Tech Titans Target Healthcare
- Twitter Suffering From Growing Pains (and Facebook Comparisons)
- Agile Comes to Data Integration
Page 5 of 8
Apache Derby is an open source relational database from the Apache project. Written in Java, Apache Derby will run in any Java virtual machine and has a very small footprint (less than 3MB), including an embedded JDBC driver. It is released under the Apache License Version 2.0.
Apache Derby started as Cloudscape in the mid-1990s, then it was acquired by Informix before being re-branded as IBM Cloudscape when IBM took over the assets from Informix. In 2004 IBM contributed the Cloudscape code to the Apache project and in 2005 the first Apache Derby version was released as Version 10.1.1. Apache Derby can be run in two modes, either as a fully functional embedded database engine (for instance as used in Apache Tomcat and Red Hat application server) or as a traditional client-server database using the Derby Network Server.
We installed Version 10.9.1 on a Windows Server 2008 running the Oracle Java run-time Virtual Environment (JRE) Version 1.7. It is important that the JRE is installed prior to installing Apache Derby and the install does require a few commands issued from the DOS prompt. Apache Derby ships with an interactive scripting tool named "ij." It works with the embedded Derby JDBC driver or a client JDBC driver. Using the command prompt we were able to create a test database and empty table from the command prompt using just a few short commands.
After creating our test table, we decided to utilize the built-in bulk insert procedure to insert our million-plus test rows. The bulk insert worked fine except we ran into a similar problem we had with PostgreSQL with uppercase vs. lowercase in the execute syntax. Our table was named test1, all lower case, but the parser (and documentation, after some online research) insisted we enter it in all upper case like this:
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'TEST1','c:\temp\SampleData.txt',',',null,null,0);
After fixing the case problem, the data import took just a few seconds and a select statement to retrieve the first 100,000 records ran in less than five seconds. Just as we observed with the other database servers, working with individual rows of data proved to be quite efficient and our select, update and delete statements completed in fractions of a second. Although Apache Derby did not deliver quite the same speed as MariaDB and MySQL in our tests, but the differences were small, ranging from one-tenth to one-hundredth of a second slower.
Security features include user authentication and authorization allowing for object-level access restrictions. Apache Derby databases can also be encrypted either when they are created or encryption can be added to existing databases.
The small footprint of Apache Derby makes it an excellent choice for embedding in Java applications such as those running on PDAs or cell phones. The Apache website provides comprehensive and current documentation and there is also a fairly active community of blogs and forums dedicated in whole or in part to Apache Derby.