topic unable to connect to db using ODBC in Administration and Deployment

unable to connect to db using ODBC

AmitSri — Tue, 12 Dec 2017 12:43:37 GMT

Hi, We have installed sas viya 3.2 and Our database is Denodo. We need to connect to denodo to access the data using ODBC(3rd party driver). We have also installed denodo ODBC drivers on to the sas server. While we try to connect to the db, using the below script from sas studio

cas mySession sessopts=(caslib=casuser timeout=1800 locale="en_US");

libname odbclib odbc noprompt = "server = denodoIP; DRIVER = DenodoODBCDriver; userid = sas_odbc_user;
password = xxxxxxxxxx; database = dbname";

Its throwing an error as below.

ERROR: The ODBC engine cannot be found.

ERROR: Error in the LIBNAME statement.

Could anyone let us know what is causing this error. Also is there any way to check if the ODBC configuration is proper or not. Thanks!!

Re: unable to connect to db using ODBC

alexal — Tue, 12 Dec 2017 13:10:23 GMT

@AmitSri,

Launch the CAS Server Monitor, http://<FQDN>:8777/cas-shared-default-http/tkcas.dsp, and go to "RunTime Environment". What are the current values for ODBCINI & LD_LIBRARY_PATH?

Re: unable to connect to db using ODBC

AmitSri — Tue, 12 Dec 2017 14:36:24 GMT

Hi Alex,

I have checked it. And the values are as follows:

ODBCINI /etc/odbc.ini
ODBCINSTINI /etc/odbcinst.ini

Re: unable to connect to db using ODBC

alexal — Tue, 12 Dec 2017 14:39:07 GMT

@AmitSri,

What about LD_LIBRARY_PATH?

Re: unable to connect to db using ODBC

AmitSri — Tue, 12 Dec 2017 14:45:56 GMT

And here is the LD_LIBRARY path in the attachment. Thanks!!

Re: unable to connect to db using ODBC

alexal — Tue, 12 Dec 2017 14:48:13 GMT

@AmitSri,

I would like to review these files:

/etc/odbc.ini
/etc/odbcinst.ini

Re: unable to connect to db using ODBC

AmitSri — Tue, 12 Dec 2017 14:54:34 GMT

Okay. Please find them here. Thanks!!

Re: unable to connect to db using ODBC

AmitSri — Tue, 12 Dec 2017 14:55:01 GMT

Re: unable to connect to db using ODBC

AmitSri — Wed, 13 Dec 2017 09:14:08 GMT

Hi Alex,

Sorry, Have semt you the wrong files. Please find the correct files here.

Thanks,

Sri

Re: unable to connect to db using ODBC

AmitSri — Wed, 13 Dec 2017 09:14:35 GMT

Re: unable to connect to db using ODBC

alexal — Wed, 13 Dec 2017 13:19:28 GMT

@AmitSri,

Thanks. Please add this logger to /opt/sas/viya/config/etc/cas/default/logconfig.xml:

<logger name="App.cas.tkcastkts">
<level value="trace"/>
</logger>

Restart CAS server, repeat the problem and show me the most recent CAS server log.

Re: unable to connect to db using ODBC

AmitSri — Wed, 13 Dec 2017 13:45:47 GMT

Thanks Alex!!

I have added the logger to the xml. Now, to restart the cas server, Should I setup the cascontroller to run as a service?

Re: unable to connect to db using ODBC

alexal — Wed, 13 Dec 2017 13:49:53 GMT

@AmitSri,

Just use this command:

sudo systemctl restart sas-viya-cascontroller-default

Re: unable to connect to db using ODBC

AmitSri — Wed, 13 Dec 2017 13:58:07 GMT

Okay.. its alraedy a service available at /etc/init.d. So I had restarted it. Will send the log in a while. Thanks!!

Re: unable to connect to db using ODBC

AmitSri — Thu, 14 Dec 2017 06:06:42 GMT

Hi Alex,

Please find the log here. Kindly have a look at it and suggest us. Thanks!!

Re: unable to connect to db using ODBC

AmitSri — Fri, 15 Dec 2017 09:35:58 GMT

Hi Alex, Can I have any help on this please. Thanks!!

Re: unable to connect to db using ODBC

alexal — Fri, 15 Dec 2017 13:24:40 GMT

@AmitSri,

I do not see any debug messages in the log. Have you reproduced the problem?

Re: unable to connect to db using ODBC

Timmy2383 — Fri, 15 Dec 2017 13:32:50 GMT

I thought the NOPROMPT syntax was for Windows environments only? Especially the way you have it here, referring only a driver name an full path (bc in Windows the drivers are automatically registered, but in Unix you need to give the full pah to the odbc driver)

You need to make sure you have set the environment variable for LD_LIBRARY_PATH in sasenv_local if you haven't already

Re: unable to connect to db using ODBC

AmitSri — Fri, 15 Dec 2017 13:39:55 GMT

Yes Alex, I had done that.

Here is the program that I had run.
cas mySession sessopts=(caslib=casuser timeout=1800 locale="en_US");

caslib odbclib datasource=(srctype="odbc" username="sas_odbc_user"
password="xxxxxxxxxxxxxxxxxx"
odbc_dsn="VDP_acme_DSN");

proc casutil;
list files incaslib=odbclib;
run;

And getting error as below:
ERROR: The connection to the data source driver failed.
ERROR: No diagnostic records were returned.
ERROR: Function failed.
ERROR: The action stopped due to errors.

Re: unable to connect to db using ODBC

AmitSri — Fri, 15 Dec 2017 13:55:25 GMT

Is this the one that you are expecting me to share?
cat caslaunch_default_controller_daemon.log
2017-12-13T15:53:41 gspdsv04.aqua.local:0: NOTE: The list of machines in '/opt/sas/viya/home/SASFoundation/../../config/etc/cas/default/cas.hosts' will be ignored because the server is running in SMP mode.
=== CAS gspdsv04.aqua.local:5570 (0) http://gspdsv04.aqua.local:443 ===
[root@gspdsv04 default]# cat caslaunch_default_controller_daemon.err
2017-12-13T19:46:31 gspdsv04.aqua.local:0: ERROR: Could not find the specified session.

Previously I had sent you another log "cas_2017-12-15_gspdsv04_1103.log"