Accessing ODBC data sources from Rocket Software UniVerse and UniData

Contents

• Introduction
• Configuring and testing the ODBC data source
• Connecting to the ODBC data source from the UniVerse SQL Client Interface
• Connect to the ODBC data source from the UniData SQL Client Interface
• Resources

Introduction

Easysoft ODBC drivers enable the 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:

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

• UniData Using the UniBasic SQL Client Interface (BCI) (PDF)
• UniVerse Basic SQL Client Interface Guide (PDF)
• ODBC on Linux and UNIX platforms
• 64-bit ODBC