topic Re: Configure SQL anywhere ODBC on AIX to connect sybase database in Administration and Deployment

Configure SQL anywhere ODBC on AIX to connect sybase database

Mitesh7394 — Thu, 25 Jan 2018 12:15:29 GMT

Hi All,

I have installed SQL anywhere ODBC 17 driver on my AIX system. I want to connect from SAS to sybase database by creating odbc library. Could you please guide me? I have done below steps-

1. installation of driver

2. create symlink --> i am not sure about this step, for now i have created symlinks of libodbc.so, libdbodbc_r.so and libdbodbc_n.so, like below,

libodbc.so -> libdbodbc17.so
lrwxrwxrwx 1 root system 16 Jan 22 16:45 libdbodbc_r.so -> libdbodbc17_r.so
lrwxrwxrwx 1 root system 16 Jan 22 16:46 libdbodbc_n.so -> libdbodbc17_n.so

3. tested connection on OS,

[sas@xxxx]</local/apps/SAS94/thirdparty> dbping -d -c "uid=xxxxxx;pwd=xxxxxxx;eng=xxxxxx;links=tcpip(host=xxxxxx;port=2638);dbn=xxxxxxx"

SQL Anywhere Server Ping Utility Version 17.0.0.1063

Connected to SQL Anywhere 17.0.8.4043 server "xxxxxx" and database "xxxxxx"

at address xxxxxxx.

Ping database successful.

4. made entry in odbc.ini and odbcinst.ini

5. created ODBC library

6. library testing--> its failing with below error,

ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified
ERROR: Error in the LIBNAME statement.

Could you please tell me post installation steps?

Thanks,

Mitesh

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

nhvdwalt — Thu, 25 Jan 2018 12:36:10 GMT

Have you pointed <<SASHome>>/SASFoundation/9.4/bin/sasenv_local to your odbc.ini file ?

If yes, make sure the spelling of the DSN in your libname matches the entry in the odbc.ini EXACTLY

Are you using an ODBC driver manager too i.e. do you have multiple ODBC drivers on your system ?

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

Mitesh7394 — Thu, 25 Jan 2018 12:48:16 GMT

I have made below entry in sasenv_local from SAS foundation/bin/,

#---------------------------------------------------------------------------------------------------
$ LIBPATH=/local/apps/SAS94/thirdparty/butterfly/lib64:$LIBPATH
$ export LIBPATH

i have made below entry in odbc.ini from data direct folder,

Description = Sybase ODBC Data Source
UID = xxxxx
PWD = xxxxx
Driver = /shared/thirdparty/butterfly/lib64/libodbc.so
ENG = xxxxx
DBN = xxxxx
LINKS = tcpip(host=xxxxx;port=2638)

and below entry in odbcinst.ini,

[SQL Anywhere ODBC]
Driver=/shared/thirdparty/butterfly/lib64/libodbc.so
APILevel=0
ConnectFunctions=YYY
DriverODBCVer=17.0.0.1063
FileUsage=0
HelpRootDirectory=/shared/thirdparty/DataDirect/help
SQLLevel=0

Is i did something wrong here? My SAS does not have sybase license that is the reason i am using ODBC driver to connect sybase. Yes, on my system there are multiple SQL/ODBC drivers are installed.

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

nhvdwalt — Thu, 25 Jan 2018 12:56:51 GMT

You need to set ODBCHOME and ODBCINI as well.

ODBCHOME need to point to the driver's installation directory e.g. /shared/thirdparty/butterfly

ODBCINI needs to point to your odbc.ini file

SAS needs this in order to find the db driver at runtime.

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

Mitesh7394 — Thu, 25 Jan 2018 13:04:39 GMT

could you please tell me which file should i refer to point, and what is the meaning of_r and _n?

libodbc.so
libdbodbc17_r.so
libdbodbc17_n.so

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

nhvdwalt — Thu, 25 Jan 2018 13:09:20 GMT

Just point to the directory those files are in. Probably ../lib64

SAS will automatically look for libodbc.so when it reads the directory.

No idea what the other two are, but not important for now.

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

Mitesh7394 — Thu, 25 Jan 2018 13:16:32 GMT

i have done changes as per your instructions, but still same error-

ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified
ERROR: Error in the LIBNAME statement.

Now my,

