UniVerse External Database Access (EDA) with Microsoft ODBC Driver 17 for Linux

Overview

External Database Access (EDA) enables you to convert data stored in a Rocket UniVerse and UniData database to a first normal form (1NF) database, such as Microsoft SQL Server, then access that data using existing UniVerse BASIC programs, RetrieVe, or UniData/UniVerse SQL. For UniVerse running on a Linux platform, previously you had to purchase an ODBC driver to work with the EDA solution. Now, Microsoft provides you with ODBC Driver 17 for SQL Server on all Linux platforms. It is free and easy to install and use. UniVerse supports it starting with UV 11.3.1.6018 or later.

In this blog, you will learn how to work with the UniVerse EDA solution with Microsoft ODBC Driver 17 for SQL Server on Linux. I’ll cover the sample steps that will convert the UniVerse CUSTOMER file data into SQL Server tables.

Note

You must order an additional UniVerse EDA package license to work on this solution. You can verify the EDA license information using the “uvregen -z” command.

Requirements

Rocket MultiValue products Version
UniVerse 11.3.1.6018 (4/26/2018) or later
UniVerse EDA license Yes
EDA Schema Manager 4.33.1

(U2 DB Tools – 4.3.0.2720)

 

Microsoft Version
SQL Server or SQL Server Express 2012 or higher

SQL Server Configuration Setting

When using Microsoft ODBC Driver 17 for Linux, you must set the SQL Server authentication option to “SQL Server and Windows Authentication mode”. You can change the option by using the Microsoft SQL Server Management Studio Tool.

You must set up the SQL Server to a fixed port like 1433; you cannot use TCP Dynamic Ports via the SQL Server Configuration Manager Tool. You can run this tool by using the “C:\Windows\SysWOW64\SQLServerManager12.msc” command for SQL Server 2014.

For different SQL Server versions, the path is different. Please see Microsoft documentation via this web link – https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager?view=sql-server-2017

Start the SQL Server Configuration Manager Tool to set up the TCP/IP port for either the SQLSERVER or SQLEXPRESS version.

Right-click on the “TCP/IP” protocol option to change the TCP Port number to 1433 or a different port number.

 

 

 

 

 

 

 

 

 

 

 

 

 

Installing the Microsoft ODBC Driver 17 for SQL Server on Linux

Microsoft ODBC Driver 17 for SQL Server can work with RedHat Enterprise Server 6 & 7 and other Linux platforms.  Here is the sample script file to install Microsoft ODBC Driver 17.

sudo su
#RedHat Enterprise Server 6
curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo

#RedHat Enterprise Server 7
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

exit
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install msodbcsql17
# optional: for unixODBC development headers
sudo yum install unixODBC-devel

Please see more installation information on the Microsoft web site via the following link – https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

The Linux yum commands will install the Microsoft libmsodbcsql-17.1.so.0.1 driver in the “/opt/microsoft/msodbcsql17/lib64” folder. It will also install the UNIX ODBC driver manager tool (libodbc.so.2) in the “/usr/lib64” folder. For the UniVerse EDA driver, it will only look up the libodbc.so file. You must copy the “/usr/lib64/libodbc.so.2” to “/usr/lib64/libodbc.so”, if the libodbc.so file does not exist in the “/usr/lib64” folder.

The odbcinst.ini file will be created in the /etc folder.

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1
UsageCount=1

However, the odbc.ini file must be created manually to find your SQL Server DSN setting.

Here are two sample DSN names in the /etc/odbc.ini file that work with SQL Server and SQL Server EXPRESS. If you work with SQL Server and Express versions on the same machine, you must change one port number to a different port, e.g. 1435.

[sqlserver]
Driver=ODBC Driver 17 for SQL Server
Description=SQL Server DSN created during installation
Server=x.x.x.x
UID=xxx
PWD=xxx
Database=sample_database

[sqlexpress]
Driver=ODBC Driver 17 for SQL Server
Description=SQL Server DSN created during installation
Server=y.y.y.y\\SQLEXPRESS,1433
UID=xxx
PWD=xxx
Database=sample_database

After an ODBC data source name (DSN) has been created in the /etc/odbc.ini file, you can use the “isql” command to verify the DSN setting.

Syntax
isql –v DSN [UID [PWD]] [options]
[root@ etc]# isql -v sqlserve xxx xxx
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

If the “isql” command does not provide any meaningful error message, you can turn on the UNIX ODBC driver manager log. The logging setting can be added to the /etc/odbcinst.ini file.

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1
UsageCount=1

[ODBC]
Trace = Yes
TraceFile = /tmp/unixodbc.log
ForceTrace = Yes
Pooling = No

