# Derby properties

The Derby properties are used for configuring the system and database, as well as for diagnostics such as logging statements, and monitoring and tracing locks.

The table later in this topic lists all the core Derby properties.

Derby also supports a number of properties that are specific to the Network Server. For information about these properties, see "Setting Network Server properties" in the Derby Server and Administration Guide.

For information on how to set Derby properties, see "Setting Derby properties" in the Derby Developer's Guide.

Note: When setting properties that have boolean values, be sure to trim extra spaces around the word true. Extra spaces around the word true cause the property to be set to false, regardless of its previous setting.

To disable or turn off a database-wide property setting, set its value to null. To determine the result of this action, recall that the search order for properties is as follows (as stated in "Precedence of properties" in the Derby Developer's Guide).

1. System-wide properties set programmatically (as a command-line option to the JVM when starting the application or within application code); not consulted if derby.database.propertiesOnly is set to true
2. Database-wide properties
3. System-wide properties set in the derby.properties file; not consulted if derby.database.propertiesOnly is set to true

Setting the database-wide property to null has the effect of removing the property from the list of database properties and restoring the system property setting from derby.properties if there is one. As always, if no value can be determined from the search, the built-in default applies.

For example, the following code fragment turns off a previous database-wide setting of the derby.database.fullAccessUsers property:

    Statement s = conn.createStatement();
    s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
        "'derby.database.fullAccessUsers', null)");

If the property is a static one, the null setting does not take effect until you reboot the database. Moreover, the static property derby.database.sqlAuthorization cannot be disabled after it has been enabled, even with a reboot.

The following table summarizes the general Derby properties. In the Scope column of this table, S stands for system-wide, D stands for database-wide, and C indicates the value persists with newly created conglomerates.

Table 1. Derby properties
Property | Scope | Dynamic or Static
--- | --- | ---
derby.authentication.builtin.algorithm | S, D | Dynamic
derby.authentication.builtin.iterations | S, D | Dynamic
derby.authentication.builtin.saltLength | S, D | Dynamic
derby.authentication.ldap.searchAuthDN | S, D | Static
derby.authentication.ldap.searchAuthPW | S, D | Static
derby.authentication.ldap.searchBase | S, D | Static
derby.authentication.ldap.searchFilter | S, D | Static
derby.authentication.native.passwordLifetimeMillis | S, D | Static
derby.authentication.native.passwordLifetimeThreshold | S, D | Static
derby.authentication.provider | S, D | Static
derby.authentication.server | S, D | Static
derby.connection.requireAuthentication | S, D | Static
derby.database.classpath | D | Dynamic
derby.database.defaultConnectionMode | S, D | See the main page for this property for information about when changes to the property are dynamic
derby.database.forceDatabaseLock | S | Static
derby.database.fullAccessUsers | S, D | See the main page for this property for information about when changes to the property are dynamic
derby.database.noAutoBoot | D | Static
derby.database.propertiesOnly | D | Dynamic
derby.database.readOnlyAccessUsers | S, D | See the main page for this property for information about when changes to the property are dynamic
derby.database.sqlAuthorization | S, D | Static
derby.infolog.append | S | Static
derby.jdbc.xaTransactionTimeout | S, D | Dynamic
derby.language.logQueryPlan | S | Static
derby.language.logStatementText | S, D | Static
derby.language.sequence.preallocator | S, D | Static
derby.language.statementCacheSize | S, D | Static
derby.locks.deadlockTimeout | S, D | Dynamic
derby.locks.deadlockTrace | S, D | Dynamic
derby.locks.escalationThreshold | S, D | Dynamic
derby.locks.monitor | S, D | Dynamic
derby.locks.waitTimeout | S, D | Dynamic
derby.replication.logBufferSize | S | Static
derby.replication.maxLogShippingInterval | S | Static
derby.replication.minLogShippingInterval | S | Static
derby.replication.verbose | S | Static
derby.storage.indexStats.auto | S, D | Static
derby.storage.indexStats.log | S, D | Static
derby.storage.indexStats.trace | S, D | Static
derby.storage.initialPages | C | Static
derby.storage.minimumRecordSize | S, D, C | Dynamic
derby.storage.pageCacheSize | S | Static
derby.storage.pageReservedSpace | S, D, C | Dynamic
derby.storage.pageSize | S, D, C | Dynamic
derby.storage.rowLocking | S, D | Static
derby.storage.tempDirectory | S, D | Static
derby.storage.useDefaultFilePermissions | S | Dynamic
derby.stream.error.extendedDiagSeverityLevel | S | Static
derby.stream.error.field | S | Static
derby.stream.error.file | S | Static
derby.stream.error.logBootTrace | S | Static
derby.stream.error.logSeverityLevel | S | Static
derby.stream.error.method | S | Static
derby.stream.error.rollingFile.count | S | Static
derby.stream.error.rollingFile.limit | S | Static
derby.stream.error.rollingFile.pattern | S | Static
derby.stream.error.style | S | Static
derby.system.bootAll | S | Static
derby.system.durability | S | Static
derby.system.home | S | Static
derby.user.UserName | S, D | Dynamic
DataDictionaryVersion | D | Neither; value can be retrieved, but not set

