Using in-memory databases

For testing and developing applications, or for processing transient
or reproducible data, you can use
Derby's in-memory database
facility.

An in-memory database resides completely in main memory, not in the file
system. It is useful for testing and developing applications, when you may want
to create and discard databases that will never be used again. It is also
useful when you need to process only temporary or reproducible data.

If you have the required memory available, you may also benefit from faster
processing (no disk I/O) and from the simplicity of not having to explicitly
delete databases you have finished with.

Creating an in-memory database

To create an in-memory database, specify memory as the JDBC
subsubprotocol. For example, to create an in-memory database named
myDB using the embedded driver, use the following connection
URL:

jdbc:derby:memory:myDB;create=true

For the network client driver, use the following connection URL. Because
the client driver does not understand the memory
subsubprotocol, you must include it in the database name:

jdbc:derby://myhost:1527/memory:myDB;create=true

Be careful to specify a colon (:) after
memory.

Referring to in-memory databases

When you create or refer to an in-memory database, any path that is not
absolute is interpreted as relative to the system directory, just as with file
system databases. For example, if the system directory is
C:\myderby, the following paths are regarded as
equivalent:

jdbc:derby:memory:db
jdbc:derby:memory:C:\myderby\db

Similarly, Derby treats
the following URLs as names for the same in-memory database:

Using in-memory databases

When you use an in-memory database, you need to make sure to configure the
heap and the Derby page
cache size. See "Configure
Derby to use an in-memory
database" in Tuning Java DB for details.

For examples of how to use an in-memory database, see some of the
ij command examples in the
Java DB Tools and Utilities Guide (execute
and async, for example).

Removing an in-memory database

To remove an in-memory database, use the connection URL attribute drop
as follows:

jdbc:derby:memory:myDB;drop=true

jdbc:derby://myhost:1527/memory:myDB;drop=true

You can shut down an in-memory database using the shutdown=true
attribute before you drop the database, but this is optional. Dropping the
database also performs the shutdown.

When you drop the database,
Derby issues what appears to
be an error but is actually an indication of success. You need to catch error
08006, as described in "The WwdEmbedded program" in
Getting Started with Java DB.

An in-memory database is automatically removed if any of the following
happens:

The Java Virtual Machine (JVM) is shut down normally (for example, if you
exit the ij tool)

The JVM crashes

The machine you are running on crashes or shuts down

Persisting an in-memory database

If you create an in-memory database and then decided that you want to keep it
after all, you can use one of the backup system procedures
(SYSCS_UTIL.SYSCS_BACKUP_DATABASE, for example) to persist it. You can then boot
it as an in-memory database at a later time, or use it as a normal file system
database. See "Backing up and restoring databases" in
Java DB Server and Administration Guide for information on using the
backup procedures.