Description of the optional parameters

<database>.jdbc.SCHEMA

This parameter defines the schema on the DBMS (Database Management System) to be used by FirstSpirit. A schema is also frequently called a “database”. Under Oracle, it corresponds to one normal user account, in other DBMS, e.g. PostgreSQL, a normal user account can also include several schemata.

If this parameter is defined, it is a default layer. In a FirstSpirit project to which default layers only are assigned, a FirstSpirit user cannot create any new additional schemata. Only the FirstSpirit administrator can add further layers to the project.

To enable the creation of other schemata for FirstSpirit users too, a so-called DBA layer is required, but which in most DBMS requires DBA rights (DBA = Database Administrator). The SCHEMA parameter is not entered in a DBA layer. The FirstSpirit user can use a DBA layer to independently generate new default layers.

For further details of the differences between default layers and DBA layers and their advantages and disadvantages, please refer to the FirstSpirit Online documentation.

Example:

database.jdbc.SCHEMA=goodsdatabase

<database>.jdbc.POOLMAX

FirstSpirit uses a separate pool instance for every application, e.g., a schema node in a project, a deployed web application (e.g., FirstSpirit DynamicDatabaseAccess), etc. POOLMAX defines the maximum number of connections to the database that can be open in parallel.

content1.jdbc.POOLMAX=15

POOLMAX must be at least the value of POOLMIN + 4. If it is not, the value is adjusted automatically and a corresponding warning is logged.

<database>.jdbc.POOLMIN

POOLMIN defines the minimum number of available DB reserved for each pool.

content1.jdbc.POOLMIN=10

If a value is not specified, 5 DB connections are reserved for each pool.

<database>.jdbc.FETCH_CONNECTION_TIMEOUT

When the value of POOLMAX is reached, there is a default wait time of 5 seconds for a free connection. After this, an exception occurs at the user (e.g., client, generation). The wait time for a free connection is configured with the FETCH_CONNECTION_TIMEOUT parameter (the value is set in milliseconds).

content1.jdbc.FETCH_CONNECTION_TIMEOUT=6000

<database>.jdbc.POOLCYCLE

The time interval (in seconds) during which FirstSpirit removes expired DB connections from the pool is defined via parameter POOLCYCLE. A DB connection is classified as expired when either the POOLTIMEOUT or the CONNECTIONTIMEOUT has elapsed. If a value is not specified, the minimum value accepted by FirstSpirit – 90 seconds – is set.

content1.jdbc.POOLCYCLE=120

<database>.jdbc.POOLTIMEOUT

The time interval (in seconds) during which the FirstSpirit Server can use a DB connection is defined via parameter POOLTIMEOUT. If the server does not release this connection after the time interval has elapsed, it is closed automatically. If a value is not specified, the value "180” is set by default.

content1.jdbc.POOLTIMEOUT=240

<database>.jdbc.CONNECTIONTIMEOUT

The time interval (in seconds) after which a DB connection is considered as old by the FirstSpirit Server and closed is defined via parameter CONNECTIONTIMEOUT. If a value is not specified, a timeout of 30 minutes (value 1800) is set by default. A value <= 0 deactivates the timeout. This value must always be less than the idle timeout des of the database server, which amounts normally some hours.
Example for setting the timeout to 15 minutes:

content1.jdbc.CONNECTIONTIMEOUT=900

<database>.jdbc.CONNECTIONRETRY

During SQL query execution FirstSpirit tries to use a connection from the connection pool. If there are no free connections, an attempt is made to establish a new database connection. Requests can be rejected (e.g. due to the database configuration). The number of connection attempts to the database is defined via parameter CONNECTIONRETRY. If the number is exceeded, a failed connection attempt is aborted with a warning message. If a value is not specified, the value "5” is set by default.

content1.jdbc.CONNECTIONRETRY=3

<database>.jdbc.CONNECTIONRETRYCYCLE

After a failed attempt to establish a database connection, the ORMapper waits for the time specified in CONNECTIONRETRYCYCLE (in ms) to elapse before trying again. If a value is not specified, the value "300” is set by default.

content1.jdbc.CONNECTIONRETRYCYCLE=500

