Database Usage

The following deployment actions will affect the database structure and/or data content.

• Install/Replace (deploy-bundle-clean) - Wipe out existing database tables, install new bundle, create new schema.
• Update (deploy-bundle) - Upgrade an existing bundle, update existing Data Model.
• Reset (deploy-db-clean) - Reset the existing database to its default state from the command line or console. Options include Model Tables or Model and System Tables (everything).

Database Deployment Phases

1. Drop
2. Create/Update

The following table lists the command line (omni.sh/omni.cmd) deployment commands and their Omni Console equivalents.

During a database reset or a deployment that invokes the clean flag, the following database tables are dropped and recreated. These table identifiers are hardcoded in the server and will match exact names or character patterns if denoted by an asterisk (*).

These are performed after a new bundle is deployed or updated.

1. Drop existing database tables (if clean is specified – Reset/Replace only). This includes the following data sources:
   1. Model
   2. Ramp
   3. Source
   4. Clean system tables (Clean only).
   5. System (Reset only).
   6. Delete existing Liquibase Database changelogs.
2. Custom(er) database pre-deployment migration scripts.
3. Omni-Gen pre-deployment migration scripts.
4. Create/Update database tables. This includes the following data sources:
   1. Model
   2. Ramp
   3. Source
   4. System
5. Custom(er) database post-deployment migration scripts.
6. Omni-Gen post-deployment migration scripts.

The Work Order and Work Order Item (and other os* table measures, ramp, and so on) are created as part of the bundle deployment process.

1. The Controller checks on startup to ensure the os_work_order and os_work_order_item tables exist. If not, it creates them.
2. There will be two options for Database reset from the Console, see Deployment below.
3. Deployment
   1. Install/Replace - If the tables exist, all Work Orders with omni_system_type = "SERVER", and their related Work Order Items, are deleted.
   2. Upgrade/Update - If the tables do not exist (for some reason) they are created or updated, as needed.
   3. Database Reset
      1. The Model option resets all managed tables as before, except Work Order and Work Order Item are handled as in Install/Replace.
      2. The System and Model option resets all of the managed tables in the system (including Work Order and Work Order Item). This is the same behavior as the previous implementation of Reset Database.

There is a validation step during installation that checks for the existence of the system tables in the database. After you supply parameters for the Omni-Gen database, you are prompted to continue or quit the installation, based on the existence of the tables. This serves to prevent you from accidently overwriting database table information.

Generating JPA from IDS, changelogs, and so on.

1. Backup existing bundle.
2. Clean previous bundle files and generated artifacts.
3. Copy the bundle to your directory and unzip the contents.
4. Generate Effective IDS documents.
5. Generate IDS documentation.
6. Generate IDS sample OIDs.
7. Generate XSD schemas for the IDS documents.
8. Generate JPA model for the IDS documents.
9. Compile the JPA model.
10. Weave the JPA model.
11. Package and move the model jar.

Each time a bundle or database deployment is undertaken from the command line or user interface, a log file is created with a name and timestamp corresponding to the event.

• The file name is created by concatenating the operation name and a timestamp of when the event started. For example, deploy_update_2019-11-06 12-25-29.865.json
• Location: omnigen/OmniGenData/deployment
• The information in these files is the content that is loaded on the deployment progress screen of the Omni Console.

The following table provides a list of the possible generated log files and the corresponding deployment commands.

This process allows pre-deployment and post-deployment database scripts to be run from separate locations for customers and the internal Omni-Gen team.

The following directory structure exists to hold the source files used in the migration process. There are pre and post folders that contain a directory for each data source. The migration scripts should be added to the appropriate directory.

Customer: /omnigen/OmniGenData/migration

Omni-Gen: /omnigen/OmniServer/dbms/migration

The following is an example of a SQL wrapper for a Liquibase changelog:

• It is assumed that the end-user will be using this format, so the SQL syntax should be database vendor specific.
• This changeSet executes native SQL against the specified database (H2 in this case).

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/
dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-
3.3.xsd">
    <changeSet author="liquibase-docs" id="sql-example">
        <sql dbms="h2"
             endDelimiter="\nGO"
             splitStatements="true"
             stripComments="true">
             <comment>You must specify the Schema if it is not the 
default for the datasource</comment>
             <comment>This varies between databases</comment>
              create table person(name varchar(255));
              insert into person (name) values ('Bob');
        </sql>
    </changeSet>
</databaseChangeLog>

If you are not using the default schema for a data source, it must be explicitly specified in the migration script as shown in the changelog comment above.

It is recommended to continue using the standard changeset format to accommodate as many database vendors as possible. However, there may be vendor specific issues where the SQL version is more appropriate.

