Configure the FME Flow Database on a Separate Database Server

To configure FME Flow to use a separate database server, follow the steps below. You can configure FME Flow with a PostgreSQL (recommended), Microsoft SQL Server, or Oracle database, running on either Windows or Linux.

Note  PostgreSQL is the recommended database server with FME Flow, offering enhanced performance and stability through targeted optimizations.
  1. Database Configuration: Set up FME Flow database tables and database users with permissions to access the FME Flow database.
    2. Note  The specific way to set up database tables and user permissions on various production databases may differ depending on the available database tools and intended target platform.

    PostgreSQL is an open source database that can be downloaded from the Internet. For more information, see the PostgreSQL web site at:

    https://www.postgresql.com

    It’s assumed that PostgreSQL is already installed .

    One notation used is <FMEFlowDir>, which is the installation directory of FME Flow. In Windows, this is typically C:\Program Files\FMEFlow.

    It’s also assumed that you have JRE 5 or higher and PostgreSQL database installed before you proceed further. If you want to use the JDBC driver, there are several steps to perform.

    If you are upgrading, you should back up any job history you want to keep.

    This section describes how to configure a PostgreSQL database. You can configure this database from other PostgreSQL database tools, depending on your personal preference.

    For a PostgreSQL database configuration, various SQL scripts are provided to assist you. These SQL scripts are located in the following directory:

    <FMEFlowDir>\Server\database\postgresql

    Note  This directory refers to the machine on which the FME Flow Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the database machine, or reference it through a UNC path.
    • postgresql_createUser.sql – This script creates the FME Flow database user and password that you specified during installation, and grants all required permissions to this user. The password appears as <<DATABASE_PASSWORD>>. Before running this script, replace <<DATABASE_PASSWORD>> with the password that was specified for the database during the FME Flow installation.
    • postgresql_createDB.sql – This script creates the FME Flow database.
    • postgresql_createSchema.sql – This script creates all FME Flow related tables, indexes, views, and triggers.
    • postgresql_dropUser.sql – This script drops the FME Flow database user.
    • postgresql_dropDB.sql – This script drops the FME Flow database.

    Using the Command Prompt

    To use the command prompt to configure the database, follow these instructions:

    1. Install PostgreSQL SQL shell (psql) if you haven’t done so already.
    2. Open psql and log on as a user with the appropriate privileges:

    3. Create an FME Flow database user as follows:
      1. Open postgresql_createUser.sql in a text editor, and replace <<DATABASE_PASSWORD>> with the password specified during the FME Flow installation.
      2. From the SQL prompt, run the postgresql_createUser.sql script by entering the following command:

        3. \i '<FMEFlowDir>/Server/database/postgresql/postgresql_createUser.sql'

        By default, the provided SQL script creates the FME Flow database user and password that you specified during installation:

    4. Create an FME Flow database.

      5. From the SQL prompt, run the postgresql_createDB.sql script by entering the following command:

      \i '<FMEFlowDir>/Server/database/postgresql/postgresql_createDB.sql'

      By default, the provided SQL script creates an FME Flow database named fmeflow and grants all privileges on the database to the user.

    5. Quit psql and log on as the FME Flow database user.

    6. Create the FME Flow database schema as follows:

      7. From the SQL prompt, run the postgresql_createSchema.sql script by entering the following command:

      \i '<FMEFlowDir>/Server/database/postgresql/postgresql_createSchema.sql'

      By default, the provided SQL script creates all FME Flow related tables, indexes, views, and triggers.

    This section describes configuring PostgreSQL from both the command prompt and a user interface, such as the PostgreSQL Query Browser and the PostgreSQL Administrator.

    For PostgreSQL configurations, various SQL scripts are provided to help with PostgreSQL database configuration. These SQL scripts are located in the <FMEFlowDir>/Server/database/postgresql/ directory of the FME Flow Core machine.

    Note  This directory refers to the machine on which the FME Flow Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the database machine, or reference it through a UNC path.
    • postgresql_createUser.sql – This script creates the FME Flow database user and password that you specified during installation, and grants all required permissions to the FME Flow database user. The password appears as <<DATABASE_PASSWORD>>. Before running this script, replace <<DATABASE_PASSWORD>> with the password that was specified for the database during the FME Flow installation.
      • Note  The password is encrypted and appears in the script as <<DATABASE_PASSWORD>>.
    • postgresql_createDB.sql – This script drops the FME Flow database and creates a new one with the required FME Flow tables. By default this database is named fmeflow. Be careful when running this script because it drops the existing FME Flow database.
    • postgresql_createSchema.sql – This script creates all FME Flow related tables, indexes, views, and triggers.
      • Note  This script must be run as user fmeflow.
    • postgresql_dropDB.sql – This script drops the existing FME Flow database, which is named fmeflow by default.
    • postgresql_dropUser.sql – This script drops the FME Flow database user.

    Using the Command Prompt

    To use the command prompt to configure the database, follow these instructions:

    1. Install PostgreSQL Server if you haven’t already.
    2. Open a command prompt and change to the following directory, which contains the PostgreSQL SQL scripts:

      3. <FMEFlowDir>/Server/database/postgresql/

    3. Open postgresql_createUser.sql in a text editor, and replace <<DATABASE_PASSWORD>> with the password specified during the FME Flow installation.

    4. Run the postgresql program with the postgresql_createUser.sql script:

      psql -d postgres -h <hostname> -p <port> -f postgresql_createUser.sql

    5. Run the postgresql program with the postgresql_createDB.sql script:

      psql -d postgres -h <hostname> -p <port> -f postgresql_createDB.sql

    6. Run the postgresql program with the postgresql_createSchema.sql script, as the user you specified during installation:

      psql -U <username> -d fmeflow -h <hostname> -p <port> -f postgresql_createSchema.sql

    Note  If you drop the FME Flow database and login user, run the postgresql program with the appropriate postgresql_dropDB.sql or postgresql_dropUser.sql scripts.

    After Database Creation

    1. On the machine on which the FME Flow database server is installed, open pg_hba.conf. For default installation directories of this file:
      • PostgreSQL 10: <PostgreSQLDir>\data\pg10\pg_hba.conf
      • PostgreSQL 11: C:\Program Files\PostgreSQL\11\data\pg_hba.conf
    2. Change the following lines:

      3. host    all     all     127.0.0.1/32      md5

      host    all     all     ::1/128         md5

      to:

      host    all      all       0.0.0.0/0         md5

      host   all      all     ::/0             md5

    3. Restart the FME Flow Database service.

    It is assumed that SQL Server is installed.

    One notation used is <FMEFlowDir>, which is the installation directory of FME Flow. In Windows, this is typically C:\Program Files\FMEFlow.

    SQL scripts are provided to help with SQL Server database configuration. These SQL scripts are located in the <FMEFlowDir>\Server\database\sqlserver\ directory of the FME Flow Core machine.

    Note  This directory refers to the machine on which the FME Flow Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the database machine, or reference it through a UNC path.

    If you are upgrading, you should back up any job history you want to keep.

    Prerequisite for Unsecured Database Servers

    If your SQL Server database server is not secured with a valid SSL certificate, you must update the fmeDatabaseConfig.txt configuration file as follows:

    1. Near the bottom, under the heading FME SERVER SETTINGS START, locate the block of code beginning with DB_TYPE=sqlserver.

    2. In the corresponding line for the DB_JDBC_URL parameter, update the encrypt value from true to false.

    3. Save and close the file.

    1. Open the SQL Server Management Studio and connect to the SQL Server database engine.
    2. Open sqlserver_createDB.sql and run the script against the fmeflow database, as shown below:

    3. On the Object Explorer pane, expand the Security folder, right-click Login, and then select New Login:

    4. On the Login – New page, click General to open the page shown in the example below.
      • At the Login name field, enter fmeflow.
      • Make sure the option button is active for SQL Server authentication.
      • In the Password field enter the password that was specified for the database during the FME Flow installation, and in the Confirm password field enter the same password again.
      • Make sure the Enforce password policy is active. If not, click the checkbox to activate it.
      • If required, click the checkbox for Enforce password expiration to clear it.
      • At the Default database field enter fmeflow.

    5. On the same Login – New page, click User Mapping to open the page shown in the next figure.
      • At the Users mapped to this login location, in the Map column click the checkbox to activate it and map the login name to the fmeflow database.
      • At the Database role membership for location, notice that the fmeflow database is specified and check db_owner to assign that role to this database.
      • Click OK to close this page.

    1. Connect to the SQL Server database engine and run sqlserver_createDB.sql.
    2. Open sqlserver_createUser.sql.
    3. Look for the line that begins:

      4. CREATE LOGIN fmeflow WITH PASSWORD =

      Replace <<DATABASE_PASSWORD>> with the password specified during the installation.

    4. Run the script.
    Note  If your SQL Server uses an Always On availability group, you must also configure the MultisubnetFailover property of the DB_JDBC_URL parameter in the fmeDatabaseConfig.txt configuration file. For more information, see the SQL Server examples under Configure the Database Connection.

    Before proceeding, you must provide the following:

    <FMEFlowDir> is the installation directory of FME Flow. In Windows, this is typically C:\Program Files\FMEFlow.

    If you are upgrading, you should back up any job history you want to keep.

    This section describes how to configure an Oracle database. You can configure the Oracle database from other Oracle database tools depending on your personal preference.

    For an Oracle database configuration, various SQL scripts are provided to help.

    These SQL scripts are located in the <FMEFlowDir>\Server\database\oracle directory of the FME Flow Core machine.

    Note  This directory refers to the machine on which the FME Flow Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the database machine, or reference it through a UNC path.
    • oracle_createUser.sql – This script creates the FME Flow database user, and grants all required permissions to the FME Flow database user. The password appears as <<DATABASE_PASSWORD>>. Before running this script, replace <<DATABASE_PASSWORD>> with the password that was specified for the database during the FME Flow installation.
    • oracle_createDB.sql – This script creates all FME Flow related database packages, tables, indexes, views, and triggers.
    • oracle_dropUser.sql – This script drops the FME Flow database user and the database packages, tables, indexes, views, and triggers associated with the FME Flow database user.
    Using the Command Prompt

    To use the command prompt to configure the database, follow these instructions:

    1. Install Oracle SQL*Plus, if you haven’t already.
    2. Open Oracle SQL*Plus and log on as a user with adequate privileges, such as SYSDBA.

      3. In this example, the following parameters were used and entered:

      • User: SYS AS SYSDBA
      • Password: fmeflow
      • String: XX-ORACLE11G:1521/ORCL AS SYSDBA

    3. Create an FME Flow database user as follows:
      1. In oracle_createUser.sql, replace <<DATABASE_PASSWORD>>with the password that was specified for the database during the FME Flow installation.
      2. From the SQL prompt, run the oracle_createUser.sql script by entering the following command:

        3. @”<FMEFlowDir>\Server\database\oracle\oracle_createUser.sql”

        The default TABLESPACE is the Oracle DB configured default. If the created user doesn’t have read/write permissions on the default TABLESPACE, change the default TABLESPACE or create an appropriate TABLESPACE.

        This command creates a TABLESPACE on the Oracle DB server called FMEFLOW shown in the following example:

        CREATE SMALLFILE TABLESPACE FMEFLOW DATAFILE 'E:\ORADATA\FMEFLOW01.DBF' SIZE 100000K REUSE AUTOEXTEND ON NEXT 2048K MAXSIZE 1024M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS;

        By default this command uses the TABLESPACE “FMEFLOW” as shown in the following example:

        CREATE USER fmeflow IDENTIFIED BY fmeflow DEFAULT TABLESPACE "FMEFLOW" TEMPORARY TABLESPACE "TEMP";

    4. When an FME Flow database user is created, you can connect to it by logging on again to SQL*Plus.

      5. In this example, the following parameters were used and entered on the user interface:

      • User: fmeflow
      • Password: fmeflow

      Note   The preceding example assumes your user name and password are fmeflow.
    5. When connected to the FME Flow database, run the oracle_createDB.sql script by entering the following command:

      6. @”<FMEFlowDir>\Server\database\oracle\oracle_createDB.sql”

    Note   If you drop the FME Flow database and login user, run the oracle_dropUser.sql script.

    Oracle SQL*Plus is a freeware client for connecting to an Oracle database. You can download it from the Internet using the following address:

    https://www.oracle.com/technology/software/tech/sql_plus/index.html

    For more information, see the Oracle web site at:

    https://www.oracle.com/technology/sample_code/tech/sql_plus/htdocs/demobld.html

    The instructions that follow describe how to configure Oracle for use with FME Flow. These instructions describe using SQL*Plus for the database setup. Depending on your personal preference, you can also configure the Oracle database using other Oracle tools.

    For Oracle configurations, various SQL scripts are provided to help with Oracle database configuration. These SQL scripts are located in the <FMEFlowDir>/Server/database oracle/ directory of the FME Flow Core machine.

    Note  This directory refers to the machine on which the FME Flow Core is installed (not the machine on which the database server is installed). To run these scripts, you must transfer this directory to the database machine, or reference it through a UNC path.
    • oracle_createUser.sql – This script creates the FME Flow database user, and grants all required permissions to the FME Flow database user. The password appears as <<DATABASE_PASSWORD>>. Before running this script, replace <<DATABASE_PASSWORD>> with the password that was specified for the database during the FME Flow installation.
    • oracle_createDB.sql – This script creates all FME Flow related database packages, tables, indexes, views, and triggers.
    • oracle_dropUser.sql – This script drops the FME Flow database user and the database packages, tables, indexes, views, and triggers associated with the FME Flow database user.
    Using the Command Prompt

    To use the command prompt to configure the database, follow these instructions:

    1. Install the Oracle Client Tools, which includes SQL*Plus, if you haven’t already.

    2. Run sqlplus and log on as a user with adequate privileges, such as SYSDBA.

      For example:

      sqlplus SYS/<password>@<Host>:<Port>/<service> AS SYSDBA

    3. Create the FME Flow database user:

      1. In a text editor, open the oracle_creatorUser.sql script and update the password <<DATABASE_PASSWORD>> to the one specified during the FME Flow installation.
      2. From the SQL prompt, run the oracle_createUser.sql script by entering the following command:

        3. @”<FMEFlowDir>/Server/database/oracle/oracle_createUser.sql”

        The default TABLESPACE is the Oracle DB configured default. If the created user doesn’t have read/write permissions on the default TABLESPACE, you need to change the default TABLESPACE or create an appropriate TABLESPACE.

        This command creates a TABLESPACE on the Oracle DB server called FMEFLOW shown in the following example:

        CREATE SMALLFILE TABLESPACE FMEFLOW DATAFILE '/ORADATA/FMEFLOW01.DBF' SIZE 100000K REUSE AUTOEXTEND ON NEXT 2048K MAXSIZE 1024M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS;

        By default this command uses the TABLESPACE “FMEFLOW” shown in this example:

        CREATE USER fmeflow IDENTIFIED BY fmeflow DEFAULT TABLESPACE "FMEFLOW" TEMPORARY TABLESPACE "TEMP";

    4. When an FME Flow database user has been created, you can connect to it by logging on again to SQL*Plus or by entering the following command:

      CONNECT <UserName>/<Password>@<Host>:<Port>/<service>;

    5. When connected to the FME Flow database, run the oracle_createDB.sql script by entering the following command:

      @”<FMEFlowDir>/Server/database/oracle/oracle_createDB.sql”

  2. Enable connections: FME Flow connects to the database when it starts. Therefore, ensure that your database server is running and configured to accept incoming connections before FME Flow is started. The database must allow connections over TCP/IP with all machines on which the FME Flow Core and FME Engines are installed.
  3. Ensure that all installations of FME Flow are running. For more information, see Working with the FME Flow System Services.

What's Next?