Skip to content

Create and Configure a Database for Integration Services - Admin

Create and Configure a Database for Volt MX Admin

To create a database for Admin, follow these steps:

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

    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;

  2. 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** 
           - For Oracle: **admin user password** 
        flyway.placeholders.VOLTMX_SERVER_STORAGE_DATABASE_INSTANCE ->  MySQL – empty, 
        SQL Server – instance name if given, Oracle - SID, DB2 - DB 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_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
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, C:/programfiles/java/jdk1.7/lib/security/cacerts(cacerts Location)
6 ssl.keyStore ${VOLTMX_SERVER_KEYSTORE_LOCATION}
Example value, C:/programfiles/java/jdk1.7/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,
for WebSphere: com.ibm.websphere.naming.WsnInitialContextFactory
if jboss_jms is used: org.jboss.naming.remote.client.InitialContextFactory
if activemq is used: org.apache.activemq.jndi.ActiveMQInitialContextFactory
10 metrics.providerURL ${VOLTMX_SERVER_JMS_PROVIDER_URL}
Example value,For WebSphere: iiop://<ip>:<port>
For Tomcat/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 Volt MX 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

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