Jump to: navigation, search

ODBC Connection

Important
The information on this page was current at the time of the Interaction Server 8.5.1 release. For Interaction Server 8.5.3, refer to the Migrating to ODBC from DB Server page.

These directions apply primarily to Linux. Directions on deploying ODBC drivers for Windows are available on this page in the Integrated Capture Points documentation.

Install unixODBC

Download

For compatibility purposes, the easiest and fastest way to do this is to download a prebuilt RPM or package that is compatible with your current Linux version.

Important
The Microsoft web page about the Microsoft ODBC Driver 11 for SQL Server on Linux refers to only version 2.3.0 of unixODBC Driver Manager.

You can also

Warning
Genesys has no responsibility or license for unixODBC, which is a third-party package. Any and all of these unixODBC installation options are merely recommendations, and it is your responsibility to the choose one that fits your purpose best. There are dependencies for each of these options. Also, environments with alternative third-party drivers, database accelerators and ODBC Managers are not supported on compatibility issues. Genesys recommends that you not use ODBC drivers for different RDBMS (i.e. ORACLE and DB2) on UNIX platforms simultaneously.

Check

Once you have downloaded and installed unixODBC, check the odbcinst.ini (under /etc in the installation directory) and odbc.ini (in $HOME/.odbc.ini) files. This guide will refer to the unixODBC installation directory as the environment variable $UNIXODBC.

Install ODBC drivers

Oracle

Install the ODBC Driver

  1. Download the latest version of the ODBC driver from http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html. You will need these two packages:
    • Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications
    • Instant Client Package - ODBC: Additional libraries for enabling ODBC applications
  2. Unzip both packages to the same directory, which this guide will refer to as environment variable $DRIVER.
  3. From the $DRIVER directory, run the following: 
    ./odbc_update_ini.sh $UNIXODBC  $DRIVER [<Driver_Name>] [<DSN>]

    For example, 

    ./odbc_update_ini.sh $UNIXODBC $DRIVER oracleodbc-12.1 ora
  4. Set the three environment variables ORACLE_HOME, LD_LIBRARY_PATH, and TNS_ADMIN.
    • TNS_ADMIN is the directory containing the TNSNAMES.ora file, which describes Oracle connections. If such a file does not exist, you must create it.
    • ORACLE_HOME is the directory where the bin and lib directories are located for the Oracle client.
    • LD_LIBRARY_PATH is the load library path.

    For example, 

    export ORACLE_HOME=$DRIVER
export LD_LIBRARY_PATH= $UNIXODBC/lib/:$DRIVER/
export TNS_ADMIN=/etc/oracle

Sample

This is a sample of odbc.ini with a configured DSN: 

[ora]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = OracleODBC-12.1
DSN = ora
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = F
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = GENESYS_INX
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = SYSTEM
Password = system
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
MaxTokenSize=8192
Important
If Interaction Server uses ODBC to connect to the Oracle database in your environment, you must set the option Lobs to T in the odbc.ini file.

This is a sample of odbcinst.ini: 

[OracleODBC-12.1]
Description=Oracle ODBC driver for Oracle 12g
Driver=/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
Setup=
FileUsage=
CPTimeout=
CPReuse=
Driver Logging=7

In this example, the DSN name is ora. ServerName is the corresponding service name in TNSNAMES.ora: 

GENESYS_INX =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = fakehost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = genesys_inx)
    )
  )

MS SQL