<database>.jdbc.MAXSTRINGLENGTH

The parameter MAXSTRINGLENGTH determines the maximum number of characters of a VARCHAR column when creating a new database table.

If a higher value is specified for a string attribute than the one defined via parameter MAXSTRINGLENGTH, this string attribute is stored as a different data type (e.g. as BLOB, CLOB or TEXT) in the database. If no value is set here, a default value is set depending on the database used.

content1.jdbc.MAXSTRINGLENGTH=8191

The parameter MAXSTRINGLENGTH is supported for the following databases:

• PostgreSQL
• Oracle 19c

The following applies to PostgreSQL:

• Default value: If no value is defined using MAXSTRINGLENGTH, the default value 255 is used.
• Maximum value: It is not recommended to define values higher than 65534.

The following applies to Oracle 19c:

• Default value: If no value is defined using MAXSTRINGLENGTH, the default value 2000 is used.
• Recommendation: Depending on the character set (parameter jdbc.oracle.CHARACTERSET), it is useful to adjust the character length. For Oracle 19c, for example, the character set AL32UTF8 with character length 8191 is recommended. The recommended value for the respective character set can be found in the table below.
• Prerequisite: In order to be able to store more than 4000 bytes (with AL32UTF8 with up to 4 bytes per character, in the worst case only 1000 characters) per column, the value MAX_STRING_SIZE must be set to EXTENDED in the database.
• Maximum value: If values are specified for the parameter that are higher than the recommended value of the specified character set (parameter jdbc.oracle.CHARACTERSET), a corresponding message is logged in the server log. The maximum value for the respective character set can be found in the table below.
  • For values that are higher than the maximum value of the specified character set, an ERROR is logged and the value is reduced to the maximum value.
  • For values larger than the recommended value and smaller than the maximum value of the specified character set, a corresponding WARN message is logged, in which the recommended value is displayed.

Values to be used for Oracle 19c (depending on the character set):

<database>.jdbc.JNDI

If the ORMapper runs in a web container or application server, it is possible to establish a database connection via a data source. Therefore, the ORMapper uses the pooling capacity of the web container. The parameter JNDI determines the JNDI name of the used data source.

database.jdbc.JNDI=java:comp/env/jdbc/ORMapper

<database>.jdbc.isolation

Transactions running simultaneously that make changes to data in the database could result in undefined states. Transaction isolation should be configured to prevent a running transaction from being changed to an undefined state by another transaction running at the same time (due to a change in the data used). Different isolation levels can be configured using this parameter. The following are generally supported:

• READ_COMMITTED: lowest isolation level.
• REPEATABLE_READ: medium isolation level.
• SERIALIZABLE: highest isolation level (default value).

Note: Not every database management system supports all isolation levels. For instance, OracleDB 11 supports only the READ_COMMITTED and SERIALIZABLE isolation levels, but not REPEATABLE_READ. In certain cases, you should consult the documentation of the relevant database for more information.

database.jdbc.isolation=READ_COMMITTED

<database>.jdbc.property

With different jdbc.property parameters further connection settings can be made. Examples:

There are also database-specific properties, like for PostgreSQL:

jdbc.property.defaultRowFetchSize=10000

or for Oracle Database:

jdbc.property.oracle.net.ssl_version=1.2

<database>.jdbc.property.defaultRowFetchSize (PostGreSQL only)

To optimize the memory management when using a PostGreSQL database a defaultRowFetchSize of 5000 is passed to the driver.

This default value can be changed in the database configuration (in the ServerManager under Server properties - Databases) by the parameter jdbc.property.defaultRowFetchSize.

The parameter jdbc.property.defaultRowFetchSize determines the number of rows fetched in a ResultSet by one fetch with trip to the database.

High values are more performant for large database queries, but also require more memory in the Java VM and can lead to memory problems up to OutOfMemory exceptions.

For further information see: https://jdbc.postgresql.org/documentation/head/connect.html

module

If a FirstSpirit module is used for JDBC driver, you must specify the name of the JDBC driver module by using this parameter.

database.module=JDBC_PostgreSQL_9_1