This section includes information on the database tables, as well as noted naming convention for the deployment specific tables.

• Liquibase
  • Holds transactions for all the operations performed by Liquibase on the given database.
  • Databasechangelog, databasechangeloglock
• Ramp
  • Used to load data from the ramp into the Omni-Gen environment.
  • Named: og_{subject_name}_r
• Source. Note: In Omni-Gen Integration Edition, source tables are not used.
  • Source data (does not get modified).
  • Named: og_{subject_name}_s
• Model
  • Instance data
  • Named: og_{subject_name}
• System tables
  • Used by Omni-Gen system, not dependent on the model.
  • Named: os_

Below is a list of Omni-Gen system tables. These are not subject specific and exist regardless of the subject deployment bundle.

System tables and descriptions – DO NOT MODIFY TABLES

The fundamental building block of Liquibase is the Changelog.

These are stored in omnigen/OmniServer/dbms/changelogs.

A set of changelogs is generated for each of the main data sources in the Omni-Gen system.

1. MigrateChangeLog.xml
   • A Liquibase changelog denoting the differential between the existing database and the generated JPA classes (Model Jar).
2. DropChangeLog.xml
   • A Liquibase changelog denoting the operations necessary to drop all of the specified tables in the given schema.
3. JpaModel.xml
   • A Liquibase changelog created directly from the JPA class definitions according to the persistence.xml.

Omni-Gen Liquibase Changelogs are stored in the following locations, under omnigen/OmniServer/dbms/:

• Changelogs: All dynamically generated changelogs created by Omni-Gen during the deployment process. This folder gets cleared during a Reset or Bundle Clean operation.
• Controller: Pre-written Liquibase scripts that run during the startup phase of the Controller.
• Migration:
  • Contains pre-written database scripts to aid in migration between versions or feature releases. These run every time deployment is executed.
  • Separate folders for each data source.
  • Different location for Information Builders system and customer-specific scripts.
• System: Contains pre-written scripts related to System database table maintenance. Currently, this only includes cleaning Work Order and Work Order Item tables, and their creation.

The Controller may execute a number of database operations, depending on the state of the system.

The root Liquibase script for the controller is stored in /OmniServer/dbms/controller. This script contains a list of scripts and files to execute that includes creating the Work Order and Work Order Item tables, if they do not already exist. If any issues occur during this process, the Controller will continue to start, either by executing an empty Changeset, or ignoring the exception. A message will be included in the Controller log file indicating the issue, and the Console will show the relative configuration errors.

The controller will validate all database connections at a set interval (every 20 seconds) and display an error regarding any issue, if one exists. You will be prohibited from taking any actions you would normally take with an invalid configuration. If the database reconnects or the settings are fixed, the errors will resolve and the console will return to normal.

The Operations pages on the Console provide a better idea of the inner workings of your Omni-Gen system. The database activity tab shows the slowest ten, and the most recent ten queries from the Server. You can enable this feature using the SQL Profiling Enabled option under Configuration, Databases for Model, Ramp, and Source before data is run through the system in order to gather the metrics.

The Omni-Gen application makes use of multiple database connection pools to manage database connections. Each is separately configurable, but a mechanism is provided to allow common-configuration (across server and controller) using the default settings. Configuration for controller and server can be changed in the OmniGenConfiguration.property file.

• Settings: defaultDataSourceSettings
• Property Prefix: server.datasource.default
• Max-active: 50
• Initial Size: 2

Omni Controller Application

• Pool: modelConnectionPool
• Construction: Eager
• Settings: modelDataSourceSettings
• Property Prefix: server.datasource.model
• Max-active: 50
• Initial Size: 2
• Derives From: defaultDataSourceSettings

Omni Controller Application

• Pool: systemConnectionPool
• Construction: Eager
• Settings: systemDataSourceSettings
• Property Prefix: server.datasource.system
• Max-active: 50
• Initial Size: 2
• Derives From: modelDataSourceSettings

Omni Server Application

• Pool: modelConnectionPool
• Construction: Lazy
• Settings: modelDataSourceSettings
• Property Prefix: server.datasource.model
• Max-active: 50
• Initial Size: 2
• Derives From: defaultDataSourceSettings

Omni Server Application

• Pool: rampConnectionPool
• Construction: Lazy
• Settings: rampDataSourceSettings
• Property Prefix: server.datasource.ramp
• Max-active: 50
• Initial Size: 2
• Derives From: defaultDataSourceSettings

Omni Server Application