Important
MS SQL officially supports only version 2.3.0 of unixODBC
  1. Run 
    export LD_LIBRARY_PATH= $UNIXODBC/lib
  2. Follow the installation instructions given at http://www.microsoft.com/en-us/download/details.aspx?id=36437
    If you want to use the MSSQL Driver with unixODBC version 2.3.1 or greater, you must perform the installation with the flag ‑force, and afterwards you must do the following:
    1. Locate the file libodbcinst.so.2 that was installed by unixODBC.
    2. Make a symbolic link to it: 
      sudo ln -s libodbcinst.so.2 usr/lib64/libodbcinst.so.1

    Odbcinst.ini should now contain the following: 

    [ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1
  3. Append a DSN entry name to odbc.ini, for example: 
    [sqltest]
Driver = ODBC Driver 11 for SQL Server
DSN = sqltest
Trace = No
ServerName = fakehost
UserID = genesys
Password = genesys
Database = genesys_inx

DB2

Install the ODBC Driver

  1. Download the IBM Data Server Driver for ODBC and the CLI (64-bit) driver for 64-bit operating systems from http://www-01.ibm.com/support/docview.wss?uid=swg24029746
  2. Decompress the driver package in the desired location (for example: /usr/local/db2odbc). We will refer to this location as $DRIVER.
  3. Add the driver manually by editing the odbcinst.ini file to add the following: 
    [DB2]
Description=DB2 ODBC Driver
Driver=$DRIVER /odbc_cli/clidriver/lib/libdb2o.so
  4. Save the file.

Test the Connection

  1. Create a new DSN:
    1. Create a file db2cli.ini in $DRIVER/odbc_cli/clidriver/cfg
    2. Add this section to it: 
      [SAMPLE_ODBC_DSN_NAME]
hostname=your_db2_server_host
port=your_db2_server_port
database=name_of_the_DB
protocol=TCPIP
autocommit=0
    3. Save the file
  2. Now you must add an entry to odbc.ini so unixODBC will know about that particular DSN: to do this, add the following section to odbc.ini: 
    [SAMPLE_ODBC_DSN_NAME]
Driver=DRIVER_NAME_FROM_ODBCINST.INI

    In our case the driver name is DB2 (see above), and the line will look like 

    Driver=DB2

Detailed installation instructions are at https://www-304.ibm.com/support/docview.wss?uid=swg21418043

PostgreSQL

Information about PostgreSQL is provided in the Framework Database Connectivity Reference Guide and at http://www.postgresql.org/.

Configure the DAP

This section applies to Interaction Server and Event Logger. Related information about Database Capture Point is in a separate location.

For an ODBC connection, you must configure the Database Access Point (DAP) associated with the Application in question, as follows:

  1. Add an option called dbprotocol to the [settings] section of the Interaction Server DAP and give it the setting odbc. For the Event Logger DAP, the equivalent is the delivery-protocol option in the [logger-settings] section. This forces Interaction server to use ODBC to access the databases.
  2. In Genesys Administrator, enter the following in the [DB Info] section of the Configuration tab (in Configuration Manager, on the DB Info Tab):
    • DBMS Name
    • DBMS Type
    • Database Name
    • User Name
    • Password
  3. Optionally, specify a non-default driver, as described below.

To make switching to ODBC easier for existing installations, Interaction Server will try to build the ODBC connection strings itself based on the information that is available in the DAP. During this process, Interaction Server uses the following default names for the drivers:

Database Type Default Driver
MS SQL {SQL Server Native Client 10.0}
DB2 {IBM DB2 ODBC DRIVER - DB2COPY1}
Oracle {Oracle in OraClient11g_home1}
PostgreSQL {PostgreSQL ANSI(x64)}

If your configuration uses a different driver name (as in the examples of Oracle and DB2 above), you must provide the actual driver name:

  1. Locate or create the connection-string option in the [settings] section.
  2. The value of connection-string is a list of key-value pairs separated by semicolons ( ; ).
    • To specify the actual driver, give it a value of driver=<driver_name>.
    • If your database server is not running on the default port (1433 for MSSQL, 1521 for Oracle and 50000 for DB2), you must also provide the port number by adding port=<actual_port> to the the value of connection-string; for example, driver=DB2 ODBC Driver;port=1234.

DSN

You can also have Interaction Server use a DSN that has been configured in your system. To do this, locate or create the connection-string option in the [settings] section of the DAP (the [logger-settings] section in the Event Logger DAP), and set it to DSN=<name_of_the_dsn>. With this method, the user name and password to connect to the database are taken from the settings on the DB Info tab and do not need to be provided in the DSN properties.

Test the Connection

To test the connection, run the command 

Isql -v [DSN name]

If the proper credentials are not configured in the odbc.ini file, you may have to add them as parameters of this command; for example, 

Isql -v [DSN name] [username] [password]

Related information can be found on the pages linked to this page in the Integrated Capture Points documentation.

This page was last edited on December 12, 2018, at 17:55.
Comments or questions about this documentation? Contact us for support!