Database Configuration

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 <FMEServerDir>\Server\database\oracle directory of the machine on which the FME Server core was installed (not the machine on which the database is installed).

• oracle_createUser.sql – This script creates FME Server database user and password, and grants all required permissions to the FME Server database user. By default, the user and password are both named fmeserver.
• oracle_createDB.sql – This script creates all FME Server related database packages, tables, indexes, views, and triggers.
• oracle_dropUser.sql – This script drops the FME Server database user and the database packages, tables, indexes, views, and triggers associated with the FME Server 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.

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 an FME Server database user as follows:
• From the SQL prompt, run the oracle_createUser.sql script by entering the following command:

@”<FMEServerDir>\Server\database\oracle\oracle_createUser.sql”



By default, the provided SQL script creates an FME Server database user named fmeserver with the password fmeserver. 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 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 an FME Server database user is created, you can connect to it by logging on again to SQL*Plus.

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

• User: fmeserver
• Password: fmeserver



Note:   The preceding example assumes your user name and password are fmeserver.

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

@”<FMEServerDir>\Server\database\oracle\oracle_createDB.sql”

Note:   If you drop the FME Server 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:

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

For more information, see the Oracle web site at:

http://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 Server. 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 <FMEServerDir>/Server/database oracle/ directory of the machine on which the FME Server core was installed (not the machine on which the database is installed).

• oracle_createUser.sql – This script creates FME Server database user and password, and grants all required permissions to the FME Server database user. By default, the user and password are both named fmeserver.
• oracle_createDB.sql – This script creates all FME Server related database packages, tables, indexes, views, and triggers.
• oracle_dropUser.sql – This script drops the FME Server database user and the database packages, tables, indexes, views, and triggers associated with the FME Server 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 an FME Server database user. From the SQL prompt, run the oracle_createUser.sql script by entering the following command:

   @”<FMEServerDir>/Server/database/oracle/oracle_createUser.sql”

   By default, the provided SQL script creates an FME Server database user named fmeserver with the password fmeserver. 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 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 an FME Server database user has been created, you can connect to it by logging on again to SQL*Plus or by entering the following command:

   CONNECT fmeserver/fmeserver@<Host>:<Port>/<service>;

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

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

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:

<FMEServerDir>\Server\database\postgresql

Note:  This directory refers to the machine on which the FME Server core was installed (not the machine on which the database is installed).

• postgresql_createUser.sql – This script creates the FME Server database user and password, and grants all required permissions to this user. By default, both the user and password are names fmeserver.
• postgresql_createDB.sql – This script creates the FME Server database.
• postgresql_createSchema.sql – This script creates all FME Server related tables, indexes, views, and triggers.
• postgresql_dropUser.sql – This script drops the FME Server database user.
• postgresql_dropDB.sql – This script drops the FME Server 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 Server database user as follows:
• From the SQL prompt, run the postgresql_createUser.sql script by entering the following command:

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

• By default, the provided SQL script creates an FME Server database user named fmeserver with the password fmeserver:



4. Create an FME Server database.

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

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

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



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



6. Create the FME Server database schema as follows:

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

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

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

Note:  The preceding example assumes your user name and password are fmeserver.

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 <FMEServerDir>/Server/database/postgresql/ directory of the machine on which the FME Server core was installed (not the machine on which the database is installed).

• postgresql_createUser.sql – This script creates the FME Server database user and password, and grants all required permissions to the FME Server database user. By default the user is named fmeserver and the password is fmeserver.
• postgresql_createDB.sql – This script drops the FME Server database and creates a new one with the required FME Server tables. By default this database is named fmeserver. Be careful when running this script because it drops the existing FME Server database.
• postgresql_createSchema.sql – This script creates all FME Server related tables, indexes, views, and triggers.
• postgresql_dropDB.sql – This script drops the existing FME Server database, which is named fmeserver by default.
• postgresql_dropUser.sql – This script drops the FME Server 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:

<FMEServerDir>/Server/database/postgresql/

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

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

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_createSchema.sql script, as user fmeserver:

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

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