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.
 

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

MySQL

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/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
• 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 5.7.x reference manual
• 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:

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):

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".

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:

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

Oracle

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.
 

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