There are additional properties associated with the Derby tools. For more information about tool-specific properties, see the Derby Tools and Utilities Guide.

- derby.authentication.builtin.algorithm
  The derby.authentication.builtin.algorithm property specifies the message digest algorithm to use to protect the passwords that are stored in the database when using NATIVE authentication.
- derby.authentication.builtin.iterations
  The derby.authentication.builtin.iterations property specifies the number of times to apply the hash function (which is specified by the derby.authentication.builtin.algorithm property) on the credentials.
- derby.authentication.builtin.saltLength
  The derby.authentication.builtin.saltLength property specifies the number of bytes of random salt that will be added to users' credentials before hashing them.
- derby.authentication.ldap.searchAuthDN
  Along with derby.authentication.ldap.searchAuthPW, the derby.authentication.ldap.searchAuthDN property indicates how Derby should bind with the LDAP directory server to do searches for user DN (distinguished name).
- derby.authentication.ldap.searchAuthPW
  Along with derby.authentication.ldap.searchAuthDN, the derby.authentication.ldap.searchAuthPW property indicates how Derby should bind with the directory server to do searches in order to retrieve a fully qualified user DN (distinguished name).
- derby.authentication.ldap.searchBase
  The derby.authentication.ldap.searchBase property specifies the root DN of the point in your hierarchy from which to begin a guest or anonymous search for the user's DN.
- derby.authentication.ldap.searchFilter
  The derby.authentication.ldap.searchFilter property specifies the search filter to use to determine what constitutes a user (and other search predicate) for Derby searches for a full DN during user authentication.
- derby.authentication.native.passwordLifetimeMillis
  The derby.authentication.native.passwordLifetimeMillis property specifies the number of milliseconds a NATIVE authentication password remains valid after being created, reset, or modified.
- derby.authentication.native.passwordLifetimeThreshold
  The derby.authentication.native.passwordLifetimeThreshold property specifies the threshold that triggers a connection-time warning that a password is about to expire.
- derby.authentication.provider
  The derby.authentication.provider property specifies the authentication provider for Derby user authentication.
- derby.authentication.server
  The derby.authentication.server property specifies the location of the external directory service that provides user authentication for the Derby system as defined with the derby.authentication.provider property.
- derby.connection.requireAuthentication
  The derby.connection.requireAuthentication property turns on user authentication for Derby.
- derby.database.classpath
  The derby.database.classpath property specifies the classpath that Derby should use when searching for jar files in a database.
- derby.database.defaultConnectionMode
  The derby.database.defaultConnectionMode property, one of the user authorization properties, defines the default connection mode for users of the database or system for which this property is set.
- derby.database.forceDatabaseLock
  The derby.database.forceDatabaseLock property, if set to true on some platforms, prevents Derby from booting a database if a db.lck file is present in the database directory.
- derby.database.fullAccessUsers
  The derby.database.fullAccessUsers property, one of the user authorization properties, specifies a list of users to which full (read-write) access to a database is granted.
- derby.database.noAutoBoot
  The derby.database.noAutoBoot property specifies that a database should not be automatically booted at startup time.
- derby.database.propertiesOnly
  The derby.database.propertiesOnly property, when set to true, ensures that database-wide properties cannot be overridden by system-wide properties.
- derby.database.readOnlyAccessUsers
  The derby.database.readOnlyAccessUsers property, one of the user authorization properties, specifies a list of users to which read-only access to a database is granted.
- derby.database.sqlAuthorization
  The derby.database.sqlAuthorization property, one of the user authorization properties, enables the SQL standard authorization mode for the database or system on which this property is set.
- derby.infolog.append
  The derby.infolog.append property specifies whether to append to or overwrite (delete and recreate) the derby.log file when the Derby engine is started.
- derby.jdbc.xaTransactionTimeout
  The derby.jdbc.xaTransactionTimeout property specifies the default value of the XA transaction timeout that is used when a user either does not specify the XA transaction timeout or requests to use the default value.
- derby.language.logQueryPlan
  The derby.language.logQueryPlan property, when set to true, tells Derby to write the query plan information into the derby.log file for all executed queries.
- derby.language.logStatementText
  The derby.language.logStatementText property, when set to true, tells Derby to write the text and parameter values of all executed statements to the information log at the beginning of execution.
- derby.language.sequence.preallocator
  The derby.language.sequence.preallocator property specifies how many values to preallocate for sequences.
- derby.language.statementCacheSize
  The derby.language.statementCacheSize property defines the size, in number of statements, of the database statement cache (prepared statements kept in memory).
- derby.locks.deadlockTimeout
  The derby.locks.deadlockTimeout property determines the number of seconds after which Derby checks whether a transaction waiting to obtain a lock is involved in a deadlock.
- derby.locks.deadlockTrace
  The derby.locks.deadlockTrace property causes a detailed list of locks at the time of a deadlock or a timeout to be written to the error log (typically the file derby.log).
- derby.locks.escalationThreshold
  The derby.locks.escalationThreshold property is used by the Derby system at runtime in determining when to attempt to escalate locking for at least one of the tables involved in a transaction from row-level locking to table-level locking.
