Frequently Asked Questions

1.1. What License is Derby under? (Quick Answer: ASL 2.0)

Derby is available under the Apache License, Version 2.0.

1.2. Who needs to file an Individual Contributor License Agreement (ICLA)?

The ASF desires contributors to submit an ICLA, but requires committers to do so. (See the "Contributor License Agreements" section on the Licenses page.) Contributors who are not committers are in a somewhat fuzzy spot. Small fixes posted to Jira probably don't require an ICLA, especially since Jira includes an upload option to "Grant license to ASF for inclusion in ASF works (as per the Apache Software License)". However, if you make a large contribution, or many contributions, a Derby committer will likely ask you to submit an ICLA.

2.1. How can I add a new entry to the Derby FAQ?

We welcome additions! Please add your new entry to DERBY-232. All you need is to create a Jira id if you don't already have one.

2.2. How do I search the Derby site?

The left-hand menu on the Derby web site includes a search box that searches the entire db.apache.org web site; so, that includes Derby and the other Apache DB Project sub-projects.

If you want to search all of apache.org, the ASF home page has a search box for all of apache.org.

If you want to restrict a search to a subset of the Derby site, for example to just the Derby manuals, one way to do that is to use a www.google.com domain search. For example, the search string below looks for all references to "create table" in the 10.13 documentation set:

"create table" site:db.apache.org/derby/docs/10.13

3.1. Where is the Derby source code?

The Apache Derby: Source Code page provides instructions for checking out the Derby source code from the SVN repository at Apache. You can also use the Apache ViewCVS interface to view Derby source code using your web browser.

Information about document source for the Derby manuals is covered in the Apache Derby: DITA-sourced documentation page.

Information about web site source is covered in the Apache Derby Web Site page.

3.2. Where can I read about Derby internals?

The Derby Engine papers and the HowItWorks Wiki page are good starting points. Also browse the derby-dev@db.apache.org mail list and post any questions you have.

3.3. Where are Apache Derby bugs logged?

Apache Derby uses Apache's Jira issue tracker to track bugs.

3.4. Who can create a new Apache Derby issue?

Anyone can browse Apache Derby issues. To log new issues, see the tips page, which describes how to create a userid for yourself and how to log useful bugs. To update existing issues, email your Jira userid to derby-dev@db.apache.org and request that it be added to the derby-developers Jira list.

3.5. How do you build Derby using Eclipse or Netbeans?

See the Eclipse and Netbeans instructions on the BuildingDerby Wiki page.

3.6. What does the "am" in org.apache.derby.client.am mean?

AM means "Abstract Machine". The code in the client.am directory implements non-public JDBC interfaces that are independent of any specific protocol.

4.1. Where can I ask questions about how to use Derby?

Please post questions about how to use Derby to the derby-user@db.apache.org mail list. Sometimes you might receive a response that refers you to documentation, to a page on the Derby web site, or to another post on the mail list. Please don't be offended! We are constantly working to improve the available documentation. And if the referenced post/page/documentation doesn't help, feel free to ask for more help.

4.2. Where can I download the Derby jar files?

You can download the Derby jar files from the Derby Downloads page.

4.3. Where can I find the Derby documentation?

Derby documentation is available in PDF and HTML format from http://db.apache.org/derby/manuals/.

4.4. Can you run Derby on small devices?

Starting in 10.1, Derby added support for J2ME/CDC/Foundation with JSR169 as the JDBC api. For more information, see the Reference Guide. Starting with 10.11, Derby added support for running on the small Java 8 compact profile 2 platform.

4.5. Can I use Derby on Java release X?

See the Derby Downloads page for the compatibility between Derby and Java versions.

4.6. Does Derby have a mode that causes it to run entirely in memory?

Of course! For more information, start here.

4.7. Does Derby have a GUI?

No, Derby does not include a GUI. However, the web site includes a writeup on how to use SQuirreL SQL with Derby, and the UsesOfDerby Wiki page list other GUIs that work with Derby.

4.8. How do you specify database location?

You can specify a database name using an absolute or relative path. Details are in the Developer's Guide and helpful tips are on the Derby Wiki.

4.9. Can you run Derby as a Windows service?

The Derby Wiki provides pointers to information on how to run Derby as a Windows service.

4.10. How do I find out which version of Derby I am using?

You can use the sysinfo tool or the JDBC API to get version information from your Derby installation. See the VersionInfo wiki page for more details.

5.1. Why can't my SQL function/procedure pass a NULL INTEGER?

Either declare the function with RETURNS NULL ON NULL INPUT or define the Java method signature in the external name clause. See the "Create function problem" derby-user mail list topic.

5.2. Does Derby support a LIMIT command?