• Pool: sourceConnectionPool
• Construction: Lazy
• Settings: sourceDataSourceSettings
• Property Prefix: server.datasource.source
• Max-active: 50
• Initial Size: 2
• Derives From: defaultDataSourceSettings

Omni Server Application

• Pool: systemConnectionPool
• Construction: Eager
• Settings: modelDataSourceSettings
• Property Prefix: server.datasource.model
• Max-active: 50
• Initial Size: 2
• Derives From: defaultDataSourceSettings

The following are descriptions of the database pools and connection information.

Application. Name of the Omni component that holds the pool.

Pool. Name of the JDBC pool. The pool name should be unique for each application and is not configurable.

Construction. Whether the pool is created at application startup (Eager), or is done when features needing the pool are activated (Lazy). Not configurable.

Settings. Name of the settings that govern the pool.

Derives From. If a pool property is not provided, the corresponding value from this setting will be used.

Property Prefix. Property prefix to alter the settings in the OmniGenConfiguration.property file.

Max-active. Maximum number of active connections that can be allocated from this pool at the same time. For JDBC, if additional connections are requested, they will block waiting for a free connection, and may timeout if one does not become available within the timeout period. Max-active is configurable by setting a property of PROPERTY_PREFIX.max-connections (for example, server.datasource.default.max-connections).

Max-idle. Maximum number of connections that should be kept in the idle pool.

Min-idle. Minimum number of connections that should be kept in the pool at all times. For JDBC, the default value for this property is derived from Initial Size.

Initial Size. Number of connections that will be established when the connection pool is started. Initial Size is configurable by setting a property of PROPERTY_PREFIX.initial-connections (for example, server.datasource.default.initial-connections).

Ramifications: With all services running, a minimum of 56 connections would exist, and at most, 440 connections would be open. Using the default settings, it is recommended that a database support 500 maximum connections. This is configured differently for each database.

Source control for your database Liquibase is a software framework that provides a database vendor agnostic abstraction over common database operations. Utilizing the Changeset structure (differing formats are XML, JSON, YAML), you define database operations in a Liquibase specific format. When the changeset is executed, it will manage any idiosyncrasies for the specific database vendor (SQL Server, Oracle, PostgreSQL, and so on).

http://www.liquibase.org/

Liquibase Notes: A version of Liquibase is being used that has been patched to address an issue. Deployment was failing against a SQL Server database with a case sensitive collation (for example, SQL_Latin1_General_CP1_CS_AS).

Change tracking: There is a special table that is utilized by Liquibase for the management and record keeping of changelog actions called databasechangelog. It contains a hash of the designated changeset, as well as other identifying information. Once created, the Omni-Gen system will not reset this table, it must be manually dropped.

Fields contained in this table:

Database Locking: Liquibase uses a distributed locking system to only allow one process to update the database at a time. The other processes will simply wait until the lock has been released. There is a special table created for this purpose called databasechangeloglock.

Fields contained in this table:

SQL Server

• All indexes are explicitly created as non-clustered. This was requested as a performance enhancement. By default, SQL Server will create a clustered index on the primary key, unless instructed otherwise.
• File locks are common.
• Limit to the length of indexes across columns (900 characters for releases prior to SQL Server 2016, 1700 for SQL Server 2016 and higher).
• No Drop with Cascade.
• Maximum object name is 100 characters.

Oracle

• Limit to the length of indexes across columns.
• Maximum object name is 30 characters.
• No Drop with Cascade.

PostgreSQL

• Maximum object name is 63 characters.

Db2

• No Drop with Cascade.
• To use Db2 as a repository database, the following tuning steps are required. This is due to the nature of Db2 and its requirement for higher memory consumption during the deployment phase. If the memory is not increased, you might encounter an OutOfMemoryError exception when resetting the environment or the deployment phase.

  For new installations:

  1. The Db2 JDBC URL should include a traceLevel=0 option during the configuration.
  2. Prior to running config on the binary, set cfg.server.commandline.max-memory=2048M in the configuration file.
  3. After config completes, verify server.commandline.max-memory=2048M in the OmniGenconfiguration.properties file.

  For existing installations:

  1. The Db2 JDBC URL should include a traceLevel=0 option during the configuration.
  2. In the Omni Console, navigate to Configuration, Runtime, and click the Command Line tab. Set the JVM Process Max Memory parameter to 2048M.
  3. Stop all processes and then restart.

H2

• This is used as the default by Omni-Gen if the system cannot configure a connection to an external database.

When handling a large number of records, you may want to optimize the database being used for Omni-Gen. One way to tune the performance of the database is to increase the amount of available memory. For example, for one million records, increasing the memory by 2GB could improve performance.