Prepare the runtime environment - connecting to the database

  1. In order to connect to ORACLE, you must have a database driver "dbmora*" in FGLDIR/dbdrivers.
  2. If you want to connect to a remote Oracle server from an application server, you must install the ORACLE Client Software on your application server and configure this.
  3. Make sure that the ORACLE client environment variables are properly set. Check variables such as ORACLE_HOME (the path to the installation directory), ORACLE_SID (the server identifier when connecting locally), etc. See the Oracle documentation for more details.
  4. Verify the environment variable defining the search path for database client shared libraries (libclntsh.so on UNIX™, OCI.DLL on Windows™). On UNIX platforms, the variable is specific to the operating system. For example, on Solaris and Linux™ systems, it is LD_LIBRARY_PATH, on AIX® it is LIBPATH, or HP/UX it is SHLIB_PATH. On Windows, you define the DLL search path in the PATH environment variable.
    ORACLE version Shared library environment setting
    Oracle 9.2 and higher UNIX: Add $ORACLE_HOME/lib to LD_LIBRARY_PATH (or its equivalent).Windows: Add %ORACLE_HOME%\bin to PATH.
  5. Check the database client locale settings (NLS_LANG,NLS_DATE_FORMAT, etc). The database client locale must match the locale used by the runtime system (LC_ALL,LANG).
  6. If you are using the TNS protocol, verify if the ORACLE listener is started on the server.
  7. To test the client environment settings, you can try to connect to the ORACLE server with the SQL*Plus tool:

    $ sqlplus username/password@service

  8. Set up the fglprofile entries for database connections. Make sure that you are using the ODI driver corresponding to the database client and server version. Because Informix® features emulation are dependant from the database server version, it is mandatory to use the same version of the database client and ODI driver as the server version.
  9. Set up fglprofile for the SERIAL emulation method. The following entry defines the SERIAL emulation method. You can use the SEQUENCE based trigger or the SERIALREG based trigger method:
    dbi.database.dbname.ifxemul.datatype.serial.emulation = "(native|regtable)" 

    The value 'native' selects the SEQUENCE based method, and the value 'regtable' selects the SERIALREG based method. This entry has no effect if dbi.database.<dbname>.ifxemul.datatype.serial is set to 'false'.

    The default is SERIAL emulation enabled with native method (SEQUENCE-based). See issue SERIAL data types for more details.

  10. Define the database schema selection if needed.
    The following entry defines the database schema to be used by the application. The database interface automatically executes an "ALTER SESSION SET CURRENT_SCHEMA <owner>" instruction to switch to a specific schema:
    dbi.database.dbname.ora.schema = "name"

    Here dbname identifies the database name used in the BDL program (DATABASE dbname) and name is the schema name to be used in the ALTER SESSION instruction. If this entry is not defined, no ALTER SESSION instruction is executed and the current schema defaults to the user's name.

  11. Define pre-fetch parameters.
    Oracle offers high performance by pre-fetching rows in memory. The pre-fetching parameters can be tuned with the following entries:
    dbi.database.dbname.ora.prefetch.rows = integer
    dbi.database.dbname.ora.prefetch.memory = integer # in bytes

    These values will be applied to all application cursors.

    The interface pre-fetches rows up to the prefetch.rows limit unless the prefetch.memory limit is reached, in which case the interface returns as many rows as will fit in a buffer of size prefetch.memory. By default, pre-fetching is on and defaults to 10 rows; the memory parameter is set to zero, so the memory size is not included in computing the number of rows to prefetch.

  12. If needed, define a specific command to generate session identifiers with this FGLPROFILE setting:
    dbi.database.dbname.ora.sid.command = "SELECT ..." 

    This unique session identifier will be used to create table names for temporary table emulation.

    By default, the database driver will use "SELECT USERENV('SESSIONID') FROM DUAL".

  13. The default temporary table emulation uses regular permanent tables. If this does not fit your needs, you can use GLOBAL TEMPORARY TABLES with this FGLPROFILE setting:
    dbi.database.dbname.ifxemul.temptables.emulation = "global"
  14. By default, the Oracle database driver will use native scrollable cursors. You can turn on scrollable cursor emulation with the next FGLPROFILE setting:
    dbi.database.dbname.ora.cursor.scroll.emul = true