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:

1. 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 \<DBNAME>\ 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.

   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.KONY_METRICS_LOGGER_JNDI=java:/jdbc/voltmxreports flyway.placeholders.KONY_METRICS_LOG_LEVEL=INFO flyway.placeholders.KONY_METRICS_LOG_OPTION=logfile flyway.placeholders.KONY_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

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