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 derby.properties 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:

derby.storage.pageSize=8192

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 derby.properties file, since the property has not been set any other way.

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

java -Dderby.system.home=c:\system_directory 
    -Dderby.storage.pageSize=4096 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 = 
  conn.prepareCall("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(?, ?)"); 
cs.setString(1, "derby.storage.pageSize"); 
cs.setString(2, "32768"); 
cs.execute(); 
cs.close();

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 derby.properties file.

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

If you remove the derby.properties 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.