On UniVerse, the EDA driver supports different ODBC drivers for Linux platform. It requires you to run the edasetup.sh script to configure the EDA driver for Microsoft ODBC that will save the Microsoft ODBC driver setting to the edaconfig file in the UVHOME folder.

[root@den-vm-t14 uv1131]# bin/edasetup.sh
Please input DRIVER (DB2, ORACLE, ODBC):
ODBC
Please input DRIVERNAME (EASYSOFT or MSODBC):
MSODBC
Please input ODBCDRVPATH (/usr/lib64):

/disk1/uv1131/edaconfig has been updated.

[root@den-vm-t14 uv1131]# more edaconfig
DRIVER=ODBC
DRIVERNAME=MSODBC
ODBCDRVPATH=/usr/lib64
LOGLEVEL=0

Note: The ODBCDRVPATH setting is not used on the current UniVerse release.

Start the EDA Schema Manager Tool to create a new EDA data source with a DSN defined in the /etc/odbc.ini file as shown in the previous step. For the UniVerse EDA Data Source, the driver option is “COM-1NF” for Microsoft ODBC for Linux.

Right-click on the UniVerse CUSTOMER file to create a new CUSTOMER schema with default mapping.

When you convert the U2 CUSTOMER file to an EDA file, it will create CUSTOMER.CUSTOMER and CUSTOMER.ORDERS_MV tables in SQL Server. You can use the Microsoft SQL Server Management Studio Tool to verify that two tables were created.

Paul Chang 5 Posts

Paul Chang is a Senior Technical Support Engineer at Rocket Software. He is responsible for supporting all U2 common middleware client products, Java products and .NET clients. He has 35 years of experience in computing and holds a master’s degree in Computer Science with an emphasis on Relational Database Design.

4 Comments

  • Jeff Yan Reply

    October 5, 2018 at 10:40 am

    Thanks Paul for the great details. I’m trying to implement EDA in our company and have followed your steps. Unfortunately at the step where I set up the EDA Data connection in the EDA Schema Manager and run the test, it fails with the error message below:
    Failed to make connection to “Sqlserver1” using “TestEDA” on server “xx.xx.xx.xx”.
    Error message In /usr/ud81/sys/CTLG/e/EDAMAPSUB at line 1666 EDA COM Driver: Alloc environment handle error.

    My installation: Unidata ver 8.1 on Redhat Linux enterprise server 7. I’ve installed the MS Linux odbc driver. Our SQL server is on Windows and have the correct settings.

    I am able to use the isql tool to connect from the Linux server where Unidata is installed and select data from a test database using the same user credentials set up in the Linux server.

    Using the Schema Manager, I can create a new schema from our files, but when I try to verify the schema, it returns a failed error message: Map Schema HSFMEMBER_2 failed on lex check.

    Appreciate if you could give me some suggestions as I’ve not been able to find any resources on the Rocket site that could point to this problem.

    Thanks

    Jeff

  • Paul Reply

    October 11, 2018 at 2:01 pm

    Before you can create a new schema, you need to test the EDA Source name connection first. On Linux, the ODBC driver manager is called libodbc.so that should reside in the /usr/lib64 folder. Some ODBC installation might use the different name like libodbc.so.2.0.0 file. You can use the “ln -s libodbc.so.2.0.0 libodbc.so” to create a new symbolic link.

    In the odbcinst.ini file, you can add the logging information for the Microsoft ODBC log.

    [ODBC]
    Trace = Yes
    TraceFile = /tmp/unixodbc.log
    ForceTrace = Yes
    Pooling = No

    If there is nothing in the /tmp/unixodbc.log log file, the ODBC driver is not called during the EDA checking process. There is another EDA driver log that could be set in the /usr/ud81/include/edaconfigle file.

    DRIVER=ODBC
    DRIVERNAME=MSODBC
    ODBCDRVPATH=/usr/lib64
    LOGLEVEL=8

  • Noel Reply

    October 17, 2018 at 11:27 pm

    Hi Paul,
    If we are trying to implement a data warehouse solution, do you have any tricks or tools around the need to extract changes only (net change) from UniVerse hashed files ?
    Thanks !

  • Paul Chang Reply

    October 18, 2018 at 7:56 am

    Hi Noel,
    EDA solution is not same as Microsoft Data Transformation Services (DTS). We store UniVerse hashed files data on remote SQL Server database. When you update the UniVerse EDA file (same UniVerse hashed file name) using command or basic program, it will update the SQL Server table immediately. There is no additional step to keep data sync between UniVerse and SQL Server.

Leave a Comment

Your email address will not be published. Required fields are marked *