Versions Compared

Old Version 3

changes.mady.by.user Robert Andelin

Saved on

compared with

New Version 4

changes.mady.by.user Chieko Sugizaki

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space FUNCTDEV and version SVC2024.2

The Database Configuration screen enables you to create and configure databases using the embedded HyperSQL server or connect to an external , supported MySQL or Oracle database. You can also create and restore database backups, as well as port existing backups to a different type of database. For  For example, you could back up a HyperSQL database, then re-create it as an Oracle or MySQL database. Or, you could capture the current state of a database and use that for a rollback. 

...

Table of Contents
maxLevel2

...

Connecting to an Existing Database

This process connects the current CTP to an existing CTP database.

  1. Choose the database software from the Type menu.
    Image Added
  2. Specify the settings for connecting to your database.
  3. Click Configure Database Settings and select Connect to existing CTP database in the window that appears.
  4. Click Next.
    Image Removed
  5. Select a database type: HyperSQL, MariaDB, MySQL, Oracle, or PostgreSQL.
  6. Enter the database URL and user credentials.  See Database Settings below.
  7. Click Test Connection to confirm that it is correctverify the settings. Contact your database administrator if you have trouble using the settings you specified.
  8. Click Save & Connect to connect to complete the databaseconfiguration.

Creating a New Database

This process creates a new, empty CTP database. For information about creating a new CTP database from a backup, see Creating a Database from a Backup.

  1. Click Configure Database Settings and select Create new CTP database, user, and schema in the window that appears.
  2. Click Next.
    Image Removed
  3. Select a database type: HyperSQL, MariaDB, MySQL, Oracle, or PostgreSQL.
  4. Enter the database URL and root credentials. See Database Settings below.
  5. Click Test Connection to confirm that it is correct.
    Info

    For MySQL databases, the root user must have the GRANT OPTION privilege.

  6. Click Next.
    Image Removed

  7. Enter new user and credentials and click Create Database.

Creating a CTP Schema for an Existing Database

This process creates a CTP schema for an existing database with existing user credentials.

  1. Click Configure Database Settings and select Create CTP database schema in the window that appears.
  2. Click Next.
    Image Removed
  3. Select a database type: HyperSQL, MariaDB, MySQL, Oracle, or PostgreSQL.
  4. Enter the database URL and user credentials. See Database Settings below.
  5. Click Test Connection to confirm that it is correct.
  6. Click Create Schema.

Database Backups

...

To back up your CTP database, click Back Up Database on the Database Configurations page. What happens next depends on your database type:

  • If the database is HyperSQL, MariaDB, or MySQL, the backup will begin immediately.

  • If the database is Oracle, a dialog will appear asking for the data tablespace. Enter the name of the tablespace and click Create Backup and the backup will begin.

  • If the database is PostgreSQL, a dialog will appear for the schema. Enter the name of the schema and click Create Backup and the backup will begin.

...

  1. Click Create Database in the Database Connection Settings section. 
  2. Choose the database software from the Type menu.
    Image Added
  3. Specify the settings for connecting to your database. See Database Settings below. Alternatively, you could create a database from a backup file (see to Creating a Database from a Backup).
  4. Enable the Create a new user option if you want to create a new user with access to the database.
  5. Click Create 

Creating a Backup

  1. Click Backup Database in the Database Connection Settings section.
  2. Click the file link when prompted to download the backup file.
    Image Added

Backing Up Databases with Large Files (MySQL Only)

If you are using a MySQL database and the backup fails, try increasing the MySQL size limits as follows:

...

[client]

  • max_allowed_packet = 1G

Creating a Database from a Backup

...

  1. Click Create Database in the Database Connection Settings section.
  2. Specify information about the new database  
  3. Browse for a backup file In the Create from Backup File section and click Create.

A new database will be created in the specified location then it will be populated with the data saved in the backup file. After a new database is successfully created, CTP will ask you if you want it to delete the previous database (the database whose connection settings are shown in the main page behind the dialog).

Adding Multiple Databases

To add multiple databases of the same type, specify a different location

Migrating a Database

You can migrate a database from one type to another (for example, schema, SID, service name, or path) when creating a new database. This prevents the existing database from HyperSQL to PostgreSQL). Doing so is a basic two-step process:

  1. Create a backup of your existing database. See Creating a Database Backup.
  2. Restore your backup to the new database type. See Creating a Database from a Backup.

