This article provides instructions for creating a database user and databases, and then granting privileges. We've included instructions for MySQL and Postgres as well as instructions for Oracle. See below for details.

MYSQL and Postgres Databases

📘

Adding the Supported JDBC Driver

As of OverOps v4.56, the JDBC driver for MySQL and Oracle will no longer be available by default.

If the database to be configured for OverOps will be MySQL or Oracle, then the supported JDBC driver will need to be added. Please use the link below for instructions on installing the corresponding driver:
Using MySQL Database
Using Oracle Database

🚧

Postgres Support

Postgres is supported from version 9.6 and above.

1. Log in as a database super-user. This user will need to have full rights for GRANTs, i.e.,. root permissions.
2. Create the OverOps user:

CREATE USER 'overops'@'%' IDENTIFIED BY 'ChangeThisPassW0rd!';

3. Grant the OverOps user access to the databases:

GRANT ALL PRIVILEGES ON `dynalite`.* TO 'overops'@'%';
GRANT ALL PRIVILEGES ON `pds`.* TO 'overops'@'%';
GRANT ALL PRIVILEGES ON `qsql`.* TO 'overops'@'%';
GRANT ALL PRIVILEGES ON `takipi`.* TO 'overops'@'%';

4. Finally, you may want to force a privileges reset for MySQL (optional) using FLUSH PRIVILEGES:

FLUSH PRIVILEGES;

Oracle Database

1. Log in as a database super-user. This user will need to have full rights for GRANTs, i.e.,. root permissions.
2. Create a dedicated tablespace:

* create tablespace <tablespace name> datafile <tablespace name.dat> size 10M autoextend on;
* create temporary tablespace <temp tablespace name> tempfile <tablespace name.dat> size 5M autoextend on;

3. Create a dedicated user and grant permissions:

* create user <db username> identified by <db user password> default tablespace <tablespace name> temporary tablespace <temp tablespace name>;

4. Grant the user access:

* grant create session to <db username>;
* grant create table to <db username>;
* grant unlimited tablespace to <db username>;
* grant create procedure to <db username>;
* grant create any sequence to <db username>;
* grant create any trigger to <db username>;
* grant create any type to <db username>;

Configuring the Oracle Database

You can use the my.server.properties file to configure the the maximum number of entries that will be fetched from the Oracle database with each request.

Use the property ORACLE_DB_CONNECTION_FETCH_SIZE to configure the max number of entries; the default value is 5000.

❗️

Warning

The above flag can cause problems such as as out of memory issues; please use it with caution.

Configuring the Oracle Auto-Maintenance Job

Starting from version 4.54, the OverOps backend comes pre-packed with a maintenance job to help keep the integrity of our database, our indexes intact and their performance optimized.

The following optional parameters can be added to my.server.properties file to control the behaviour of this job:

• To disable the job, add the following property: DB_MAINTENANCE_ENABLED=false
• To control the Oracle gather schema options, you can select one of the following options: GATHER, GATHER_AUTO, GATHER_STALE, GATHER_EMPTY. And add the following property: DB_GATHER_STATS_OPTION=GATHER_AUTO (GATHER_AUTO is the default option if not specified otherwise)

To configure the schedule of this job with your appropriate maintenance window - edit the quartz_data.xml file (its default location is: /opt/takipi-server/conf/tomcat/shared/quartz_data.xml) and look for the section for dbMaintenanceJob, inside this section edit the the cron-expression value to control the scheduling (you can use a cron expression generator to help build the expression):

Before and after any change to the configuration it is advisable to stop and restart the Backend server/s services (takipi-server.sh).