Derby does not support the LIMIT syntax. However, Derby 10.4 added the ROW_NUMBER function and Derby 10.7 added the OFFSET and FETCH clauses.

Derby also supports limiting the number of rows returned by a query through JDBC. For example, to fetch the first 5 rows of a large table:

Statement stmt = con.createStatement();
stmt.setMaxRows(5);
ResultSet rs = stmt.executeQuery("SELECT * FROM myLargeTable");

Some related tuning tips are available in this external article.

Starting with the 10.4.1.3 release Derby also supports limiting the number of rows using the ROW_NUMBER function.

For example, to fetch the first 5 rows of a large table:

SELECT * FROM (
SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
FROM myLargeTable
) AS tmp
WHERE rownum <= 5;

The ROW_NUMBER function can also be used to select a limited number of rows starting with an offset, for example:

SELECT * FROM (
SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
FROM myLargeTable
) AS tmp
WHERE rownum > 200000 AND rownum <= 200005;

For more information, refer to the ROW_NUMBER built-in function in the Derby Reference Manual (available from the Documentation page). Development notes are available on the OLAPRowNumber wiki page.

The LIMIT keyword is not defined in the SQL standard, and is currently not supported.

5.3. Why do I get the error 'schema does not exist'?

The current schema for any connection defaults to a schema corresponding to the user name. If no user name is supplied then the user name (and hence current schema) defaults to APP.

However even though the current schema is set to the user name, that schema may not exist. A schema is only created by CREATE SCHEMA or creating an object (table etc.) in that schema (this is implicit schema creation).

The one exception to this is the APP schema, which is always created, though applications should not depend on that.

So you will see the schema not exists error if your application tries to access the current schema before any objects have been created in it. Possibilities are you try to perform a DROP TABLE before creating it, or try to SELECT from a table to see if it should be created or not, or when there is a problem with the sql for the very first object you try to create in a particular schema and no explicit CREATE SCHEMA was issued.

5.4. I get a lock timeout error. How do I debug this?

To debug lock timeout errors, set derby.locks.monitor=true and derby.locks.deadlockTrace=true in derby.properties. This will print the lock table information to the derby.log when the timeout happens. If you also set derby.language.logStatementText=true, then all the statements executed on the system will be written out to the derby.log. Statement information also includes the transaction id on which the statement is being executed. You can use the transaction id holding the lock in the lock table dump to do a reverse search for the transaction id in derby.log, making it possible to narrow down which statements may be holding the locks.

Further information is available on the LockDebugging wiki page.

5.5. Can Derby generate unique identifiers like sequences?

SEQUENCE support was added to Derby in release 10.6.

Also, Derby supports generated "identity" columns; examples are in the Reference Guide. The IDENTITY_VAL_LOCAL function returns the most recently assigned number.

6.1. Can you use Derby for client-server applications?

Yes, you can use derby for client-server applications. Starting in 10.1 Derby included the Derby Network Client jdbc driver. The UsesOfDerby Wiki pages lists additional drivers, including C-JDBC and the IBM DB2 JDBC Universal Driver.

6.2. Does Derby include a client-server ("network") jdbc driver?

Yes, Apache Derby has a JDBC driver that works with the Derby Network Server. It is called the Derby Network Client and it became available starting with Derby 10.1.

6.3. How can I get the message text for an error using the DB2 JDBC Universal Driver?

Set the connection attribute retrieveMessagesFromServerOnGetMessage=true. For details, see the "Error Messages with Network server and the IBM DB2 Universal JDBC Driver" mail list topic.

6.4. Can you execute a query that spans two Derby databases across different JVMs?

XA support using Network Server allows you to do this and the Derby Network Client, new in 10.1, supports this functionality. An example of establishing an XA connection is in the Admin and Server Guide.

6.5. Are there any tips to make Derby go faster?

Here are two performance tips for coding applications:

Use PreparedStatement with dynamic markers('?') instead of Statement. This will be faster since the statement does not need to be recompiled every time. See the Use prepared statements with substitution parameters and Avoid compiling SQL statements sections of the Tuning Derby guide.
Avoid inserts in autocommit mode if possible. Autocommitted inserts flush of the log to the disk for each insert statement. The commit does not return until a physical disk write has been executed. So to speed things up, run in autocommit false mode, execute a number of inserts in one transaction, and then explicitly issue a commit.

6.6. Where is an example that shows how to insert a CLOB?

The Reference Guide includes an example that shows how to insert a CLOB. Also this derby-user post provides another example.

6.7. Does Derby support JDBC 4.0?

From release 10.2.2.0 onward, Derby distributions include compiled versions of JDBC4 drivers.