You should only do this within the same version of CTP. Keeping with the HyperSQL-to-PostgreSQL example from before, you want to create the backup of your HyperSQL database in the same version of CTP as your PostgreSQL database. Doing something like backing up your HyperSQL database in a previous version of CTP, then upgrading CTP and trying to create your PostgreSQL database with that backup could create errors.

being overwritten. 

  • HyperSQL: Enter a new file path.
  • MySQL: Enter a new schema.
  • Oracle: Enter a new SID or service name.

Image Added

To port a database to a different database type, select the target database type, then specify the desired location for the new database.

To create a new database account from this interface, enter the desired username and password, enable Create a new user, then confirm the password. For new accounts on Oracle, you can also specify a data tablespace and temp tablespace or you can leave those fields empty to use the defaults (<USER>_DATA> and <USER_TEMP>).

Image Added

Current Configuration

You can view the current database configuration in this section. 

Database Table Limits

Messages received by CTP's underlying Virtualize server are logged in the database. The event message, payload, hit statistics, and time information are stored. You can change the number of event messages and entries about each hit to the server in this section.

 Image Added

Database Settings

See System Requirements for details about supported database versions and requirements.

HyperSQL Settings

CTP ships with an embedded HyperSQL database. To use it, choose File path as the connection mode.

  • Connection mode: Choose whether to connect using File path or JDBC URL. The connection configuration field that appears depends on your selection.
    • File path: Specify the where the database files should be saved. The files should be saved on the Apache Tomcat host.  Prepend a forward slash ‘/’ to make the path absolute in the file system. 
    • JDBC URL:  Specify the JDBC URL used to connect to your database. Example JDBC URL:
      jdbc:hsqldb:file:/hsqldb/em;ifexists=true
  • Username: Specify a user that will be able to access to the database. Default is the em user. If the Create a new user option is enabled, a new user with the name specified in this field will be added to the database.
  • Password: Specify the password for the user that will be able to access the database. Default is the password for the em user. If the Create a new user option is enabled, a new user with the password specified in this field will be added to the database. 

MySQL and MariaDB Settings

  • Connection mode: Choose whether to connect using Host and port or JDBC URL. The connection configuration fields that appear depends on your selection.
    • Host and port:
      • Host: Specify the host where the MySQL server is installed.
      • Port: Specify the port on the host through which the MySQL database is running.
      • Schema: Specify the name of the schema (database) that should contain the data. Default is em.
    • JDBC URL:
      • JDBC URL: Specify the JDBC URL used to connect to your database. Example JDBC URLs:
        jdbc:mariadb://hostname:3306?useUnicode=true&characterEncoding=UTF-8&sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES
        jdbc:mysql://hostname:3306?useUnicode=true&characterEncoding=UTF-8&sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES&useSSL=false&allowPublicKeyRetrieval=true
  • Username: Specify a user that will be able to access to the database. Default is the em user. If the Create a new user option is enabled, a new user with the name specified in this field will be added to the database.
  • Password: Specify the password for the user that will be able to access the database. Default is the password for the em user. If the Create a new user option is enabled, a new user with the password specified in this field will be added to the database.

Oracle Settings

  • Connection mode: Choose whether to connect using Host and port or JDBC URL. The connection configuration fields that appear depends on your selection.
    • Host and port:
      • Host: Specify the host where the Oracle server is installed.
      • Port: Specify the port on the host through which the Oracle database is running.
      • Use service name: Enable this option if you want to connect to the Oracle database using the service name. Specify the service name in the field provided. Enabling this option disables the SID field.
      • SID: Specify the SID of the Oracle database if you want to connect using the SID. Enabling the User service name option disables the SID field.
    • JDBC URL:
      • JDBC URL: Specify the JDBC URL used to connect to your database. Example JDBC URLs:
        jdbc:oracle:thin:@//hostname:1521/service_name
        jdbc:oracle:thin:@//hostname:1521:sid
        jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service_name)))
  • Username: Specify the name of the schema (database) that should contain the data. Default is em.
  • Password: Specify the password for the user that will be able to access the database. Default is the password for the em user. If the Create a new user option is enabled, a new user with the password specified in this field will be added to the database.
Info
titleJDBC Driver

CTP does not ship with JDBC driver libraries for MySQL or Oracle. Refer to the JDBC Driver Notes in the installation guide for additional information.

Note

If the Oracle database user is created outside of CTP by a Database Administrator (DBA), then it needs permission to create tables so CTP can perform database schema updates when upgrading to new product versions.  The DBA should execute:  GRANT CREATE TABLE TO EM; where EM is the Oracle database user account that CTP will use when running.

Also see Installing and Running CTP.