Setting up the database

This section describes how to prepare the databases which are needed for ConSol CM and the CMRF. This must be done before installing the applications.

The required steps are the same for both applications. You just need to adjust the names of the database and user.

Examples:

• ConSol CM database name: cmdatabase
• ConSol CM database user: cmuser
• CMRF database name: cmrfdatabase
• CMRF database user: cmrfuser

We recommend to install a database management tool for your database server:

• pgAdmin
• MySQL Workbench
• Microsoft SQL Server Management Studio
• Oracle SQL Developer

PostgreSQL

Global settings

Make the following settings in the postgresql.conf file in the data directory of the PostgreSQL installation:

max_connections=200
max_prepared_transactions = 200
jit=off

The setting jit=off disables JIT for all PostgreSQL databases. If this is not desired, you can make this setting for the ConSol CM and the CMRF databases individually by executing:

> alter database cmdatabase set jit=off;

Creation of the databases and users

1. Create the database and database user:

   CREATE DATABASE cmdatabase;
   CREATE USER cmuser WITH PASSWORD 'password';
   GRANT ALL PRIVILEGES ON DATABASE cmdatabase TO cmuser;

2. Connect to the database and grant access to the public schema to the database user:

   GRANT ALL ON SCHEMA PUBLIC TO cmuser;
   ```

Alternatively, you can create use an own schema for the ConSol CM database:

CREATE SCHEMA cmschema;
GRANT ALL ON SCHEMA cmschema TO cmuser;
ALTER DATABASE cmdatabase SET search_path TO cmschema;

MySQL

Storage engine

MySQL must be installed with InnoDB as default storage engine. This is configured in the MySQL configuration file. The default file is:

• Linux: my.cnf
• Windows: my.ini

Encoding and collation

The recommended database character set is utf-8. The collation depends on the MySQL version.

info

MySQL 5 only supports up to 3-byte Unicode characters. Use MySQL 8 if you need 4-byte characters.

MySQL 5

The following settings are necessary in the mysqld section of the configuration file:

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

MySQL 8

The following settings are necessary in the mysqld section of the configuration file:

character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

Timezone settings on Windows

When executing MySQL on Windows, you need to manually set the time zone (see https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html#time-zone-installation).

1. Go to https://dev.mysql.com/downloads/timezones.html and download the latest timezone posix file, e.g. timezone_2021f_posix_sql.zip, for your MySQL version.

2. Unpack the file and open a terminal at the location where the timezone_posix.sql file is located.

3. Run the following command:

   mysql -u root -p mysql < timezone_posix.sql
   info

   mysql has to be added to the path as an environmental variable.

4. Enter the administrator password and press the enter key.

5. Restart the MySQL server (e.g. in the task manager under Services -> MySQL -> restart).

6. Open the directory of the MySQL server and add the timezone setting to the my.ini file, [mysqld] section.

   default_time_zone='Europe/Berlin'
   info

   The timezone must match the timezone of the local machine.

7. Restart the MySQL server again.

Creation of the databases and users

Execute the following commands to create a new database cmdatabase and a new user cmuser. Grant the user all necessary rights on the database.

info

The default password policy requires at least 8 characters and a mix of letters and numbers. Nevertheless, this is not checked when executing the commands in the command line.

MySQL 5

create database cmdatabase CHARACTER SET utf8;
grant all on cmdatabase.* to cmuser@'%' identified by 'password';
grant all on cmdatabase.* to cmuser@localhost identified by 'password';
flush privileges;

MySQL 8

CREATE USER 'cmuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'cmuser'@'%' IDENTIFIED BY 'password';
create database cmdatabase CHARACTER SET utf8mb4;
grant all on cmdatabase.* to 'cmuser'@'%';
grant all on cmdatabase.* to 'cmuser'@localhost;
flush privileges;

Recommended settings

To avoid problems with scene import, it is recommended to set xa_detach_on_prepare=off globally for the database if you use MySQL 8 in version 8.0.29 or higher.

Add the following setting to the [mysqld] section of the my.cnf / my.ini file of your MySQL installation.

[mysqld]
xa_detach_on_prepare=OFF

Optional settings

If you are planning to have attachments bigger than 1 MB, the MySQL configuration file must also be adapted:

• Linux: /etc/mysql/my.cnf
• Windows: <DB_HOME>/my.ini

In the server section [mysqld], the allowed maximum size of attachments must be entered.

The following value allows 16 MB attachments:

max_allowed_packet=16777216

To allow larger attachments, e.g. 25M MB, you might also need to increase the size of the MySQL log files:

innodb_log_file_size=256M

Setting of the first case number

After the ConSol CM-specific database objects have been created during the first successful start of the ConSol CM application (see Setting up ConSol CM), you can set the starting case number. By default, the case numbers are assigned starting with 1, but you can determine that they should start with another number.

For example, execute the following statement to start the case numbers with 10000:

ALTER TABLE cmas_ticket AUTO_INCREMENT = 10000;

Microsoft SQL

Creation of the databases and users

1. Create a new database cmdatabase. Execute the following statement on this database:

   ALTER DATABASE cmdatabase SET READ_COMMITTED_SNAPSHOT ON;

2. Set the collation.

    ALTER DATABASE cmdatabase COLLATE SQL_Latin1_General_CP1_CI_AS;

3. Create an application user cmuser with the following authorizations:

   • Grant user cmuser access to database medium master (role public).
   • Grant user cmuser access to database medium cmdatabase (role public and role db_owner).

Setting of the first case number

After the ConSol CM-specific database objects have been created during the first successful start of the ConSol CM application (see Setting up ConSol CM), you can set the starting case number. By default, the case numbers are assigned starting with 1, but you can determine that they should start with another number.

For example, execute the following statement to start the case numbers with 10000:

dbcc checkident ('cmas_ticket', reseed, 10000)

Performance tip for Windows operating system

When running Microsoft SQL Server on Windows, the power options have an impact on the database performance. Change the power plan from Balanced to High Performance to improve database performance.

Oracle

Encoding and collation

An Oracle instance with database character set AL32UTF8 is required. This can be checked with the following SQL statement:

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

The result should be:

• NLS_CHARACTERSET = AL32UTF8

Initialization parameter settings

The following Oracle instance parameters must be changed to the following values or higher:

• processes = 20 + 200 * (number of ConSol CM connection pools)

  info

  If shared with other servers, that + 200 for each ConSol CM connection pool

• open_cursors = 1000

  hinweis

  Necessary for statement caching in application server

The parameters can be changed as sysdba via sqlplus on the default instance with the following commands:

# sqlplus / as sysdba
> ALTER SYSTEM SET processes=420 SCOPE=SPFILE;
> ALTER SYSTEM SET open_cursors=1000 SCOPE=SPFILE;

Note that changes are only effective after restarting the database instance. This can be performed by:

> SHUTDOWN;
> STARTUP;

Creation of the databases and users

Create a separate database user account cmuser with the roles CONNECT and RESOURCE.

The password of this application user must not expire, as it is used in the database connection pool of the application server.

This can be created by the sqlplus command line interpreter via the following commands:

# sqlplus /nolog (start sqlplus client)
SQL> connect <SYSTEM>/<PASSWORD> --use your DBA user and password, connection to the default DB
-- (or SQL>connect sys/<PASSWORD> as sysdba)
SQL> create user 'cmuser' identified by 'password';
SQL> grant connect, resource to 'cmuser';
SQL> grant create procedure to 'cmuser';
SQL> grant create view to 'cmuser';

warnung

For Oracle databases the username is case-insensitive during CREATE USER and GRANT. But it is stored in upper case in the database metadata. Therefore, you must use the upper case name in SELECT statements on metadata as dba_users and dba_role_privs.

info

If you want to create views or cubes for reporting, e.g. on the Cubes page of the Web Admin Suite, the CMRF user needs permissions to create views on the CMRF database.

Tablespace for application-specific tables and indexes

The database user cmuser needs a tablespace where he can store his tables and indexes. In case a dedicated tablespace should be used for the ConSol CM* application, the following SQL is the easiest way to create a tablespace cmtablespace:

SQL> create bigfile tablespace 'cmtablespace';

warnung

This only works if OMF is enabled in the Oracle database. This should be the case for most databases.

If the default tablespace of the database (default: USERS) should not be used, the default tablespace of the user must be changed. This can be changed by the following command:

SQL> alter user 'cmuser' default tablespace 'cmtablespace';

For Oracle 12c, it is also required that the database user has a quota on this tablespace:

SQL> alter user 'cmuser' QUOTA UNLIMITED ON 'cmtablespace';

info

It is not possible to use more than one tablespace for one ConSol CM installation.

Setting of the first case number

After the ConSol CM-specific database objects have been created during the first successful start of the ConSol CM application (see Setting up ConSol CM), you can set the starting case number. By default, the case numbers are assigned starting with 1, but you can determine that they should start with another number.

For example, execute the following statement as application Oracle user to start the ticket numbers with 10000:

drop sequence TICKET_SEQUENCE;
CREATE SEQUENCE TICKET_SEQUENCE" MINVALUE 10000 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 100000 CACHE 20 NOORDER NOCYCLE ;