Overview

Universal Controller can use a database space of an existing database or you can install a database specifically for the Controller.

We recommend an initial size of 100MB.

Note

In a High Availability environment, each cluster node connects to the same database.

Database Permissions

In order to install Universal Controller, the database user configured for the Controller will require DDL (Data Definition Language) permission in the database during the install.

Once the install has been completed successfully, the configured database user requires only DML (Data Manipulation Language) permissions for running the Controller.

Database Management Systems

The following database management systems are supported:

MySQL
Microsoft SQL Server
Oracle
PostgreSQL

Databases that are compatible with and use the same JDBC drivers as the supported databases are also supported, for example AWS RDS (MySQL), MariaDB (MySQL), or Azure SQL Database (SQL Server). We recommend that customers periodically review the database documentation for Incompatibilities and Feature Differences as these may change over time.

MySQL

Info

MySQL 8.0.x is supported.

Step 1	Download MySQL installation instructions.
Step 2	Download MySQL (Windows only). For Windows, select Windows (x86, 32-bit), MSI Installer For Unix and Linux, you can use a tar.gz download or select a systems package installer appropriate for your environment, such as Yum.
Step 3	Install MySQL as per the instructions.
Step 4	Make a note of the user ID and password to be used later when installing the Controller.
Step 5	The database will be created automatically when you select MySQL during the Controller installation process.

MySQL Options

The following enhancements can be made to your MySQL database.

Speeding Up MySQL Performance

For Windows installations, you can speed up MySQL performance by adding the following parameter to the appropriate MySQL.ini file:

innodb_flush_log_at_trx_commit=0

For more information about this parameter, see the MySQL documentation:

http://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

Setting the MySQL max_allowed_packet Configuration Variable

A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.

If you want the Controller to handle big packets, you must increase the MySQL max_allowed_packet configuration variable on the database server.

For detailed information about this variable, refer to:

MySQL 8.0.x reference manual

MySQL SSL/TLS Configuration

If you use SSL/TLS for JDBC communication to your MySQL environment, some additional configuration is required (depending on your needs).

The MySQL configuration property sslMode can be used to control the SSL behavior for database connections.

By default, network connections are SSL encrypted; the sslMode property permits secure connections to be turned off or different levels of security to be selected.

The following sslMode values are allowed:

sslMode Value	Description
"DISABLED"	Establish unencrypted connections.
"PREFERRED"	Establish encrypted connections if the server enabled them, otherwise fall back to unencrypted connections. (Default value)
"REQUIRED"	Establish secure connections if the server enabled them, fail otherwise.
"VERIFY_CA"	Similar to REQUIRED; but additionally, verify the server TLS certificate against the configured Certificate Authority (CA) certificates.
"VERIFY_IDENTITY"	Similar to VERIFY_CA; but additionally, verify that the server certificate matches the host to which the connection is attempted.

To change the default behavior of SSL (PREFERRED), add the following to the uc.properties configuration file where sslModeValue is one of the values listed above (DISABLED, PREFERRED, REQUIRED, VERIFY_CA, VERIFY_IDENTITY):

uc.db.url.append.properties=&sslMode=sslModeValue

This property replaced the deprecated legacy properties "useSSL", "requireSSL", and "verifyServerCertificate", which are still accepted but translated into a value for "sslMode".

If "sslMode" is not explicitly set:

{"useSSL=false"} is translated to "sslMode=DISABLED".
{"useSSL=true", "requireSSL=false", "verifyServerCertificate=false"} is translated to "sslMode=PREFERRED".
{"useSSL=true", "requireSSL=true", "verifyServerCertificate=false"} is translated to "sslMode=REQUIRED".
{"useSSL=true" AND "verifyServerCertificate=true"} is translated to "sslMode=VERIFY_CA".
There is no equivalent legacy settings for "sslMode=VERIFY_IDENTITY".

Note

For ALL server versions, the default setting of sslMode is "PREFERRED", and it is equivalent to the legacy settings of useSSL=true, requireSSL=false, and verifyServerCertificate=false, which are different from their default settings for Connector/J 8.0.12 and earlier in some situations.

Applications that continue to use the legacy properties and rely on their old default settings should be reviewed.

You may need to enable connections with TLSv1.2 and higher versions using the enabledTLSProtocols connection property. To specify the enabledTLSProtocols property, add the following to the uc.properties configuration file:

uc.db.url.append.properties=&enabledTLSProtocols=TLSv1.2

Prior to considering the enabledTLSProtocols connection property , you should verify the database connection using the latest Universal Controller maintenance release, as it may be using a more recent MySQL Connector/J, with functionality changed or added.

https://stonebranchdocs.atlassian.net/wiki/display/SMLRI/Universal+Controller+Maintenance+Lists

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-known-issues-limitations.html

Microsoft SQL Server

Microsoft SQL Server versions 2016, 2017, 2019 and 2022 are supported.

Step 1	Download and install MS SQL Server as per the Microsoft documentation.
Step 2	Create the Controller database. You can use any legal name, but we recommend the name uc. Important You must use a *case-insensitive* collation.
Step 3	Make a note of the userid and password to be used later when installing the Controller.

Note

Universal Controller automatically appends the sendStringParametersAsUnicode parameter to the URL, setting it to false.

When set to false, the Unicode translation property specifies that prepared parameters for character data are sent as ASCII or Multi-byte Character Set (MBCS) instead of Unicode.

jdbc:sqlserver://localhost:1433;databaseName=uc;sendStringParametersAsUnicode=false

Oracle

Oracle versions 19c and 21c are supported.

Step 1	Download and install Oracle as per the Oracle documentation.
Step 2	Create the Controller database. You can use any legal name, but we recommend the name uc.
Step 3	Make a note of the userid and password to be used later when installing the Controller.

If PDB (Pluggable Database) is being used for the Oracle Controller database, the JDBC URL should be used in EZCONNECT format and point to the PDB service, not the database SID.

For example:

jdbc:oracle:thin:@//dbhost:1521/pdbuc.userdomain

Oracle Options

The following enhancements can be made to your Oracle database.

Setting `open_cursors` Value for Large Imports

To facilitate large imports on Oracle, specify the maximum number of cursors that can be open by setting the open_cursors value to 1000.

(The cursors are used only during the import; they then are closed.)

Checking the Current Value of `open_cursors`

To check the current value for maximum open cursors, issue the following sql*plus utility command:

show parameter open_cursors

A listing similar to the following will display:

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     1000

Setting a New Value for `open_cursors`

You can temporarily set the open_cursors value with the following SQL:

alter system set open_cursors=1000

To make a permanent change, you must set the open_cursors value in the initialization parameters file.

Note

If you do not set open_cursors to 1000, you could receive the following error message during large imports:

ORA-01000: maximum open cursors exceeded

Character Sets

Universal Controller does not stipulate a requirement for the Oracle database character set; for multilingual support, you can use the default Unicode character set of AL32UTF8.

https://docs.oracle.com/database/121/NLSPG/ch6unicode.htm#NLSPG317

Block Size

A block size of 8K is optimal for most systems, including Universal Controller.

https://docs.oracle.com/cd/B19306_01/server.102/b14211/iodesign.htm#i19636

PostgreSQL

PostgreSQL version 17 is supported.

Step 1	Download and install PostgreSQL as per the PostgreSQL documentation.
Step 2	Create the Controller database. You can use any legal name, but we recommend the name uc.
Step 3	Make a note of the userid and password to be used later when installing the Controller.

Browser not supported