Properties case study

Derby allows you a lot of freedom in configuring your system. This freedom can be confusing if you do not understand how properties work. You also have the option of not setting any properties and instead using the Derby defaults, which are tuned for a single-user embedded system.

Imagine the following scenario of an embedded environment:

Your system has a file, a text file in the system directory, which you have created and named system_directory. Your databases have also been created in this directory. The properties file sets the following property:
You start up your application, being sure to set the derby.system.home property appropriately:
java -Dderby.system.home=c:\system_directory MyApp

The command lines in this example assume that you are using a Windows system.

You then create a new table:
CREATE TABLE table1 (a INT, b VARCHAR(10))

Derby takes the page size of 8192 from the system-wide properties set in the file, since the property has not been set any other way.

You shut down and then restart your application, setting the value of to 4096 programmatically, as a parameter to the JVM command line:

java -Dderby.system.home=c:\system_directory MyApp

CREATE TABLE anothertable (a INT, b VARCHAR(10))

The page size for the anothertable table will be 4096 bytes.

You establish a connection to the database and set the value of the page size for all new tables to 32768 as a database-wide property:
CallableStatement cs = 
cs.setString(1, ""); 
cs.setString(2, "32768"); 

You then create a new table that automatically inherits the page size set by the property:

CREATE TABLE table2 (a INT, b VARCHAR(10))

The page size for the table2 table is 32768 bytes.

You shut down the application, then restart, this time forgetting to set the system-wide property programmatically (as a command-line option to the JVM):
java -Dderby.system.home=c:\system_directory MyApp
You then create another table:
CREATE TABLE table4 (a INT, b VARCHAR(10))

Derby uses the persistent database-wide property of 32768 for this table, since the database-wide property set in the previous session is persistent and overrides the system-wide property set in the file.

What you have is a situation in which three different tables each get a different page size, even though the file remained constant.

If you remove the file from the system or remove the database from its current location (forgetting to move the file with it), you could get yet another value for a new table.

To avoid this situation, be consistent in the way you set properties.

Related concepts
Properties overview
Setting Derby properties