Create and Configure a Database for Quantum 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 Quantum Fabric with Oracle.

    Quantum

    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.KONY_SERVER_STORAGE_DATABASE_TYPE -> value must be 
mysql/oracle/sqlserver based on the chosen databases.

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

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

flyway.placeholders.KONY_SERVER_STORAGE_DATABASE_USERNAME -> database user name
flyway.placeholders.KONY_SERVER_STORAGE_DATABASE_PASSWORD ->:  (Encrypt this password)
   - For MySQL and SQL Server: database user password 

flyway.placeholders.KONY_SERVER_STORAGE_DATABASE_INSTANCE ->  MySQL – empty, 
SQL Server – instance name if given, Oracle - SID name.

flyway.placeholders.KONY_SERVER_CACHEID_TRANSPORT=""
      flyway.placeholders.KONY_SERVER_SESSION_DISTRIBUTED="FALSE"
      flyway.placeholders.KONY_SERVER_CACHE_TYPE="EHCACHE"
      flyway.placeholders.KONY_SERVER_CACHE_URL=""
flyway.placeholders.KONY_SERVER_JMS_INITIAL_CONTEXT_FACTORY=""
flyway.placeholders.KONY_SERVER_JMS_PROVIDER_URL=""
flyway.placeholders.KONY_SERVER_JMS_USER_NAME=""
flyway.placeholders.KONY_SERVER_JMS_USER_PASSWORD="" (Encrypt this password)
flyway.placeholders.KONY_SERVER_KEYSTORE_LOCATION=""
flyway.placeholders.KONY_SERVER_LOG_LOCATION=""
flyway.placeholders.KONY_SERVER_MEMCACHED_COUNT=""
flyway.placeholders.KONY_SERVER_MEMCACHE_CLUSTER=""
flyway.placeholders.KONY_SERVER_RICH_CLIENT_DEPLOY=""
flyway.placeholders.KONY_SERVER_TRUSTSTORE_LOCATION=""
flyway.placeholders.KONY_SERVER_TRUSTSTORE_PASSWORD=""  (Encrypt this password)
flyway.placeholders.KONY_SERVER_LOG_OPTION="logfile"
flyway.placeholders.KONY_SERVER_SSL_SOCKETFACTORY_PROVIDER=
com.ibm.websphere.ssl.protocol.SSLSocketFactory

