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.
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.
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:
jdbc:derby:memory:/home/myname/db jdbc:derby:memory:/home/myname/../myname/db
Conventions for specifying the database path name has more information on database paths.
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 Derby for details.
For examples of how to use an in-memory database, see some of the ij command examples in the Derby Tools and Utilities Guide (execute and async, for example).
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 Derby.
If user authentication and SQL authorization are both enabled, only the database owner can drop the database. (See the Derby Security Guide for details on authentication and authorization.)
An in-memory database is automatically removed if any of the following happens:
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 Derby Server and Administration Guide for information on using the backup procedures.