Migrating to ODBC from DB Server (8.5.3 Only)

Starting with release 8.5.3, you must configure Interaction Server to use ODBC database connections. Support for DB Server is deprecated and existing environments must migrate to using ODBC connections.

This page explains how to migrate existing DB Server connections to use ODBC connections.

Important

This page does not provide complete, step-by-step instructions. You must understand such concepts as DSN, ODBC, basing database configuration, and ODBC driver configuration before proceeding with migration.

Important note

Some technical details on the following pages are obsolete and do not apply to Interaction Server 8.5.3:

• Deploying Interaction Server in this guide
• ODBC Connection in this guide
• ODBC Drivers in the eServices Integrated Capture Points Guide

In particular:

• Support of 32-bit platforms was discontinued in the 8.1.3 release. Interaction Server requires a 64-bit operating system and a 64-bit version of the ODBC driver.
• Support of the IBM DB2 database was discontinued in the 8.5.2 release.
• Support of the Solaris and IBM AIX platforms was discontinued in the 8.5.3 release.
• Database vendors have released new ODBC drivers with simplified installation processes.

Installing ODBC drivers

Important

• As Interaction Server is a 64-bit application, the ODBC driver must support 64-bit architecture.
• Installation of ODBC drivers might require administrator-level account privileges.
• Interaction Server does not support alternative ODBC drivers and tools from third-party brands, unless they are explicitly listed in this document.

You must ensure that ODBC drivers are installed on each Interaction Server machine in your environment (primary and backup).

• For Microsoft SQL Server, Genesys recommends the most recent 17.x driver release. See one of the following pages for more information: Windows or Linux.
• For Oracle, Genesys recommends the most recent 18.x driver release. See one of the following pages for more information: Windows or Linux.
• For PostgreSQL, Genesys recommends the most recent 10.x driver release. See one of the following pages for more information: Windows or Linux.

It is recommended to configure the Data Source Name (DSN) for the hosts on which Interaction Server will run (see the DSN article in Wikipedia for more information). Follow the vendor instructions to install the ODBC driver and configure the DSN. Verify a successful connection to database with the tools that accompany the driver.

Configuring the DSN

You can use the ODBC Data Source Administrator in Windows to create driver-specific DSN objects:

1. Open a command prompt.
2. Enter the command odbcad32 to open the ODBC Data Source Administrator.
3. Select the Drivers tab to view the ODBC drivers installed in your environment. Note the names of the available drivers.

   

4. Switch to the tab for DSN creation:
   • If Interaction Server is started as a console application or by LCA (Local Control Agent), you must use the tab User DSN .
   • If Interaction Server is started as a service by the SYSTEM account on Windows, you must use the tab System DSN.

   

5. Select Add and follow the vendor instructions to configure the DSN, specific to your database engine and the selected driver.

Important

For Oracle, ensure you check Enable LOBs in the Oracle tab.

Preparing the database

Before starting the migration, you must ensure the Interaction Server database uses the latest version of the database schema.

For Oracle databases, you must ensure that the field flexible_properties is of datatype BLOB. You must also convert your old data, if necessary.

Using the BLOB datatype on Linux

If you are using Linux, add the line Lobs=T in the odbc.ini file configured for the Interaction Server host.

Converting existing data

Perform the following steps to convert the flexible_properties column to the BLOB datatype:

1. Stop Interaction Server.
2. Convert the flexible_properties column datatype by executing the following script:

   alter table interactions modify (flexible_properties blob default null);

3. Start Interaction Server.

Important

You must ensure that the preconditions mentioned in the Oracle documentation are met. Databases that are created with default scripts from Genesys should meet these preconditions, but it's recommend that customers also verify these preconditions are met.

Preparing DAP configuration objects

New environment

For a new environment, create a Database Access Point (DAP) object as described in the Framework Database Connectivity Reference Guide and add the DAP to the connections of your Interaction Server.

To configure the database-oriented Event Logger for Interaction Server, create a second DAP object as described on the Deploying Event Logger page.

Existing environment

For an existing environment, use Genesys Administration Extension (GAX) to create copies of the existing DAP objects used by Interaction Server. You can use these copies to switch back to connecting via DB Server while testing.

Updating configuration objects to use ODBC

