Database Configuration Overview

Regardless of the installation type (LSB or tar), a database must be prepared to hold the Qorus system schema before the Qorus server can be started.

Qorus Integration Engine requires two database schemas to operate as in the following table.

Qorus Integration Engine System Schemas (Datasources)

Schema	Type	Description
`omq`	Required	System schema, system and user metadata, workflow data will be installed here
`omquser`	Recommended	User-modifiable schema containing custom user objects; for initial installs, this schema will be empty

The system and user schemas can be created in different databases; they can even be of different database types.

Each schema will be accessed through a datasource (see system datasource (dbparams) File), which represents a connection to a schema on a particular database server, using a particular username and password, as well as other optional information.

The omq datasource must point to an Oracle, MySQL/MariaDB/Percona, or PostgreSQL database, but the omquser Datasource has no restrictions on the database driver.

Note that the database server does not necessarily have to be located on the same system as the Qorus Integration Engine server, however, for performance reasons, a fast network connection is required between the two systems at the minimum.

Note: Note that it is highly recommended that any Qorus Integration Engine database instance destined for production use should be in a clustered configuration to ensure the highest possible reliability. Additionally, regular backups/dumps of the database should be made in case of a catastrophic database error. All Qorus data, metadata, configuration and states, order data, etc, is stored in the system schema. If the system schema becomes corrupted, Qorus will not function.

Note that no changes can be made by the user to the Qorus system schema or Qorus may no longer function. The optional user schema may be used in any way desired.

The following sections will describe how to set up the system and user schemas for Oracle, MySQL/MariaDB/Percona, and PostgreSQL databases.

Oracle Configuration

This step describes how to set up Oracle database schemas and tablespaces for use with Qorus Integration Engine on an Oracle 9i database server or later. The creation of an Oracle instance is outside the scope of this documentation. Use the dbca tool or see your Oracle documentation for more information. However Qore Technologies highly recommends that any Qorus Integration Engine database instance destined for production use should be in a clustered configuration to ensure the highest possible reliability.

Note: Note that even with clustered configurations, load-sharing must be disabled in the tnsnames.ora file used by the Qorus server; the Qorus server must make all connections to the same physical server as the cluster synchronization delay will cause problems with synchronized transaction management within the Qorus server.

The Qorus system datasources to be defined in the next step that will point to the schemas described here are omq (for the Qorus system schema) and optinally omquser (for the optional user schema). The omq system schema is necessary for the operation of the system.

Oracle Privileges

The priviledges described here apply to the system schema user (omq). Example scripts for creating tablespaces and schema users follow in Example Tablespace and User Creation Scripts.

Name	Mandatory	Qorus Module	Description
`CREATE` `SESSION`	Yes	Core	Part of the `CONNECT` role in Oracle 10 or later
`CREATE` `PROCEDURE`	Yes	Core	Part of the `RESOURCE` role in Oracle 10 or later
`CREATE` `SEQUENCE`	Yes	Core	Part of the `RESOURCE` role in Oracle 10 or later
`CREATE` `TABLE`	Yes	Core	Part of the `RESOURCE` role in Oracle 10 or later
`CREATE` `TRIGGER`	Yes	Core	Part of the `RESOURCE` role in Oracle 10 or later
`CREATE` `TYPE`	Yes	Core	Part of the `RESOURCE` role in Oracle 10 or later
`CREATE` `VIEW`	Yes	Core	Part of the `RESOURCE` role in Oracle 10 or later
`CREATE` `MATERIALIZED` VIEW	Yes	Workflow and Job Instance Snapshots	Required for installation too
`EXECUTE` `DBMS_STATS`	No	schema-tool: Schema Manipulation Helper Tool, Deleting Old Data From the Database	This package is used for gathering statistics, but it can also be performed manually by a DBA
`EXECUTE` `DBMS_OUTPUT`	No	Debug	Used in the debugging code of info-service
`EXECUTE` `DBMS_APPLICATION_INFO`	No	Deleting Old Data From the Database	A helper information logging in the Deleting Old Data From the Database
`SELECT` `V$BUFFER_POOL_STATISTICS`	No	schema-tool: Schema Manipulation Helper Tool	schema-tool: Schema Manipulation Helper Tool uses it for index tree analysis, but it can also be handled by manually by a DBA

Note

Oracle suggests using roles rather than raw GRANTs in its documentation; please consult your DBA when setting up new Oracle schemas
The names and passwords of the schemas can be set per installation; any valid Oracle name can be used. In this document, the names omq and omquser will be used to refer to these schemas. In the next step, the datasources omq and omquser will be set up to point to these schemas as well.

Oracle Tablespaces

Each schema requires two tablespaces, one for data and one for indexes. Tablespace names are flexible and do not necessarily have to be as described in the table below; tablespace names can be overridden with the –-data-ts and –index-ts options to the oload and schema-tool programs and by default in the options file with the qorus-client.omq-data-tablespace and qorus-client.omq-index-tablespace options. However, for the rest of this document, the names will appear as given below.

