Database scripts and configuration

The ISTORE database is created by using a number of database scripts. These scripts are created based on your configuration and the type of action being completed against the database.

Change sets

When the database is created for the first time in a config, a change set is created that contains all of the scripts required to create the database for that configuration. The change set is in the following location: <config-name>/change-sets.

Each change set is built up in the following way: <number>-<action>-<date>-<time>. For example: 0-initial-2022.07.12-16.45.

Change sets are created for:

  • Initial deployment - initial
  • Additive updates - additive
  • Destructive updates - destructive
  • Upgrade - upgrade-release
  • Release - release

A change set contains the database scripts that are required to create the database or make additive updates, some also contain configuration changes that caused the change in database scripts.

Releasing database scripts

You can create a release change set that contains the database scripts required to create or update a database with the same changes as in your development environment.

For example, if you use the config development environment to develop a schema you will complete an iterative process that results in a number of changes to the database. When you are happy with the current database, you can create a release change set. You can use the database scripts in this change set to create a database for that schema in any environment. The first time you run this command, the release change set named 1-release-<date>-<time> is created. All other change sets that were present are removed.

To create a release change set, run:

deploy -r

If you have multiple configs, specify the config name with -c <config-name>.

When you run this command, a database backup is also created with the name of the change set. E.g. 1-release-<date>-<time>.

After you create the first release change set, you can continue to develop your configuration further with additive changes. You can create other release change sets that contain the scripts required to update the database from a previous release to the next one.

For example, if you run deploy -r for a second time you will have the following change-sets available:

- 1-release-<date>-<time>
- 2-release-<date>-<time>

You can use the scripts in the 2-release change set to update a database created with the 1-release change set.

Destructive changes

A destructive action requires you to recreate the database. You cannot update an existing database with the changes from a destructive change set. If you make a destructive change, you have 2 options:

  • Revert back to a release from before you made the destructive change.
    • Run deploy -r <release-change-set-number>.
      This will restore the database to the version at the specified release. All later change sets are removed.
  • Make the destructive change set the new first release.
    • Run deploy -r.
      If the system detects a destructive change set between now and the last release, you are prompted if you want to make the new release the 1st release change set. All other change sets are removed.

Upgrading

When an upgrade requires changes to the database, the scripts to complete these changes are captured in a change set. Any change sets that were created between the last release change set and the upgrade are removed. The upgrade change set is named <number>-upgrade-release-<date>-<time>.

Using change sets

If you want to use the change sets to recreate or update the Information Store database in another environment, you can run the database scripts from your release change sets. Each release change set contains a run-database-scripts.sh script that you can use to create or update your Information Store database. This script runs all of the .sql scripts in the changeset in the correct order.

Note: On Windows, you must run the shell script from WSL.

To run the scripts for a release, run the run-database-scripts.sh script for the change set:

  ./run-database-scripts.sh -s <db-server> -p <db-port> [-U <db-username> -P <db-password>]

Where:

  • -s <db-server> is the host name of the server running the database management system.
  • -p <db-port> is the port number for the database management system.
  • -U <db-username> is the user name of the user that can create or modify databases in your database management system.
    For information about the required credentials for the database in an on-prem deployment, see Specifying the deployment credentials.
  • -P <db-password> is the password for the user.

When you update the database in a system using the deployment toolkit, you must run the toolkit tasks that are used to update the Liberty server with the changes:

Allowed environment variables:

Variable name Description
DB_USERNAME The username for the DBA of the database
DB_PASSWORD The password for the DBA user of the database

The -U and -P flags, or the equivalent environment variables, are required for SQL Server.