Create and Configure a Database for Integration Services - Admin

Create and Configure a Database for VoltMX Admin

To create a database for Admin, follow these steps:

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

Note: For Oracle databases, a schema name should be in capital letters.
Find the word adminDB in SQL files located in the admin scripts and replace it with ADMINDB.

Note: For Oracle, create necessary tablespaces and Users before proceeding. Refer to Prerequisites for Volt MX Foundry with Oracle.

Volt MX

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

CREATE DATABASE admindb;

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

  CREATE DATABASE < DBNAME > DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

For Foundry version V9.2.2.0 or greater:

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

  CREATE DATABASE < DBNAME > DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

The following details are required for Flyway configuration:
- Schema name for Admin: admindb
- Placeholders for Admin:

For Admin (admindb):

replace the following placeholders in SQL migrations for your database flyway.placeholders.VOLTMX_SERVER_STORAGE_DATABASE_TYPE -> value must be

    mysql/oracle/sqlserver based on the chosen databases.

    flyway.placeholders.VOLTMX_SERVER_STORAGE_DATABASE_HOSTNAME -> database host name where
    admindb is created.

    flyway.placeholders.VOLTMX_SERVER_STORAGE_DATABASE_PORT -> database port where
    admindb is created.

    flyway.placeholders.VOLTMX_SERVER_STORAGE_DATABASE_USERNAME -> database user name
    flyway.placeholders.VOLTMX_SERVER_STORAGE_DATABASE_PASSWORD ->:  ([Encrypt](Encrypt_Passwords.md) this password)
          - For MySQL and SQL Server: database user password

    flyway.placeholders.VOLTMX_SERVER_STORAGE_DATABASE_INSTANCE ->  MySQL – empty,
    SQL Server – instance name if given, Oracle - SID name. flyway.placeholders.VOLTMX_SERVER_CACHEID_TRANSPORT=""flyway.placeholders.VOLTMX_SERVER_SESSION_DISTRIBUTED="FALSE"
    flyway.placeholders.VOLTMX_SERVER_CACHE_TYPE="EHCACHE"
    flyway.placeholders.VOLTMX_SERVER_CACHE_URL="" flyway.placeholders.VOLTMX_SERVER_JMS_INITIAL_CONTEXT_FACTORY=""
    flyway.placeholders.VOLTMX_SERVER_JMS_PROVIDER_URL=""
    flyway.placeholders.VOLTMX_SERVER_JMS_USER_NAME=""
    flyway.placeholders.VOLTMX_SERVER_JMS_USER_PASSWORD="" ([Encrypt](Encrypt_Passwords.md) this password) flyway.placeholders.VOLTMX_SERVER_KEYSTORE_LOCATION=""
    flyway.placeholders.VOLTMX_SERVER_LOG_LOCATION=""
    flyway.placeholders.VOLTMX_SERVER_MEMCACHED_COUNT=""
    flyway.placeholders.VOLTMX_SERVER_MEMCACHE_CLUSTER=""
    flyway.placeholders.VOLTMX_SERVER_RICH_CLIENT_DEPLOY=""
    flyway.placeholders.VOLTMX_SERVER_TRUSTSTORE_LOCATION=""
    flyway.placeholders.VOLTMX_SERVER_TRUSTSTORE_PASSWORD=""  ([Encrypt](Encrypt_Passwords.md) this password)
    flyway.placeholders.VOLTMX_SERVER_LOG_OPTION="logfile"
    flyway.placeholders.VOLTMX_SERVER_SSL_SOCKETFACTORY_PROVIDER=
    com.ibm.websphere.ssl.protocol.SSLSocketFactory

    flyway.placeholders.VOLTMX_SERVER_SSL_SERVERSOCKETFACTORY_PROVIDER=
    com.ibm.websphere.ssl.protocol.SSLServerSocketFactory
    flyway.placeholders.VOLTMX_SERVER_LOGGER_JNDI_NAME=jdbc/voltmxadmindb
    flyway.placeholders.VOLTMX_SERVER_SSL_SERVERSOCKETFACTORY_PROVIDER
    flyway.placeholders.VOLTMX_SERVER_SSL_SOCKETFACTORY_PROVIDER
    flyway.placeholders.VOLTMX_SERVER_LOG_OPTION

    flyway.placeholders.SERVER_TOPIC_CONNECTION_FACTORY=ConnectionFactory

