Notifications and restrictions concerning the specific database systems

This chapter contains notifications and restrictions concerning the use of specific database systems, e.g. concerning the use of specific driver versions, the configuration, the unicode support, restrictions concerning functions etc. Further notes can be found in the respective submenus of Examples for linking different database systems.

In general

It is recommended to use a compatible JDBC driver version for the applied database version (see also Creating a JDBC driver module), except as noted otherwise.

Restrictions

Some databases have restrictions regarding the maximum name length (especially column names) or database line length. Therefore, always observe the following when creating content data structures:

1. All text input fields (or similar) should only be generated as large as really necessary.
2. All column names should be chosen as short as possible.
3. Language-dependent input fields should only be used if they are really required.
4. Not every database can store unicode characters in UTF-8 format. If you are planning to create multilingual projects with unicode characters, ensure that the database being used is unicode-capable and correspondingly configured.
5. To ensure that FirstSpirit works smoothly with a particular database, the encoding/character set of the FirstSpirit server should match that of the database (for example, they should both use UTF-8 or Latin-1 encoding). This is particularly important when using native database types (varchar, date, etc.). The configuration approach depends on which database is being used (for an example of configuration via the JDBC driver).
6. The number of columns should be kept as low as possible.
7. References to entries in external databases

Note about reference graph support for database content: The reference graph of a project is a central part of many FirstSpirit functions (e.g. “Show usages”). References to entries in external databases can only be added to the reference graph if numeric primary keys are used.

MySQL

Unicode: Unicode support from MySQL Version 5.

Further restrictions for MySQL databases (from version 4.x):

Big tables can not be stored http://bugs.mysql.com/bug.php?id=30295

“The maximum row length, except for VARBINARY, VARCHAR, BLOB and TEXT columns, is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes... InnoDB stores the first 768 bytes of a VARBINARY, VARCHAR, BLOB, or TEXT column in the row, and the rest into separate pages.”

This means: a table with 11 columns of the type TEXT or VARCHAR (>730) is to big for MySQL. This restriction applies to the following examples:

• 4 languages with 2 DOM input components and a language dependent string column (more than ca. 230 characters) or
• 2 languages with 5 DOM input components plus 1 language independent string column (more tha 320 characters) or
• 1 language with 11 string comlumns (each more than 730 characters)

Oracle

When using Oracle databases, the saving of database schemes and changes to them can take some time.

Unicode: When installing an Oracle database, UNICODE support should be activated to enable all international characters to be displayed. To do this, the following parameters must be set in the Create Database statement, when creating the Oracle instance:

NLS_CHARACTERSET: AL16UTF16
NLS_NCHAR_CHARACTERSET: AL32UTF8

The CHARACTERSET switch in the layer configuration (when using the Oracle19cLayer) should match this database setting.

To check whether the current configuration corresponds to the FirstSpirit recommendation, the following SQL call can be used:

SELECT PROPERTY_NAME,
 PROPERTY_VALUE
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME = 'NLS_CHARACTERSET'
 OR PROPERTY_NAME = 'NLS_NCHAR_CHARACTERSET'
ORDER BY PROPERTY_NAME;

If the configuration matches our recommendation, the output is:

PROPERTY_NAME           PROPERTY_VALUE
----------------------  --------------
NLS_CHARACTERSET        AL32UTF8
NLS_NCHAR_CHARACTERSET  AL16UTF16

Driver: For Oracle (starting with version 19c, with Oracle19cLayer), the JDBC driver of the series 16.9 should be used Oracle recommends use of the driver variant “ojdbc8.jar” when using Java 11.

Driver: For Oracle (before version 19c), the JDBC driver of the series 10.1 (ojdbc14_10.1.0.x.jar) should be used because problems can arise if the data type LONG is used with version 10.2 from 4000 characters on and UTF-8 coding. As an alternative, the compatibility mode for Oracle 9 LONG must be activated when using the driver 10.2, because LONG is deprecated since Oracle 9. For this purpose the parameter

jdbc.property.oracle.jdbc.RetainV9LongBindBehavior=true

must be added in the database configuration.

IBM DB2

Unicode: The UNICODE support should be activated during DB2 database creation.

Deleting columns: When using DB2, it is not possible to delete columns via the JDBC driver. However, the columns can be deleted in the database schema of the FirstSpirit SiteArchitect, but remain in the database.

Other notes: The configured heap size of DB2 is too small by default (128 x 4KB) and should be at least 1024x4KB. Execution of the following statement on the DB2 console is recommended:

db2 update db cfg for myDB using applheapsz 1024

PostgreSQL

Upgrades from PostgreSQL version 8 to version 9 are particularly prone to problems as far as the use of FirstSpirit is concerned. In such cases, the version of the database may not (may no longer) be compatible with the version of the JDBC driver used with FirstSpirit. Therefore, if you do encounter any problems with the JDBC driver, you should update it to a version that is compatible with the version of the database.