Configure theFME FlowDatabase on a Separate Database Server

Note  "> NoteComplete this steponlyif you are configuring theFME FlowDatabase on your own database server, and didnotinstall the PostgreSQL database that is included with theFME Flowinstaller. For more information, seeProvide a Database Server.

To configureFME Flowto use a separate database server, follow the steps below. You can configureFME Flowwith a Microsoft SQL Server, PostgreSQL, or Oracle database, running on either Windows or Linux.

  1. Database Configuration: Set upFME Flowdatabase tables and database users with permissions to access theFME Flowdatabase.
    2. Note  "> NoteThe 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.

    It is assumed that SQL Server is installed.

    One notation used is<FMEFlowDir>, which is the installation directory ofFME Flow. In Windows, this is typicallyC:\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 theFME FlowCore machine.

    Note  "> NoteThis directory refers to the machine on which theFME FlowCore 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.

    1. Open the SQL Server Management Studio and connect to the SQL Server database engine.
    2. Opensqlserver_createDB.sqland run the script against thefmeserverdatabase, 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, enterfmeserver.
      • 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 theFME Flowinstallation, 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 fmeserver.

    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 thefmeserverdatabase.
      • At the Database role membership for location, notice that the fmeserver database is specified and checkdb_ownerto assign that role to this database.
      • Click OK to close this page.

    1. Connect to the SQL Server database engine and runsqlserver_createDB.sql.
    2. Opensqlserver_createUser.sql.
    3. Look for the line that begins:

      4. CREATE LOGIN fmeserver WITH PASSWORD =

      Replace<>with the password specified during the installation.

    4. Run the script.
    Note  "> NoteIf your SQL Server uses an Always On availability group, you must also configure theMultisubnetFailoverproperty of theDB_JDBC_URLparameter in the fmeDatabaseConfig.txt configuration file. For more information, see the SQL Server examples underConfigure the Database Connection.

    Before proceeding, you must provide the following:

    <FMEFlowDir>is the installation directory ofFME Flow. In Windows, this is typicallyC:\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\Server\database\oracledirectory of theFME FlowCore machine.

    Note  "> NoteThis directory refers to the machine on which theFME FlowCore 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 theFME Flowdatabase user, and grants all required permissions to theFME Flowdatabase user. The password appears as<>. Before running this script, replace<>with the password that was specified for the database during theFME Flowinstallation.
    • oracle_createDB.sql– This script creates allFME Flowrelated database packages, tables, indexes, views, and triggers.
    • oracle_dropUser.sql– This script drops theFME Flowdatabase user and the database packages, tables, indexes, views, and triggers associated with theFME Flowdatabase 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: fmeserver
      • String: XX-ORACLE11G:1521/ORCL AS SYSDBA

    3. Create anFME Flowdatabase user as follows:
      1. Inoracle_createUser.sql, replace<>with the password that was specified for the database during theFME Flowinstallation.
      2. From the SQL prompt, run theoracle_createUser.sqlscript by entering the following command:

        3. @”\Server\database\oracle\oracle_createUser.sql”

        默认表空间是Oracle数据库配置default. If the created user doesn’t have read/write permissions on the default TABLESPACE, change the default TABLESPACE or create an appropriate TABLESPACE.

        这个命令创建一个表空间的Oracle数据库server called FMESERVER shown in the following example:

        CREATE SMALLFILE TABLESPACE FMESERVER DATAFILE 'E:\ORADATA\FMESERVER01.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 “FMESERVER” as shown in the following example:

        CREATE USER fmeserver IDENTIFIED BY fmeserver DEFAULT TABLESPACE "FMESERVER" TEMPORARY TABLESPACE "TEMP";

    4. When anFME Flowdatabase 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: fmeserver
      • Password: fmeserver

      Note  "> NoteThe preceding example assumes your user name and password are fmeserver.
    5. When connected to theFME Flowdatabase, run theoracle_createDB.sqlscript by entering the following command:

      6. @”\Server\database\oracle\oracle_createDB.sql”

    Note  "> NoteIf you drop theFME Flowdatabase and login user, run theoracle_dropUser.sqlscript.

    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 withFME 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/Server/database oracle/directory of theFME FlowCore machine.

    Note  "> NoteThis directory refers to the machine on which theFME FlowCore 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 theFME Flowdatabase user, and grants all required permissions to theFME Flowdatabase user. The password appears as<>. Before running this script, replace<>with the password that was specified for the database during theFME Flowinstallation.
    • oracle_createDB.sql– This script creates allFME Flowrelated database packages, tables, indexes, views, and triggers.
    • oracle_dropUser.sql– This script drops theFME Flowdatabase user and the database packages, tables, indexes, views, and triggers associated with theFME Flowdatabase 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. Runsqlplusand log on as a user with adequate privileges, such as SYSDBA.

      For example:

      sqlplus SYS/@:/AS SYSDBA

    3. Create theFME Flowdatabase user:

      1. In a text editor, open theoracle_creatorUser.sqlscript and update the password<>to the one specified during theFME Flowinstallation.
      2. From the SQL prompt, run the oracle_createUser.sql script by entering the following command:

        3. @”/Server/database/oracle/oracle_createUser.sql”

        默认表空间是Oracle数据库配置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.

        这个命令创建一个表空间的Oracle数据库server called FMESERVER shown in the following example:

        CREATE SMALLFILE TABLESPACE FMESERVER DATAFILE '/ORADATA/FMESERVER01.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 “FMESERVER” shown in this example:

        CREATE USER fmeserver IDENTIFIED BY fmeserver DEFAULT TABLESPACE "FMESERVER" TEMPORARY TABLESPACE "TEMP";

    4. When anFME Flowdatabase 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 theFME Flowdatabase, run the oracle_createDB.sql script by entering the following command:

      @”/Server/database/oracle/oracle_createDB.sql”

    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, which is the installation directory ofFME Flow. In Windows, this is typicallyC:\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:

    \Server\database\postgresql

    Note  "> NoteThis directory refers to the machine on which theFME FlowCore 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 theFME Flowdatabase user and password that you specified during installation, and grants all required permissions to this user. The password appears as<>. Before running this script, replace<>with the password that was specified for the database during theFME Flowinstallation.
    • postgresql_createDB.sql– This script creates theFME Flowdatabase.
    • postgresql_createSchema.sql– This script creates allFME Flowrelated tables, indexes, views, and triggers.
    • postgresql_dropUser.sql– This script drops theFME Flowdatabase user.
    • postgresql_dropDB.sql– This script drops theFME Flowdatabase.

    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. Openpsqland log on as a user with the appropriate privileges:

    3. Create anFME Flowdatabase user as follows:
      1. Openpostgresql_createUser.sqlin a text editor, and replace<>with the password specified during theFME Flowinstallation.
      2. From the SQL prompt, run thepostgresql_createUser.sqlscript by entering the following command:

        3. \i '/Server/database/postgresql/postgresql_createUser.sql'

        By default, the provided SQL script creates theFME Flowdatabase user and password that you specified during installation:

    4. Create anFME Flowdatabase.

      5. From the SQL prompt, run thepostgresql_createDB.sqlscript by entering the following command:

      \i '/Server/database/postgresql/postgresql_createDB.sql'

      By default, the provided SQL script creates anFME Flowdatabase named fmeserver and grants all privileges on the database to the user.

    5. Quitpsqland log on as theFME Flowdatabase user.

    6. Create theFME Flowdatabase schema as follows:

      7. From the SQL prompt, run thepostgresql_createSchema.sqlscript by entering the following command:

      \i '/Server/database/postgresql/postgresql_createSchema.sql'

      By default, the provided SQL script creates allFME Flowrelated 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/Server/database/postgresql/directory of theFME FlowCore machine.

    Note  "> NoteThis directory refers to the machine on which theFME FlowCore 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 theFME Flowdatabase user and password that you specified during installation, and grants all required permissions to theFME Flowdatabase user. The password appears as<>. Before running this script, replace<>with the password that was specified for the database during theFME Flowinstallation.
      • Note  "> NoteThe password is encrypted and appears in the script as<>.
    • postgresql_createDB.sql– This script drops theFME Flowdatabase and creates a new one with the requiredFME Flowtables. By default this database is named fmeserver. Be careful when running this script because it drops the existingFME Flowdatabase.
    • postgresql_createSchema.sql– This script creates allFME Flowrelated tables, indexes, views, and triggers.
      • Note  "> NoteThis script must be run as user fmeserver.
    • postgresql_dropDB.sql– This script drops the existingFME Flowdatabase, which is named fmeserver by default.
    • postgresql_dropUser.sql– This script drops theFME Flowdatabase 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. /Server/database/postgresql/

    3. Openpostgresql_createUser.sqlin a text editor, and replace<>with the password specified during theFME Flowinstallation.

    4. 运行与postgresql_cre postgresql程序ateUser.sql script:

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

    5. 运行与postgresql_cre postgresql程序ateDB.sql script:

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

    6. 运行与postgresql_cre postgresql程序ateSchema.sql script, as the user you specified during installation:

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

    Note  "> NoteIf you drop theFME Flowdatabase and login user, run thepostgresqlprogram with the appropriatepostgresql_dropDB.sqlorpostgresql_dropUser.sqlscripts.

    After Database Creation

    1. On the machine on which theFME Flowdatabase 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. RestarttheFME FlowDatabase service.
  2. Enable connections:FME Flowconnects to the database when it starts. Therefore, ensure that your database server is running and configured to accept incoming connections beforeFME Flowis started. The database must allow connections over TCP/IP with all machines on which theFME FlowCore and FME Engines are installed.
  3. Ensure that all installations ofFME Floware running. For more information, seeWorking with the FME Flow System Services.

What's Next?