No.	Property name	Place holder
1	richclient.deploy	${VOLTMX_SERVER_RICH_CLIENT_DEPLOY} Example value, `lib/apps` (Directory where the rich client binaries will be downloaded. Used by admin module)
2	memcache.cluster	${VOLTMX_SERVER_MEMCACHE_CLUSTER} Example value, `10.10.10.10:21201` ( where memcache cluster is running) *Note:* If the installation is being done without memcache, leave this value empty.
3	memcache.no.of.clients	${VOLTMX_SERVER_MEMCACHED_COUNT} Example value, `1` *Note:* If the installation is being done without memcache, leave this value empty.
4	cacheid.transport	${VOLTMX_SERVER_CACHEID_TRANSPORT} Example value, `Null` (Specify the transfer mode through below property. Valid values are PARAM_ONLY, COOKIE_ONLY, EITHER (Default) or null if memcache is not used)
5	ssl.trustStore	${VOLTMX_SERVER_TRUSTSTORE_LOCATION} Example value, `$JAVA_HOME/jre/lib/security/cacerts` (cacerts Location)
6	ssl.keyStore	${VOLTMX_SERVER_KEYSTORE_LOCATION} Example value, `$JAVA_HOME/jre/lib/security/cacerts` (cacerts Location)
7	ssl.trustStorePassword	${VOLTMX_SERVER_TRUSTSTORE_PASSWORD} Example value, `changeit`
8	ssl.keyStorePassword	${VOLTMX_SERVER_TRUSTSTORE_PASSWORD Example value, `changeit`
9	metrics.initialContextFactoryName	${VOLTMX_SERVER_JMS_INITIAL_CONTEXT_FACTORY} Example value, ii. for WebSphere: `com.ibm.websphere.naming.WsnInitialContextFactory` iii. for Tomcat: `org.apache.activemq.jndi.ActiveMQInitialContextFactory` iv. if jboss_jms is used: `org.jboss.naming.remote.client.InitialContextFactory` v. if activemq is used: `org.apache.activemq.jndi.ActiveMQInitialContextFactory`
10	metrics.providerURL	${VOLTMX_SERVER_JMS_PROVIDER_URL} Example value, For Tomcat: `tcp://$HOST_IP$:$USER_INPUT_JMS_PORT$?jms.useAsyncSend=TRUE` For JBoss: `http-remoting://<Hostname/Host IP>:<HTTP Port>`
11	SERVER_LOG_LOCATION	${VOLTMX_SERVER_LOG_LOCATION} Example value, `C:/voltmxmflogs/` (Log location for middleware log)
12	SERVER_LOGGER_JNDI_NAME	${VOLTMX_SERVER_LOGGER_JNDI_NAME} Example value,`java:comp/env/jdbc/voltmxadmindb`

Tablespace Placeholders for Oracle:

Product Name	Tablespace Placeholders for Oracle
Admin DB / Integration Services	VOLTMX_SERVER_DATA_TABLESPACE, VOLTMX_SERVER_INDEX_TABLESPACE, VOLTMX_SERVER_LOB_TABLESPACE

SQL files paths for an Admin in VoltMXFoundry_Plugins folder:

Path for SQL files in the VoltMXFoundry_Plugins folder	Database	Component
\VoltMXFoundry_Plugins\middleware\admindb_mysql	MySQL	Admin DB
\VoltMXFoundry_Plugins\middleware\admindb_oracle	Oracle
\VoltMXFoundry_Plugins\middleware\admindb_sqlserver	SQL Server

Execute all SQL scripts by using the steps provided at Configuring Flyway Command-line Tool.
Add the additional rows in the server_configuration table of ADMINDB. To add these rows, execute the following SQL query:

insert into currentSchema.server_configuration(prop_name, prop_value, created_date , updated_date) values(' management_server_host_name, ‘server_host_ip/name ', CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

# The following additional records will be inserted into the server_configuration table of the Admin DB:
- management_server_host_name
- management_server_port For Standalone JBoss the management_server_port should be jboss.management.http.port, which is present in the standalone.xml file.
- management_server_user
- management_server_password
- management_server_groups
- voltmx_server_shared_lib_name
  (This property is required for WebSphere only)
- management_server_scheme
- server_console_redirect_ip
  (This property is required for JBoss domain mode only)
- server_console_redirect_port
  (This property is required for JBoss domain mode only)
- management_server_truststore_filename
- management_server_keystore_filename
- management_server_truststore_password
- management_server_keystore_password
- cacheType = EHCACHE
- voltmxcentral.datasource = java:comp/env/jdbc/KDCDB

Important: If you are installing Volt MX Foundry V8 on an application server using the existing database and in case if there is a change in server details, you must update the management_server details in the admin database with the application server instance details for the WebAapp publish to work. You must update the following fields in the server_configuration table of the admin DB:
- management_server_host_name \<application_instance hostname>
- management_server_port \<soap port of application_instance>
- management_server_user \<application_instance admin username>
- management_server_password \<application_instance admin password>
- management_server_groups \<application_instance groups details>

Click here to view the Admin Server DB schema diagram

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