odbc.ini-
Description = Sybase ODBC Data Source
UID = xxxxx
PWD = xxxxx
Driver = /shared/thirdparty/butterfly/lib64/libodbc.so---->this path is where from actual driver installation path
ENG = xxxxx
DBN = xxxxx
LINKS = tcpip(host=xxxxxx;port=2638)

odbcinst.ini-

[SQL Anywhere ODBC]
Driver=/shared/thirdparty/DataDirect/lib/libodbc.so---> i have copied my libodbc.so in lib folder of data direct and mentioned same path here now
APILevel=0
ConnectFunctions=YYY
DriverODBCVer=17.0.0.1063
FileUsage=0
HelpRootDirectory=/shared/thirdparty/DataDirect/help
SQLLevel=0

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

Nishant2000 — Mon, 29 Jan 2018 05:48:12 GMT

Hi Mitesh,

I think how you have configured the SQL Anywhere Driver 17 is having some issue.

Can you please share the content of the folder under the client which you have installed?

Also the libodbc.so if not wrong is not the one which is comes with the SQL Driver 17 client ? If I am correct this is ODBC driver, where as you will need to use driver of SQL Anywhere Driver 17.

The files needed for a working SQL Anywhere ODBC driver. These files should be placed in a single directory. The SQL Anywhere installation places them all in the operating-system subdirectory of your SQL Anywhere installation directory (for example, bin32 or bin64).
AIX:

libdbodbc12.so

libdbodbc12_n.so

libdbodm12.so

libdbtasks12.so

libdbicu12.so

libdbicudt12.so

dblg[LL]12.res

So, please check once your installation, as the driver you are pointing to is wrong one.

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

nhvdwalt — Mon, 29 Jan 2018 05:49:50 GMT

What does your libname statement look like ?

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

Nishant2000 — Mon, 29 Jan 2018 06:22:39 GMT

Hi Mitesh,

Please try using DBODBC17.dll which will be under ../SQL Anywhere 17/Bin64 location(location depends on your installation).

Once you have done this then, create DSN entry. You can also follow below link for more details.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbprogramming/configuring-driver-client-deploy.html

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

Mitesh7394 — Mon, 29 Jan 2018 08:34:31 GMT

Hi Nishant,

Please find below lib statement:

LIBNAME SR_93 ODBC DATAsrc=SQLAnywhere_Butterfly SCHEMA=xxxx USER=xxxx PASSWORD="xxxx" ;

Thanks,

Mitesh

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

nhvdwalt — Mon, 29 Jan 2018 08:52:28 GMT

Can you give us the full entry in odbc.ini ? It will start with [ ... ]

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

Mitesh7394 — Mon, 29 Jan 2018 12:08:40 GMT

Hi Nishant,

This time i got error. earlier the code was not working, when i ran code my session was get thrown out of SAS by saying server disconnected-

33 GOPTIONS ACCESSIBLE;
34 LIBNAME SR_93 ODBC DATAsrc=SQLAnywhere_Butterfly SCHEMA=xxxx USER=xxxx
34 ! PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;
ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Specified driver could not be loaded
ERROR: Error in the LIBNAME statement.
35

Thanks,

Mitesh

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

Mitesh7394 — Wed, 31 Jan 2018 14:22:32 GMT

Hi,

PFB

[SQLAnywhere_Butterfly_ini]
Driver=/shared/thirdparty/SQLAnywhere17/lib64/libdbodbc17.so
uid=xxxx
pwd=xxxxr
EngineName=xxxxx
CommLinks=tcpip(host=xxxx.local;port=xxxx)
AutoStop=no
Database=xxxx
AutoPreCommit=yes

Thanks,

Mitesh

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

nhvdwalt — Thu, 01 Feb 2018 06:33:45 GMT

The datasrc=in your libname must match the name you used in odbc.ini

In this case they are different:

LIBNAME: SQLAnywhere_Butterfly

ODBC.INI: SQLAnywhere_Butterfly_ini

It doesn't really matter what you call them, but they must match.

Re: Configure SQL anywhere ODBC on AIX to connect sybase database

mit7 — Mon, 01 Feb 2021 15:27:00 GMT

Here are the installation steps for SQL Anywhere17 ODBC Driver.

https://informetopedia.com/sql-anywhere17-odbc-drive-installation-and-configuration/