Create and Configure a Database for Integration Services - Reports

Create and Configure a Database for Reports

To create a database for Reports, follow these steps:

Create a database for reports with a custom name along with prefix and suffix. Prefix and suffix are optional. For example, database name is <prefix>reportsdb<suffix>.

The following is sample query for creating a database in MSSQL:

CREATE DATABASE reportsdb;

The following is a sample query for creating a database in MySQL:

``CREATE DATABASE \` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;


    > **_Note:_**  For Oracle databases a schema name should be in capital letters.  
      Find the word `reportsdb` in SQL files located in the admin scripts and replace it with `REPORTSDB`.

    > **_Note:_** For Oracle, create necessary tablespaces. Refer to [Prerequisites for Volt MX Foundry with Oracle](Database_Prerequsites.md#prerequisites-for-volt-mx-foundry-with-oracle).

    The following details are required for Flyway configuration:


    *   Schema name for reports: `reportsdb`
    *   Placeholders for Reports:
 ```

    # For Reports (reportsdb), replace the following placeholders in SQL migrations for your database
    flyway.placeholders.VOLTMX_METRICS_LOGGER_JNDI=java:/jdbc/voltmxreports flyway.placeholders.VOLTMX_METRICS_LOG_LEVEL=INFO
    flyway.placeholders.VOLTMX_METRICS_LOG_OPTION=logfile
    flyway.placeholders.VOLTMX_METRICS_LOG_LOCATION=<log_location_for_metrics>

Tablespace Placeholders for Oracle:

 | Product Name | Tablespace Placeholders for Oracle |
 | --- | --- |
 | Reports / Metrics | METRICS\_DATA\_TABLESPACE, METRICS\_INDEX\_TABLESPACE, METRICS\_LOB\_TABLESPACE |

SQL files paths for Reports in VoltMXFoundry_Plugins folder:

| Path for SQL files in the VoltMXFoundry\_Plugins folder | Database | Component |
| --- | --- | --- |
| \\VoltMXFoundry\_Plugins\\middleware\\reportsdb\_mysql | MySQL | ReportsDB   |
| \\VoltMXFoundry\_Plugins\\middleware\\reportsdb\_oracle | Oracle |
| \\VoltMXFoundry\_Plugins\\middleware\\reportsdb\_mssql | SQL Server |

Execute all SQL scripts by using the steps provided at Configuring Flyway Command-line Tool.

Since the structure of flyway has changed from Flyway 3.2.1 to Flyway 4.0.3 in Volt MX Foundry installer, execute the following statements to make the schema_version table compatible with Flyway 4.0.3.

Oracle:
drop index "schema_version_ir_idx";
drop index "schema_version_vr_idx";
ALTER TABLE "schema_version" DROP constraint "schema_version_pk" drop index;
ALTER TABLE "schema_version" DROP COLUMN "version_rank";
ALTER TABLE "schema_version" modify("version" null);
ALTER TABLE "schema_version" add constraint "schema_version_pk" primary key("installed_rank");

MySQL:
ALTER TABLE schema_version DROP INDEX schema_version_vr_idx;
ALTER TABLE schema_version DROP INDEX schema_version_ir_idx;
ALTER TABLE schema_version DROP PRIMARY KEY;
ALTER TABLE schema_version DROP COLUMN version_rank;
ALTER TABLE schema_version CHANGE version version VARCHAR(50);
ALTER TABLE schema_version ADD PRIMARY KEY (installed_rank);

SQL Server:
DROP INDEX schema_version_ir_idx ON dbo.schema_version
GO
DROP INDEX schema_version_vr_idx ON dbo.schema_version
GO
ALTER TABLE dbo.schema_version DROP CONSTRAINT schema_version_pk
GO
ALTER TABLE dbo.schema_version DROP COLUMN version_rank
GO
ALTER TABLE dbo.schema_version ADD CONSTRAINT schema_version_pk PRIMARY KEY CLUSTERED (installed_rank)
GO
ALTER TABLE dbo.schema_version ALTER COLUMN version nvarchar(50) NULL
GO