Schema	Tablespace	Type	Description
`omq`	`omq_data`	Required	All system data is stored in the tablespace; this should be the default tablespace for the `omq` user/schema, and the user must have permissions to write to this tablespace (ie `UNLIMITED TABLESPACE` or a quota on this tablespace)
`omq`	`omq_index`	Required	All system index are stored here.
`omquser`	`omquser_data`	Optional	All data in the user schema is stored in this tablespace; this should be the default tablespace for the `omquser` user/schema, and the user must have permissions to write to this tablespace (ie `UNLIMITED TABLESPACE` or a quota on this tablespace)
`omquser`	`omquser_index`	Optional	All user indexes are stored here.

Note: If you use alternate tablespace names, you must define the tablespace names in the options file with the qorus-client.omq-data-tablespace and qorus-client.omq-index-tablespace options so that automatic schema management will work properly

Oracle Tablespace Sizing

Qorus tablespace sizing is highly dependent on the amount of workflow order instance data stored, the type of data associated with the workflows, and the archiving frequency.

The following is a rough formula for determining the tablespace sizing for an initial install, assuming a conservatively large workflow order instance data size and data to index ratio:

omq_data tablespace size = max. workflow instances * 500 KB
omq_index tablespace size = omq_data tablespace size / 5

The minumum recommended tablespace sizes for a test or evaluation instance are:

omq_data: 256 MB
omq_index: 128 MB

The optional omquser schema will be solely populated with user objects therefore the storage characteristics are installation-dependent and therefore no sizing information can be given here. The Qorus development team and Oracle DBAs should confer and agree on sizing during the design and development process of workflows and services.

To create the two required Oracle schemas and the tablespaces, SQL similar to that found in the $OMQ_DIR/templates/omq-db.sql and $OMQ_DIR/templates/omquser-db.sql can be used. Note that the directory paths and file names should match your installation, and of course the tablespace sizes should match your planning. These files are only given as examples and should not be used without modification for your environment and ideally should be verified by a DBA prior to execution.

Oracle cluster configuration is outside the scope of this document (see your Oracle documentation instead), but a clustered database is recommended for production instances.

Once the schemas and tablespaces have been created, verify the connectivity using $ORACLE_HOME/bin/sqlplus.

Note: As mentioned before, ensure that load balancing is not enabled in the tnsnames.ora file for the Qorus system schema. The Qorus server must make all connections to the same physical database server as the Oracle cluster synchronization delay will cause problems with synchronized transaction management within the Qorus server.

Example Tablespace and User Creation Scripts

These files are provided as examples only, paths and passwords at least should be modified to correspond to your Oracle installation. This example file can be found in $OMQ_DIR/templates/oracle-omq-db.sql

create tablespace omq_data
        datafile '/opt/oradata/XBOX_MASTER/datafile/omq_data01.dbf' size 512M reuse 
        autoextend on next 256M
        default storage (initial 1M next 1M minextents 1 maxextents 1000
        pctincrease 0);
        
create tablespace omq_index
        datafile '/opt/oradata/XBOX_MASTER/datafile/omq_index01.dbf' size 256M reuse 
        autoextend on next 128M
        default storage (initial 1M next 1M minextents 1 maxextents 1000
        pctincrease 0);

create user omq identified by omq default tablespace omq_data temporary tablespace temp;

grant connect, resource, create any snapshot, unlimited tablespace to omq;

This example file can be found in $OMQ_DIR/templates/oracle-omquser-db.sql

create tablespace omquser_data
        datafile '/opt/oradata/XBOX_MASTER/datafile/omquser_data01.dbf' size 128M reuse
        autoextend on next 64M
        default storage (initial 1M next 1M minextents 1 maxextents 1000
        pctincrease 0);
        
create tablespace omquser_index
        datafile '/opt/oradata/XBOX_MASTER/datafile/omquser_index01.dbf' size 64M reuse
        autoextend on next 32M
        default storage (initial 500k next 500k minextents 1 maxextents 1000
        pctincrease 0);

create user omquser identified by omquser default tablespace omquser_data temporary tablespace temp;

grant connect, resource, create any snapshot, unlimited tablespace to omquser;

Note: Do not forget to change the passwords to match your organization's security requirements

MySQL/MariaDB/Percona Configuration

Because MariaDB and Percona are a drop-in replacements for MySQL, references to MariaDB below are also valid for MySQL (which is an Oracle property; Qore Technologies is not affiliated with Oracle) and Percona. Any differences between MySQL, MariaDB, and Percona will be highlighted below.

This step describes how to set up MariaDB database schemas for use with Qorus Integration Engine on MariaDB server 5.1 or later. MariaDB installation is outside the scope of this documentation. See your MariaDB documentation for detailed instructions on how to install the MariaDB database server. Once MariaDB has been installed on the target system, Qorus databases and users can be created.

It is recommended to use UTF-8 character encoding in all MariaDB databases where international characters may be used with Qorus.

Note: All MariaDB tables used in the Qorus Integration Engine system schema use the XtraDB (InnoDB for MySQL) engine for transaction management and row-level locking.

Qorus imposes no restrictions on the database or user names used for either the system or user schemas when using MariaDB as a database server.