flyway.placeholders.KONY_SERVER_SSL_SERVERSOCKETFACTORY_PROVIDER=
com.ibm.websphere.ssl.protocol.SSLServerSocketFactory
flyway.placeholders.KONY_SERVER_LOGGER_JNDI_NAME=jdbc/konyadmindb
flyway.placeholders.KONY_SERVER_SSL_SERVERSOCKETFACTORY_PROVIDER
flyway.placeholders.KONY_SERVER_SSL_SOCKETFACTORY_PROVIDER
flyway.placeholders.KONY_SERVER_LOG_OPTION 

      flyway.placeholders.SERVER_TOPIC_CONNECTION_FACTORY=ConnectionFactory
      No.Property namePlace holder
      1richclient.deploy

      ${KONY_SERVER_RICH_CLIENT_DEPLOY}

       

      Example value, lib/apps (Directory where the rich client binaries will be downloaded. Used by admin module)

      2memcache.cluster

      ${KONY_SERVER_MEMCACHE_CLUSTER}

      Example value, 10.10.10.10:21201 (<ip>:<port> where memcache cluster is running)

      NOTE: If the installation is being done without memcache, leave this value empty.

      3memcache.no.of.clients

      ${KONY_SERVER_MEMCACHED_COUNT}

      Example value, 1

      NOTE: If the installation is being done without memcache, leave this value empty.

      4cacheid.transport

      ${KONY_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)

      5ssl.trustStore

      ${KONY_SERVER_TRUSTSTORE_LOCATION}

      Example value, $JAVA_HOME/jre/lib/security/cacerts

      (cacerts Location)

      6ssl.keyStore

      ${KONY_SERVER_KEYSTORE_LOCATION}

      Example value, $JAVA_HOME/jre/lib/security/cacerts

      (cacerts Location)

      7ssl.trustStorePassword

      ${KONY_SERVER_TRUSTSTORE_PASSWORD}

      Example value, changeit

      8ssl.keyStorePassword

      ${KONY_SERVER_TRUSTSTORE_PASSWORD

      Example value, changeit

      9metrics.initialContextFactoryName

      ${KONY_SERVER_JMS_INITIAL_CONTEXT_FACTORY}

      Example value,

      • for WebLogic: weblogic.jndi.WLInitialContextFactory
      • for WebSphere: com.ibm.websphere.naming.WsnInitialContextFactory
      • for Tomcat: org.apache.activemq.jndi.ActiveMQInitialContextFactory
      • if jboss_jms is used: org.jboss.naming.remote.client.InitialContextFactory
      • if activemq is used: org.apache.activemq.jndi.ActiveMQInitialContextFactory
      10metrics.providerURL

      ${KONY_SERVER_JMS_PROVIDER_URL}

      Example value,

      • For WebLogic: t3://<ip>:<port>
      • For WebSphere: iiop://<ip>:<port>
      • For Tomcat:  
        tcp://$HOST_IP$:$USER_INPUT_JMS_PORT$?jms.useAsyncSend=TRUE
      • For JBoss: http-remoting://<Hostname/Host IP>:<HTTP Port>
      11metrics.securityPrincipalName

      ${KONY_SERVER_JMS_USER_NAME}

      Example value, Weblogic

      (weblogic admin username)

      12metrics.securityCredentials

      ${KONY_SERVER_JMS_USER_PASSWORD}

      Example value, Weblogic123

      (weblogic admin password)

      13metrics.userName

      ${KONY_SERVER_JMS_USER_NAME}

      Example value, Weblogic

      (weblogic admin username)

      14metrics.password

      ${KONY_SERVER_JMS_USER_PASSWORD}

      Example value, Weblogic123

      (weblogic admin password)

      15SERVER_LOG_LOCATION

      ${KONY_SERVER_LOG_LOCATION}

      Example value, C:/konymflogs/

      (Log location for middleware log)

      16SERVER_LOGGER_JNDI_NAME

      ${KONY_SERVER_LOGGER_JNDI_NAME}

      Example value,
      java:comp/env/jdbc/konyadmindb

    • Tablespace Placeholders for Oracle:
      Product NameTablespace Placeholders for Oracle
      Admin DB / Integration ServicesKONY_SERVER_DATA_TABLESPACE, KONY_SERVER_INDEX_TABLESPACE, KONY_SERVER_LOB_TABLESPACE

    • SQL files paths for an Admin in QuantumFabric_Plugins folder:

      Path for SQL files in the QuantumFabric_Plugins folder Database Component
      \QuantumFabric_Plugins\middleware\admindb_mysql MySQL

      Admin DB

       
      \QuantumFabric_Plugins\middleware\admindb_oracle Oracle
      \QuantumFabric_Plugins\middleware\admindb_sqlserver SQL Server
  3. Execute all SQL scripts by using the steps provided at Configuring Flyway Command-line Tool.
  4. 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 <AppServer_host_ip/name>
    • management_server_port <AppServer_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 <AppServer_username>
    • management_server_password <AppServer_encrypted password>
    • management_server_groups <server_groups>
    • kony_server_shared_lib_name <name_of_the_shared_library_created_in_websphere>
      (This property is required for WebSphere only      )
    • management_server_scheme <http_or_https>
    • server_console_redirect_ip <Appserver_host_ip/name>
      (This property is required for JBoss domain mode only      )
    • server_console_redirect_port <Master_Node HTTP/HTTPS 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
    • konycentral.datasource = java:comp/env/jdbc/KDCDB

IMPORTANT: If you are installing Kony Fabric 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 Kony Fabric 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