Have additional connections to Derby (read-only)

Question

What I want to do: My application has a full connection to a Derby DB, and I want to poke around in the DB (read-only) in parallel (using a different tool).

I'm not sure how Derby actually works internally, but I understand that I can have only 1 active connection to a Derby DB. However, since the DB is only consisting of files on my HDD, shouldn't I be able to open additional connections to it, in read-only mode?

Are there any tools to do just that?

Answer 1

There are two possibilities how to run Apache Derby DB.

1. Embedded: You run DB within your application → only one connection possible
2. Client: You start DB as server in separate process → classic DB with many connections

You can recognize the type upon driver size. If the driver has more then 2MB that you use embedded version.

Update

When you startup the derby engine (server or embedded) it gets exclusive access to database files.

If you need to access a single database from more than one Java Virtual Machine (JVM), you will need to put a server solution in place. You can allow applications from multiple JVMs that need to access that database to connect to the server.

For details see Double-booting system behavior.

Answer 2

I realize this is an old question, but I thought I might add a little more detail on a solution since links in the currently accepted answer are broken.

It is possible to run the Derby Network Server within a JVM that is using the embedded database already. The code that is using the embedded Derby database doesn't need to change anything and can keep using the DB as is, but with the Derby Network Server started, other programs can connect to derby and access the database.

All you need to do is ensure that derbynet.jar is on the classpath

And then you can do one of the following

• Include the following line in the derby.properties file: derby.drda.startNetworkServer=true

• Specify the property as a system property at java start java -Dderby.drda.startNetworkServer=true

• You can use the NetworkServerControl API to start the Network Server from a separate thread within a Java application: NetworkServerControl server = new NetworkServerControl(); server.start (new PrintWriter(System.out));

More details here: http://db.apache.org/derby/docs/10.9/adminguide/tadminconfig814963.html

Keep in mind that doing this does not enable any security on this connection, so it is not a good idea to do this on a production system. It is possible to add security though and that is documented here: http://db.apache.org/derby/docs/10.9/adminguide/cadminnetservsecurity.html

Answer 3

Two other ideas:

1. In your application, shut down the database and close the connection when the database is not actively in use. Then your application won't interfere with another tool which is trying to open the database.
2. Make a copy of your database, by taking a backup (you can do this while the database is open by your application), then restore that backup to a separate place on your disk. Then you can use another tool to access the copied database at your ease.

Answer 4

If you can afford the memory and do not need up-to-date data, then you can access read-only databases from multiple JVMs by creating in-memory copies:

ij> connect 'jdbc:derby:memory:memdb;restoreFrom=mydb';