In the DAP object that defines the connection to the Interaction Server main database:

1. Create or change the option dbprotocol with the value odbc.
2. Create or change the option odbc-string to one of the following values:
   • If you have created a DSN object for the main database, enter the value "DSN=name_of_DSN".
   • Otherwise, enter "Driver={name_of_driver}".

In the sample picture below, the respective values are:

• DSN=ci-vm410
• Driver={ODBC Driver 17 for SQL Server}

For the main database, all options should reside in the settings section. For the event log database, all options should reside in the logger-settings section.

The steps are similar to set up the DAP object for the Event Logger database:

1. Create or change the option delivery-protocol to the value odbc.
2. Create or change the option odbc-string to one of the following values:
   • If you have created a DSN object for the Event Logger database, enter the value "DSN=name_of_DSN_for_Logger".
   • Otherwise, enter "Driver={name_of_driver}".

If you are using the same location for the main database and the Event Logger database, the values of these options might be identical. However, for performance or other reasons, you might have the Interaction Server main database and the Event Logger database installed in different locations.

Important

• Genesys recommends that you do not use different database engine types simultaneously. For example, do not use Oracle for the main database and Microsoft SQL Server for the Event Logger database.
• Do not use curly brackets ({ or }) around DSN names. Curly brackets can only be used around driver names.

Additional information for Linux

If Interaction Server is running on Linux, the value for the option odbc-string in DAP objects might depend on where the driver's files were installed. For example, if you are not using a DSN, the driver name might be the actual location of the file, such as in the examples below:

• Microsoft SQL Server: Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
• Oracle: Driver=/usr/lib/oracle/18.3/client64/lib/libsqora.so.18.1
• PostgreSQL: Driver=/home/username/pgodbc/psqlodbc-version/lib/psqlodbcw.so

To use a DSN, Linux users can install the ODBC manager unixODBC, an open-source third-party application. Database and ODBC driver vendors state information about compatibility between unixODBC and driver versions in their installation instructions.

Tip

Refer to the unixODBC User Manual for instructions on how to test an ODBC connection using the isql tool.

Testing the ODBC connection with Interaction Server

Perform the following steps to test the ODBC connection:

1. Configure Interaction Server to collect the log into a text file with debug-level details. See the Options Reference for more information on available options.
2. Start Interaction Server and let it run to generate some events for the log.
3. Stop Interaction Server.
4. Open the log and review all parts that include the string odbc or the names of associated DAP objects. In the case of a successful ODBC connection, the log contains the following messages:
   • Connection to main database:

     (before 8.5.303.08)

     Std 27126 New database connection opened (connection ID: 1)
     Std 27113 Checking database integrity (connection ID: 1)

   (since 8.5.303.08)

   Std 27128 New database connection opened (connection ID: 1, host: OracleDB)
   Std 27113 Checking database integrity (connection ID: 1)

   • Connection to Event Logger database:

     Std 27126 New database connection opened (connection ID: 100001)

     Important

     • The connection ID number 100001 indicates a connection to the Event Logger database.
     • Interaction Server might open more than one connection to the database when necessary and as directed by the values of the option number-of-database-connections. Subsequent connection ID numbers are incremented accordingly.
     • The number of database connections to the Event Logger database can be specified in the number-of-database-connections option in the setting section of the Event Logger.

Troubleshooting a failed connection

See below for quick troubleshooting hints for first-time connections. Make the necessary changes on the database engine side or in the DAP object.

All databases

• On Linux, you might need to have the location of the ODBC driver listed in the environment variable LD_LIBRARY_PATH. This also applies to the unixODBC libraries location.

Microsoft SQL Server

• Ensure the instance name is correct.

Oracle

• Ensure the SID or ServiceName values are correct, and check the listener resolution in the file tnsnames.ora.
• Ensure the setting of the environment variable TNS_ADMIN points to the folder where the file tnsnames.ora is located.
• On Linux, the driver might require the following environment variables to be set:

  export NLS_NUMERIC_CHARACTERS=""

  export NLS_LANG=".UTF8"

  export ORACLE_SID=<ORCL_actual_name>

PostgreSQL

• Ensure that permissions to connect from a remote host are managed in the configuration file(s) pg_hba.conf.