June 20, 2018

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

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:WindowsSysWOW64SQLServerManager12.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.