- derby.locks.monitor
  The derby.locks.monitor property specifies that all deadlock errors are logged to the error log.
- derby.locks.waitTimeout
  The derby.locks.waitTimeout property specifies the number of seconds after which Derby aborts a transaction when it is waiting for a lock.
- derby.replication.logBufferSize
  The derby.replication.logBufferSize property specifies the size of the replication log buffers in bytes.
- derby.replication.maxLogShippingInterval
  The derby.replication.maxLogShippingInterval property specifies, in milliseconds, the longest interval between two consecutive shipments of the transaction log from the master to the slave.
- derby.replication.minLogShippingInterval
  The derby.replication.minLogShippingInterval property specifies, in milliseconds, the shortest interval between two consecutive shipments of the transaction log from the master to the slave.
- derby.replication.verbose
  The derby.replication.verbose property specifies whether replication messages are written to the Derby log.
- derby.storage.indexStats.auto
  The derby.storage.indexStats.auto property, if set to true (the default), specifies that outdated index cardinality statistics are updated automatically during query compilation.
- derby.storage.indexStats.log
  The derby.storage.indexStats.log property, if set to true, specifies that the thread that updates index cardinality statistics during query compilation will write messages to the Derby system log (derby.log) every time it performs a task.
- derby.storage.indexStats.trace
  The derby.storage.indexStats.trace property allows you to enable more detailed logging of index cardinality statistics than the derby.storage.indexStats.log property provides.
- derby.storage.initialPages
  The derby.storage.initialPages property creates a Derby table or index with a number of pages already pre-allocated.
- derby.storage.minimumRecordSize
  The derby.storage.minimumRecordSize property indicates the minimum user row size in bytes for on-disk database pages for tables when you are creating a table.
- derby.storage.pageCacheSize
  The derby.storage.pageCacheSize property defines the size, in number of pages, of the data page cache in the database (data pages kept in memory).
- derby.storage.pageReservedSpace
  The derby.storage.pageReservedSpace property defines the percentage of space reserved for updates on an on-disk database page for tables only (not indexes) and indicates the percentage of space to keep free on a page when inserting.
- derby.storage.pageSize
  The derby.storage.pageSize property defines the page size, in bytes, for on-disk database pages for tables or indexes used during table or index creation.
- derby.storage.rowLocking
  The derby.storage.rowLocking property, if set to true, enables row-level locking.
- derby.storage.tempDirectory
  The derby.storage.tempDirectory property defines the location on disk for temporary file space needed by Derby for performing large sorts and deferred deletes and updates.
- derby.storage.useDefaultFilePermissions
  The derby.storage.useDefaultFilePermissions property overrides the default access to files on systems that run on the Java SE 7 platform.
- derby.stream.error.extendedDiagSeverityLevel
  The derby.stream.error.extendedDiagSeverityLevel property specifies whether thread dump information and extended diagnostic information are created, and at what level, in the event of a system crash or session error.
- derby.stream.error.field
  The derby.stream.error.field property specifies a static field that references a stream to which the error log is written.
- derby.stream.error.file
  The derby.stream.error.file property specifies the name of the file to which the error log is written.
- derby.stream.error.logBootTrace
  The derby.stream.error.logBootTrace property specifies whether boot errors are written to the Derby log file.
- derby.stream.error.logSeverityLevel
  The derby.stream.error.logSeverityLevel property specifies which errors are logged to the Derby error log (typically the derby.log file).
- derby.stream.error.method
  The derby.stream.error.method property specifies a static method that returns a stream to which the Derby error log is written.
- derby.stream.error.rollingFile.count
  The derby.stream.error.rollingFile.count property specifies the number of rolling log files to permit before deleting the oldest file when rolling to the next file, if derby.stream.error.style is set to rollingFile.
- derby.stream.error.rollingFile.limit
  The derby.stream.error.rollingFile.limit property specifies the number of bytes to which to limit each rolling log file before it is rolled over to the next file, if derby.stream.error.style is set to rollingFile.
- derby.stream.error.rollingFile.pattern
  The derby.stream.error.rollingFile.pattern property specifies the naming pattern to use for the rolling log files, if derby.stream.error.style is set to rollingFile.
- derby.stream.error.style
  The derby.stream.error.style property specifies that the Derby log file should be rolled over when it reaches a certain size.
- derby.system.bootAll
  The derby.system.bootAll property specifies that all databases in the directory specified by the derby.system.home property should be automatically booted at startup time.
- derby.system.durability
  The derby.system.durability property changes the default durability of Derby to improve performance at the expense of consistency and durability of the database.
- derby.system.home
  The derby.system.home property specifies the Derby system directory, which is the directory that contains subdirectories holding databases that you create and the text file derby.properties.
- derby.user.UserName
  The derby.user.UserName property caches user DNs locally when derby.authentication.provider is set to LDAP and derby.authentication.ldap.searchFilter is set to derby.user.
- DataDictionaryVersion
  The DataDictionaryVersion property shows the version of the on-disk data in the database.

Parent topic: Derby property reference
Related concepts
Scope of Derby properties
Dynamic and static properties