
A few weeks ago I helped a customer set up a successful connection between SAS and a remote DB2 database using ODBC. At first, I thought this case would be as simple as plugging in a cable. Why? Because I already had a fully-functional testing environment with direct interaction using SAS/ACCESS Interface to DB2. Besides, I had other RDBMS connected via ODBC. Therefore, it was just a matter of setting up my ODBC driver manager for DB2. No big deal right? Oh boy, was I wrong! It was not easy at all. It took me two full days to make it work in my lab, and only after some in-depth research and a lot of trial and error.
DB2 can be challenging, especially when most of the documentation I found was either incomplete, outdated, scarce or even unclear. I don't want you to undergo the same frustration I felt. As a blogger and SAS user, it is my duty to make your SAS life a little easier. That's why I decided to publish this blog post to share the solution with the community. This guide provides you with the steps to take to set up a successful connection between SAS and a remote DB2 database via ODBC on Linux, without tears.
Table of Contents
Keep in mind this is an extensive guide. I documented all the technical issues I came across in my lab, and also provided detailed explanations to overcome them. If you are beginning your journey, I recommend you to go through all the steps. However, for your convenience, if you want to jump right into a specific topic, use the following table of contents:
My Environment
All commands throughout this guide are based on this table. You should replace the code with your settings when appropriate.
Prerequisites
Before we begin, make sure you meet all of these SAS and DB2 requirements:
System Requirements
It may sound obvious, but you must verify that your SAS release supports your DB2 database version. For instance, My Environment section shows that I'm using SAS 9.4 M4. This document recommends that I need DB2 10.5 or later. Considering I currently have DB2 11.1, I'm ready to go. To review the rest of the combinations visit the SAS System Requirements page.
Another evident must-have is an ODBC driver manager already installed. As you noticed, I use unixODBC version 2.3.1. It is always recommended to have the latest version, which nowadays is 2.3.4. Still, you should be fine if you decide to use any of the 2.3.X releases. In the past, I've seen users facing difficulties with unixODBC 2.2.X.
SAS/ACCESS Interface to ODBC licensed and installed
For this connection to work, it is essential to have SAS/ACCESS Interface to ODBC licensed and installed. First, to verify that this product is licensed, run this code in a SAS session:
proc setinit no alias; run; |
In the output log look for this line:
---SAS/ACCESS Interface to ODBC 06JAN2018 |
If the product is displayed, move on to the next step. If not, look for SAS/ACCESS Interface to DB2 instead. If you have the latter, consider yourself a lucky person. You don't have to continue with this guide because you can connect to DB2 directly. If you need assistance with this off-topic issue, send me a message, and I'll be glad to help you. If you don't have either one, you should get in touch with your sales representative.
Next, to verify that this product is installed, go to your !SASROOT/sasexe
directory on your SAS server and list these files:
[sas@muse sasexe]$ ls -l *odb* -r-xr-xr-x. 1 sas sas 630196 Nov 9 2016 sasiodbu -r-xr-xr-x. 1 sas sas 603114 Nov 9 2016 sasioodb -r-xr-xr-x. 1 sas sas 603114 Nov 9 2016 sasioodb_u -r-xr-xr-x. 1 sas sas 59977 Nov 9 2016 sasodbcb -r-xr-xr-x. 1 sas sas 59977 Nov 9 2016 sasodbcb_u -r-xr-xr-x. 1 sas sas 102142 Nov 9 2016 sasodbcs -r-xr-xr-x. 1 sas sas 71982 Nov 9 2016 sasodbdb -r-xr-xr-x. 1 sas sas 71990 Nov 9 2016 sasodbdb_u -r-xr-xr-x. 1 sas sas 202343 Nov 9 2016 sasodb -r-xr-xr-x. 1 sas sas 201815 Nov 9 2016 sasodb_u -r-xr-xr-x. 1 sas sas 443852 Nov 9 2016 tkeodbc.so |
If you don't see the above list, then the product is not installed. Although, if your SAS license includes it, use your SAS depot to install it on your server.
Choosing the right DB2 client package
IBM offers five different client packages. If you don't know which package to choose, you can't go wrong with IBM Data Server Client, it's the package I use, it includes all the client tools and libraries available. Another good alternative is IBM Data Server Runtime Client, but you will have to create your DB2 instance and user yourself. I would not recommend IBM Data Server Driver for ODBC and CLI. During my research I found that people could not get it to connect to a remote DB2 database. For more information about each client package, please visit IBM data server client and driver types.
Testing your DB2 connection outside of SAS
This test is the most critical requirement before starting with the actual configuration process. Take my advice for any connection you plan to configure in the future. Firstly, you must establish a successful connection between your database client and database server outside of SAS, using your database vendor tools. This validation is a must, regardless of your RDBMS or SAS engine.
To test your connection to your remote DB2 server, run an equivalent command on your DB2 client:
[db2inst@muse ~]$ db2 connect to GSDB user db2inst using mypassword Database Connection Information Database server = DB2/LINUXX8664 11.1.1 SQL authorization ID = DB2INST Local database alias = GSDB |
If you received a similar output you might continue; otherwise, you will have to catalog your DB2 server as a TCP/IP node, and then catalog the database on that node. Since this is beyond the scope of this guide, please review these IBM documents: CATALOG NODE command and CATALOG DATABASE command.
Setting up unixODBC with DB2
Considering that you have tested a successful connection between your database client and database server with the db2
command, it's time to set up a new one using an ODBC driver instead. Please bear with me. Things are about to get interesting.
As mentioned at the beginning, unixODBC is my ODBC driver manager of choice. I use it for all my RDBMS in my lab to test ODBC connections. Its usage is almost effortless, but one of the downsides is the documentation. The only official document available for DB2 is: Using IBM DB2 with unixODBC. Judge for yourself.
This driver manager has two configuration files: odbcinst.ini
for database drivers and odbc.ini
for database definitions. Typically with most databases, those two files are all that it takes to set up an ODBC connection. DB2 plays a different game though. It uses two extra files: db2cli.ini
for DB2 definitions and an XML based file called db2dsdriver.cfg
.
For now, let's take a closer look at the contents of both unixODBC files:
odbcinst.ini
[DB2] Description = IBM DB2 ODBC Driver Driver = /opt/ibm/db2/V11.1/lib32/libdb2.so Driver64 = /opt/ibm/db2/V11.1/lib64/libdb2.so FileUsage = 1 DontDLClose = 1 |
odbc.ini
[db2odbc] Driver = DB2 Description = DB2 ODBC connection for SAS |
At this point I was feeling confident and thought the connection would work, so I tested it out with the isql
command, but to my surprise, I received this error:
[db2inst@muse ~]$ isql -v db2odbc db2inst mypassword [08001][unixODBC][IBM][CLI Driver] SQL1013N The database alias name or database name "DB2ODBC" could not be found. SQLSTATE=42705 [ISQL]ERROR: Could not SQLConnect |
db2cli.ini came to the rescue
As a result, I started looking up online documentation. After a while, I discovered the existence of the db2cli.ini
file. To get you started, IBM provides a sample configuration file called db2cli.ini.sample
, located in the installation_path/cfg
directory. The DB2 documentation recommends creating a db2cli.ini
file based on that sample file and place it in the same location. I followed their advice, and created my file with the same data source name:
[db2odbc] Database = GSDB Protocol = TCPIP Hostname = redhat ServiceName = 50000 |
Note: For IBM, the ServiceName
keyword is the port number. The default port number for the DB2 default instance is 50000. Review the correct port with your DBA.
Having set up both unixODBC and DB2 files correctly, I was feeling lucky. Therefore, I attempted a connection again, but I received the same error. My frustration was on the rise.
db2cli validate tool is your best friend
I continued digging into the documentation until I found a powerful tool: db2cli
with a particular parameter: validate
. This command/parameter duo can help you verify the contents of your configuration files (db2cli.ini
and db2dsdriver.cfg
), list their location, test your data source names and databases, display invalid/duplicate keywords, among other functionalities. It basically is your new best friend.
db2cli validate
helped me identify why the DB2 ODBC driver was unable to find my data source name. I ran this command to examine my environment, and the output was eye-opening:
[db2inst@muse ~]$ db2cli validate -dsn db2odbc =============================================================================== Client information for the current copy: =============================================================================== Client Package Type : IBM Data Server Client Client Version (level/bit): DB2 v11.1.0.0 (s1606081100/64-bit) Client Platform : Linux/X8664 Install/Instance Path : /opt/ibm/db2/V11.1 DB2DSDRIVER_CFG_PATH value: db2dsdriver.cfg Path : /home/db2inst/sqllib/cfg/db2dsdriver.cfg DB2CLIINIPATH value : db2cli.ini Path : /home/db2inst/sqllib/cfg/db2cli.ini db2diag.log Path : /home/db2inst/sqllib/db2dump/db2diag.log =============================================================================== db2dsdriver.cfg schema validation for the entire file: =============================================================================== Note: The validation utility could not find the configuration file db2dsdriver.cfg. The file is searched at "/home/db2inst/sqllib/cfg/db2dsdriver.cfg". =============================================================================== db2cli.ini validation for data source name "db2odbc": =============================================================================== Note: The validation utility could not find the configuration file db2cli.ini. The file is searched at "/home/db2inst/sqllib/cfg/db2cli.ini". =============================================================================== db2dsdriver.cfg validation for data source name "db2odbc": =============================================================================== Note: The validation utility could not find the configuration file db2dsdriver.cfg. The file is searched at "/home/db2inst/sqllib/cfg/db2dsdriver.cfg". =============================================================================== The validation is completed. =============================================================================== |
As you can see, the validation tool didn't find my db2cli.ini
. That file contained my data source name; consequently, the DB2 ODBC driver didn't find it either. As previously indicated, I created that file in the installation_path/cfg
directory, which it was: /opt/ibm/db2/V11.1/cfg
, but the DB2 ODBC driver was searching for it in: /home/db2inst/sqllib/cfg
. I didn't choose that path randomly; I just followed this IBM document. Nevertheless, if you encounter the same issue, the solution is fairly simple. You can either create a copy in the required path or point to the original file using a symbolic link. I chose the latter to avoid having duplicates. To fix this misunderstanding just execute this command as root or sudo:
[root@muse ~]# ln -s /opt/ibm/db2/V11.1/cfg/db2cli.ini /home/db2inst/sqllib/cfg/db2cli.ini |
Now you should have this symbolic link:
[db2inst@muse ~]$ ls -l /home/db2inst/sqllib/cfg/db2cli.ini lrwxrwxrwx. 1 root db2iadm 33 Sep 11 19:07 /home/db2inst/sqllib/cfg/db2cli.ini -> /opt/ibm/db2/V11.1/cfg/db2cli.ini |
After this fix, I used my new friend again, and my environment changed. Below is a partial output:
[db2inst@muse ~]$ db2cli validate -dsn db2odbc =============================================================================== db2cli.ini validation for data source name "db2odbc": =============================================================================== [ Keywords used for the connection ] Keyword Value --------------------------------------------------------------------------- DATABASE GSDB PROTOCOL TCPIP HOSTNAME redhat SERVICENAME 50000 |
At last, the DB2 ODBC driver was able to find the configuration file, read its contents, and validate the keywords. My frustration started to disappear.
It was time to see what unixODBC thought about this change. So I reran my isql
command:
[db2inst@muse ~]$ isql -v db2odbc db2inst mypassword +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> |
It finally worked!
Do we need to use db2dsdriver.cfg?
I managed to set up a successful ODBC connection using only three files: db2cli.ini
, along with odbc.ini
, and odbcinst.ini
. Thus, the answer is no; you don't need to use this additional DB2 configuration file.
For our scenario, I consider it as optional. Previously, I pointed out that it was an XML file, as opposed to db2cli.ini
, which is a text file. Both can have the same four keywords I defined. Then why does DB2 provide two files? db2cli.ini
is used to configure the behavior of CLI/ODBC applications, whereas db2dsdriver.cfg
can be used for those as well, and for other types of applications including PHP, Perl, .NET, among others.
In short, this concludes the DB2 configuration process. Now it's time to cover the SAS territory.
SAS joins the party
So far we haven't touched any SAS files. We have only tested the DB2 connection first using the db2
command, then using unixODBC's isql
command. These are mandatory steps before a successful connection with SAS.
Getting sasenv_local ready for DB2
If you have already connected SAS to other RDBMS in the past, you know that the most important file you need to care about is sasenv_local
. It is located in the !SASROOT/bin
directory. The code below shows a partial output with both ODBC and DB2 variables correctly defined:
# ODBC settings export ODBCSYSINI=/etc # DB2 settings export INSTHOME=/opt/ibm/db2/V11.1 export DB2CLIINIPATH=/opt/ibm/db2/V11.1/cfg export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/lib64:${INSTHOME}/lib64 |
Let's analyze each of the above settings. First, SAS needs to know where to locate both unixODBC configuration files. The ODBCSYSINI
variable instructs SAS to look for them in the /etc
directory.
Second, as a good practice, I defined an environment variable called INSTHOME
to reference my DB2 installation directory. This variable is optional but highly recommended. Then, the DB2CLIINIPATH
variable tells SAS the location of db2cli.ini
. If you decide to use db2dsdriver.cfg
instead, then replace that variable with DB2DSDRIVER_CFG_PATH
.
Finally, you have to inform SAS where to find unixODBC and DB2 libraries. SAS uses the LD_LIBRARY_PATH
environment variable on Linux. This variable points to /usr/lib64
because it's the default path for all unixODBC libraries. I also appended the $INSTHOME/lib64
directory, since it's the place where DB2 has all its libraries.
At this point, you might think that you are all set. I don't like being pessimistic, but there is a high possibility that your connection may not work. I didn't test all SAS and DB2 releases, but my customer and I experienced the same error when we tested our connection with a LIBNAME statement:
[sas@muse 9.4]$ ./sas -nodms 1? libname db2lib odbc user=db2inst password=mypassword datasrc=db2odbc; ERROR: An exception has been encountered. Please contact technical support and provide them with the following traceback information: The SAS task name is [Line Mod] Segmentation Violation # More lines... |
Moreover, if you run this code in SAS Studio or SAS Enterprise Guide, you might not even get a response, SAS will just crash or hang for a long time.
Fixing the Segmentation Violation error
After intense research, I was able to fix this error. We need to link two ODBC libraries that SAS provides to enable 32-bit capability. Change to your !SASROOT/sasexe
directory and backup both files:
[sas@muse sasexe]$ mv sasodb sasodb.orig [sas@muse sasexe]$ mv sasioodb sasioodb.orig |
Create two symbolic links that point to the 32-bit libraries:
[sas@muse sasexe]$ ln -s sasodb_u sasodb [sas@muse sasexe]$ ln -s sasioodb_u sasioodb |
List your files, and the output should resemble the following:
[sas@muse sasexe]$ ls -l *odb* -r-xr-xr-x. 1 sas sas 630196 Nov 9 2016 sasiodbu lrwxrwxrwx. 1 sas sas 10 Sep 11 21:51 sasioodb -> sasioodb_u -r-xr-xr-x. 1 sas sas 603114 Nov 9 2016 sasioodb.orig -r-xr-xr-x. 1 sas sas 603114 Nov 9 2016 sasioodb_u lrwxrwxrwx. 1 sas sas 8 Sep 11 21:52 sasodb -> sasodb_u -r-xr-xr-x. 1 sas sas 59977 Nov 9 2016 sasodbcb -r-xr-xr-x. 1 sas sas 59977 Nov 9 2016 sasodbcb_u -r-xr-xr-x. 1 sas sas 102142 Nov 9 2016 sasodbcs -r-xr-xr-x. 1 sas sas 71982 Nov 9 2016 sasodbdb -r-xr-xr-x. 1 sas sas 71990 Nov 9 2016 sasodbdb_u -r-xr-xr-x. 1 sas sas 202343 Nov 9 2016 sasodb.orig -r-xr-xr-x. 1 sas sas 201815 Nov 9 2016 sasodb_u -r-xr-xr-x. 1 sas sas 443852 Nov 9 2016 tkeodbc.so |
Now your LIBNAME
statement may work, but unlike my customer, you can also encounter this error:
1? libname db2lib odbc user=db2inst password=mypassword datasrc=db2odbc; ERROR: Could not load /sas/Software/SASFoundation/9.4/sasexe/sasodb (35 images loaded) ERROR: libodbc.so.1: cannot open shared object file: No such file or directory ERROR: The SAS/ACCESS Interface to ODBC cannot be loaded. The SASODB code appendage could not be loaded. ERROR: Error in the LIBNAME statement. |
The error is self-explanatory. SAS couldn't find the file libodbc.so.1
, which SASODB needs. To display all the required shared libraries, execute the ldd
command:
[sas@muse sasexe]$ ldd sasodb linux-vdso.so.1 => (0x00007ffd4efe2000) libodbc.so.1 => not found libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f9aa741d000) libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f9aa71e6000) libdl.so.2 => /lib64/libdl.so.2 (0x00007f9aa6fe2000) libm.so.6 => /lib64/libm.so.6 (0x00007f9aa6cdf000) libc.so.6 => /lib64/libc.so.6 (0x00007f9aa691e000) libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f9aa6708000) /lib64/ld-linux-x86-64.so.2 (0x00007f9aa7882000) libfreebl3.so => /lib64/libfreebl3.so (0x00007f9aa6504000) |
Indeed, that library was not found, so to fix this missing dependency, create a symbolic link using root or sudo:
[root@muse ~]# ln -s /lib64/libodbc.so.2.0.0 /lib64/libodbc.so.1 |
Creating a library that works
Now that the previous errors are gone, run a final test using the SAS command line to assign a library and then SAS Studio to display some data in a nice-looking way:
[sas@muse 9.4]$ ./sas -nodms 1? libname db2lib odbc user=db2inst password=mypassword datasrc=db2odbc; NOTE: Libref DB2LIB was successfully assigned as follows: Engine: ODBC Physical Name: db2odbc |
Note: Displaying actual data is an essential last step because if you are pointing to an incorrect DB2 ODBC driver (such as libdb2o.so
instead of libdb2.so
), you can still assign a library successfully, but you won't see any tables.
In SAS Studio I assigned the same library and ran a small query to retrieve some data:
proc print data=db2lib.department_lookup; var department_code department_en; run; |
Wrapping Up
I know it's a lot to digest. However, some users may not need to follow all steps to succeed, whereas others starting from scratch may require performing all steps carefully. Whatever your situation might be, I hope you find this guide useful and easy to follow. Did you struggle like me? Share your experience in the comments below or if you have any problems, don't hesitate to contact me.
10 Comments
Thank you for your very useful and well written article.
Roberto
This is such an amazing comprehensive tutorial. Worked like charm... Thank you Roberto!
I ended up opening a track 7612780593 , My problem is funny. I can see the list of tables when I run proc datasets but cannot query the tables listed.
can someone help me please.
I can be reached on amar.dombe@sas.com +91 9820348038
Hi Amar, from your description, it seems that you have ReadMetadata permission granted, but Read permission denied. Check these authorizations access for the library and the tables.
Any DB2 limitations with this method, including schema naming length?
Hi, Roberto!
First of all thanks for such useful article. It has appeared just in right time when I was in need to setup db2 connection via ODBC.
Why you are not using libdb2o.so driver? In my case it prevented me from segmentation error and, thus, from making links to *_u files.
You might find interesting this SAS Note.
My odbcinst.ini looks like this
[db2_odbc_driver]
Description=IBM DB2 ODBC driver
Driver=/opt/IBM/db2/db2inst1/sqllib/lib64/libdb2o.so
FileUsage=1
DontDlClose=1
odbc.ini like this
[RUDMLIB]
Driver=/opt/IBM/db2/db2inst1/sqllib/lib64/libdb2o.so
Database=RUDMLIB
Description=DB2 ODBC connection for SAS
Works like a charm.
Hi Aleksey,
Thanks for your feedback. I did not use the
libdb2o.so
driver because my customer was using it and at the final step even though we were able to run a LIBNAME statement successfully, there were no tables at all. After digging into his files, I noticed we were using different drivers, so I changed it to replicate my own configuration and he was able to display his tables.I came across that SAS note when I was doing my research, but I did not try it in my lab. Did you test the
libdb2.so
driver? Can you provide the details of your environment (SAS, OS, DB2, etc)?Regards,
Roberto
Very nice indeed. What is more weird as to other DB's IBM has a DB2 product on the Mainframe
- https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/home/src/tpc/db2z_12_prodhome.html at level 12
- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzatd/rzatdprintable.htm at level 7.3
the odbc client is supporting all of those.l
- https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/c0007944.html?pos=2
Recently in an environment getting thos Java abends wiht a complete crash of the running SAS process.
By adding the same things als in the SSH environment into the SAS scripting some things improved.
Still looking for (sas/access db2):
- retrieving underpinned the "current timestamp" not the datetime() predefined value.
- unicode lating settings as getting the well known weird characters
- buffering as it is relative slow as seen to ohter systems
- a good description what is going on wiht space and NULL (those are note the same)
- Getting the blukload really working instead of some record by record processing
- dbcommit insertbuf etc.
Really annying that a good description by SAS is missing in this case, very unusable.
Thank you, Roberto, for this very well written tutorial.
Thank you, Leonid, for the kind words. I hope this guide is useful for all our SAS users who are struggling with DB2.