Architecting, installing and maintaining your SAS environment

Configure SQL anywhere ODBC on AIX to connect sybase database

Reply
Occasional Contributor
Posts: 9

Configure SQL anywhere ODBC on AIX to connect sybase database

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

SAS Employee
Posts: 296

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

Posted in reply to Mitesh7394

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 ?

Occasional Contributor
Posts: 9

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

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.

 

SAS Employee
Posts: 296

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

Posted in reply to Mitesh7394

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.

Occasional Contributor
Posts: 9

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

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

SAS Employee
Posts: 296

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

Posted in reply to Mitesh7394

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.

Occasional Contributor
Posts: 9

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

Posted in reply to Mitesh7394

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

 

Occasional Contributor
Posts: 8

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

Posted in reply to Mitesh7394

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.

 

Occasional Contributor
Posts: 8

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

Posted in reply to Nishant2000

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/...

Occasional Contributor
Posts: 9

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

Posted in reply to Nishant2000

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

SAS Employee
Posts: 296

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

Posted in reply to Mitesh7394

What does your libname statement look like ?

Occasional Contributor
Posts: 9

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

Hi Nishant,

 

Please find below lib statement:

 

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

 

Thanks,

Mitesh

SAS Employee
Posts: 296

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

Posted in reply to Mitesh7394

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

 

 

Occasional Contributor
Posts: 9

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

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

SAS Employee
Posts: 296

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

Posted in reply to Mitesh7394

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.

Ask a Question
Discussion stats
  • 14 replies
  • 322 views
  • 0 likes
  • 3 in conversation