Example Database and User Creation Scripts

These files are provided as examples only, at least the passwords should be changed for security reasons. This example file can be found in $OMQ_DIR/templates/mysql-omq-db.sql

create user 'omq'@'%' identified by 'omq';
grant super on *.* to 'omq'@'%';
create user 'omq'@'localhost' identified by 'omq';
grant super on *.* to 'omq'@'localhost';

create database omq character set 'utf8';
grant all on omq.* to 'omq'@'%';
grant all on omq.* to 'omq'@'localhost';

This example file can be found in $OMQ_DIR/templates/mysql-omquser-db.sql

create user 'omquser'@'%' identified by 'omquser';
grant super on *.* to 'omquser'@'%';
create user 'omquser'@'localhost' identified by 'omquser';
grant super on *.* to 'omquser'@'localhost';

create database omquser character set 'utf8';
grant all on omquser.* to 'omquser'@'%';
grant all on omquser.* to 'omquser'@'localhost';

Note: Do not forget to change the passwords to match your organization's security requirements

These files can be installed with the mysql command as follows (use the appropriate user and password, the following are only examples):

unix-prompt$ mysql –uroot < $OMQ_DIR/templates/mysql-omq-db.sql
unix-prompt$ mysql –uroot < $OMQ_DIR/templates/mysql-omquser-db.sql

PostgreSQL Configuration

This step describes how to set up PostgreSQL database schemas for use with Qorus Integration Engine on PostgreSQL server 9.0.0 or later.

PostgreSQL installation and connectivity configuration (postgresql.conf, pg_hba.conf, etc configuration) is outside the scope of this documentation (however some basic information and examples are provided in the following section). See your PostgreSQL documentation for detailed instructions on how to install the PostgreSQL database server.

If the Qorus server and PostgreSQL servers are not located on the same machine, PostgreSQL must be configured to accept connections on a network interface so Qorus can communicate with the server.

Once PostgreSQL has been installed and configured on the target system, Qorus databases, tablespaces, and users can be created.

It is recommended to use UTF-8 character encoding in all PostgreSQL databases where international characters may be used with Qorus.

Each schema requires two tablespaces, one for data and one for indexes. Tablespace names are flexible and do not necessarily have to be as described in the table below; tablespace names can be overridden with the –-data-ts and –index-ts options to the oload and schema-tool programs and by default in the options file with the qorus-client.omq-data-tablespace and qorus-client.omq-index-tablespace options. However, for the rest of this document, the names will appear as given below.

Note: If you use alternate tablespace names, you must define the tablespace names in the options file with the qorus-client.omq-data-tablespace and qorus-client.omq-index-tablespace options so that automatic schema management will work properly

Required PostgreSQL Tablespaces

Schema	Tablespace	Type	Description
`omq`	`omq_data`	Mandatory	All system data is stored in the tablespace
`omq`	`omq_index`	Mandatory	All system index are stored here
`omquser`	`omquser_data`	Optional	All data in the user schema is stored in this tablespace
`omquser`	`omquser_index`	Optional	All user indexes are stored here

Example Database, Tablespace and User Creation Scripts

These files are provided as examples only; paths and passwords at least should be changed for security reasons. This example file can be found in $OMQ_DIR/templates/pgsql-omq-db.sql

create database omq encoding = 'utf8';
\connect omq;
create language plpgsql;
create tablespace omq_data location '/opt/postgresql84/db/omq_data';
create tablespace omq_index location '/opt/postgresql84/db/omq_index';
create user omq superuser password 'omq';

This example file can be found in $OMQ_DIR/templates/pgsql-omquser-db.sql

create database omquser encoding = 'utf8';
\connect omquser;
create language plpgsql;
create tablespace omquser_data location '/opt/postgresql84/db/omquser_data';
create tablespace omquser_index location '/opt/postgresql84/db/omquser_index';
create user omquser superuser password 'omquser';

Note: Do not forget to change the passwords to match your organization's security requirements

These files can be installed with the psql command as follows (use the appropriate user and password for your installation, the following are only examples):

Note: the create language plpgsql; lines are not necessary in newer versions of PostgreSQL since this language is included by default

unix-prompt$ psql –Upostgres < $OMQ_DIR/templates/pgsql-omq-db.sql
unix-prompt$ psql –Upostgres < $OMQ_DIR/templates/pgsql-omquser-db.sql

Basic PostgreSQL Connectivity Configuration

Example pg_hba.conf File

The following file allows connections to all databases and all users without a password for local users, and requires a username and password from all network users. Do not use this file if local security is a concern:

# CAUTION: Configuring the system for local "trust" authentication allows
# any local user to connect as any PostgreSQL user, including the database
# superuser. If you do not trust all your local users, use another
# authentication method.

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust
# require a password from network users
host    all         all         0.0.0.0/0             md5
host	all         all         ::/0                  md5

If you need to connect to your PostgreSQL databases from other machines on the network, set the following in your postgresql.conf file to configure the server to listen on all network interfaces:

listen_addresses = '*'

For more information on these files, or on PostgreSQL security or configuration, see your PostgreSQL documentation.

Table of Contents