Accessing ODBC data sources from IBM UniVerse and UniData

Contents

Introduction

Easysoft ODBC drivers enable the IBM U2 data servers, UniVerse and UniData, to access databases such as SQL Server, Oracle and Access from UNIX and Linux platforms.

Important Before downloading your Easysoft ODBC driver, refer to these notes:

Issue Notes
64-bit UniVerse 11 64-bit versions of UniVerse 11 need a 64-bit ODBC driver that is built with 64-bit SQLLEN and SQLULEN types. The SQL Server ODBC driver and Oracle ODBC driver that are available on the Easysoft web site are built with 64-bit SQLLEN and SQLULEN types and are therefore compatible with UniVerse 11.
64-bit UniVerse 10 and earlier 64-bit versions of UniVerse 10 and earlier need a 64-bit ODBC driver that's built with 32-bit SQLLEN and SQLULEN types. The SQL Server ODBC driver and Oracle ODBC driver that are available on the Easysoft web site are built with 64-bit SQLLEN and SQLULEN types. If you're using a 64-bit version of UniVerse, you need to contact the Easysoft support team () for a 32-bit SQLLEN or SQLULEN ODBC driver. Supply us with this information:
  • The version of UniVerse you're running.
  • The platform you're running UniVerse on.
  • Whether you're connecting to SQL Server or Oracle.

Install and license the Easysoft ODBC driver on the machine where UniVerse and UniData are installed. For installation instructions, refer to the documentation for your Easysoft ODBC driver. Refer to the documentation to find out which environment variables you need to set.

Configuring and testing the ODBC data source

  1. Create an Easysoft ODBC driver data source in the /etc/odbc.ini file and test it with unixODBC's isql command.

    For example, the following Easysoft SQL Server ODBC driver data source connects to a remote SQL Server instance:

    [mssql-dsn]
Driver                  = Easysoft ODBC-SQL Server
Server                  = my_machine\my_instance
User                    = my_domain\my_user
Password                = my_password
# If the database you want to connect to is the default
# for the SQL Server login, omit this attribute
Database                = AdventureWorks
  2. Use isql to test the new data source. For example: 
    cd /usr/local/easysoft/unixODBC/bin
./isql -v mssql-dsn

    At the prompt, enter help to display a list of tables. To exit, press Return in an empty prompt line.

  3. If your UniVerse and UniData platform is AIX, you need to extract the Driver Manager shared object and set permissions on the top-level easysoft directory: 
    cd /usr/local/easysoft/unixODBC/lib
ar -X32_64 -xv libodbc.a
mv libodbc.so.1 libodbc.so
chgrp -R GRPNAME /usr/local/easysoft
chmod -R 77n /usr/local/easysoft

    where:

    • GRPNAME is one of the groups UniVerse or UniData is a member of.
    • n is the required permission at world level, usually 5.

Connecting to the ODBC data source from the UniVerse SQL Client Interface

  1. Shut down UniVerse.
  2. Run the relink.uvlibs script so that UniVerse uses the unixODBC shared objects: 
    cd $uvhome/bin
relink.uvlibs /usr/local/easysoft/unixODBC/lib
  3. Start UniVerse.
  4. To access an ODBC data source with the CONNECT command (as shown later in this tutorial), you need to add a data source specification to $uvhome/uvodbc.config (this file may also be located in /etc): 
    <data_source>
DBMSTYPE = ODBC

    where data_source is the name of your Easysoft ODBC data source. For example:

    <mssql-dsn>
DBMSTYPE = ODBC
  5. At the UniVerse prompt, use the CONNECT command to access the ODBC data source: 
    cd $uvhome
uv
CONNECT data_source

    where data_source is the name of your Easysoft ODBC driver data source. For example:

    CONNECT mssql-dsn
  6. When prompted, enter a valid database user name and password.
  7. At the data source prompt, you can execute any valid SQL statement. To exit CONNECT, enter .Q.

Connect to the ODBC data source from the UniData SQL Client Interface

  1. Run the relink.udlibs script so that UniData uses the unixODBC shared objects: 
    cd $udthome/bin
relink.udlibs /usr/local/easysoft/unixODBC/lib

    Note You do not need to restart UniData to use the new shared objects. However, users currently running a UniData session continue to use the old shared objects until they exit and re-enter their UniData session.

  2. Access the UniData demo account: 
    cd $udthome/demo
udt
  3. At the ECL prompt, use the CONNECT command to access the ODBC data source: 
    CONNECT data_source

    where data_source is the name of your Easysoft ODBC driver data source. For example:

    CONNECT mssql-dsn
  4. When prompted, enter a valid database user name and password.
  5. At the data source prompt, you can execute any valid SQL statement. To exit CONNECT, enter .Q.

Appendix A: Resources