SAS Viya 3.5 ODBC DB2 connect Tutorial

[N224]

Hi, after so much pain connecting SAS Viya 3.5 to ODBC i wrote a small article how to get it to work.

I strongly recommend using direcotories like :

/sas
/sas/install
/sas/install/clidriver

I’m going to use some variables that you have change for yours like :

DATABASE_NAME – name of your database you want to connect to
DATABASE_LOGIN – name of account used to connect to DB
DATABASE_PASSWORD – password
DATABASE_IP – IP address of server providing db2 database.

Download and unpack db2cli driver for odbc to

/sas/install/clidriver

RHEL comes with unixODBC driver 2.3.1, and even if you want install newer one, yum still containst 2.3.1.

Check this out using command :

odbcinst –version

Check currently used files

odbcinst -j

We have to download newest available version (for today its 2.3.7)

cd /sas/install
wget -c http://www.unixodbc.org/unixODBC-2.3.7.tar.gz
tar xvf unixODBC-2.3.7.tar.gz
cd unixODBC-2.3.7
./configure
make
make install
odbcinst --version
odbc_config –prefix

Now we have to prepare some files

/usr/local/etc/odbcinst.ini

[DB2]
Description = DB2 Driver
Driver = /sas/install/clidriver/lib/libdb2o.so
FileUsage = 1
dontdlclose = 1

/usr/local/etc/odbc.ini

[DB2ODBC]
Driver=DB2
Database=DATABASE_NAME
Protocol=TCPIP
Port=50000
Hostname=DATABASE_IP
UID=DATABASE_LOGIN
PWD=DATABASE_PASSWORD
option=3
charset=UTF8

NO MATTER WHAT CHARSET DOES DB USE, JUST TYPE "CHARSET=UTF8", don't know why - it works 🙂

Now prepare db2 driver

cp /sas/install/clidriver/cfg/db2cli.ini.sample /sas/install/clidriver/cfg/db2cli.ini
cp /sas/install/clidriver/cfg/db2dsdriver.cfg.sample /sas/install/clidriver/cfg/db2dsdriver.cfg

Edit /sas/install/clidriver/cfg/db2cli.ini.sample /sas/install/clidriver/cfg/db2cli.ini

[DB2ODBC]
Driver=DB2
Database=DATABASE_NAME
Protocol=TCPIP
Port=50000
Hostname=DATABASE_IP
UID=DATABASE_LOGIN
PWD=DATABASE_PASSWORD

Edit /sas/install/clidriver/cfg/ db2dsdriver.cfg

Add a new xml to dsncollection

     <dsn alias="DB2ODBC" name=" DB2ODBC " host="DATABASE_IP" port="50000">
         <parameter name="UserID" value="DATABASE_LOGIN "/>
         <parameter name="Password" value="DATABASE_PASSWORD "/>
    </dsn>

And finally add to files :

/opt/sas/spre/home/SASFoundation/bin/sasenv_local

/opt/sas/viya/config/etc/workspaceserver/default/workspaceserver_usermods.sh

/opt/sas/viya/config/etc/cas/default/cas_usermods.settings

/opt/sas/viya/config/etc/sysconfig/compsrv/default/sas-compsrv

export ODBCSYSINI=/usr/local/etc
export ODBCINI=odbc.ini
export ODBCINSTINI=odbcinst.ini
export LD_LIBRARY_PATH=/usr/local/lib:/sas/install/clidriver/lib/:$LD_LIBRARY_PATH
export PATH=/usr/local/lib:$PATH
export DB2_CLI_DRIVER_INSTALL_PATH=/sas/install/clidriver
export LIBPATH=/sas/install/clidriver/lib
export PATH=/usr/local/sbin:$PATH
export PATH=/usr/local/bin:$PATH
export PATH=/sas/install/clidriver/bin:$PATH
export PATH=/sas/install/clidriver/adm:$PATH

Exporting so many envs is quite an overdo but.. it works 😄

enjoy having successful connection to db2

[SASKiwi]

@N224  - Thank you for taking the time write this. It's great when Community users document complicated configuration processes!

[joeFurbee]

Hi @N224,

I second @SASKiwi's comment. This is great info on a non-trivial topic. I think many users will benefit from this. I'd like to inquire if you'd be willing to formalize this post into a Communities Library article? I'd be happy to help you publish it (you've done most of the work already). I would be sure to pass your steps onto a SAS SME, for any additional input (if any). Please let me know if you're interested.

Thanks,

Joe