BookmarkSubscribeRSS Feed
fnajera
Fluorite | Level 6

Hi team,

 

We're configuring SAS/ACCESS to ODBC in a SAS Viya Marketplace deployment, in order to connect to Denodo. However, although we modify the /viya-share/access-clients/odbc/odbc.ini and odbcinst, these changes are not reflected in the platform, even when we restart the environment.  Can you please give us a hand? 

 

We tried to use the JDBC alternative, but it's like if the /data-drivers/jdbc is not mounted correctly in the pods. Because, even when we put the driver in /viya-share/access-clients/jdbc. This is not detected by the platform.

 

It's important to say, that we are able to connect to Denodo from the Jumpserver, the problem comes when we want to make the drivers and odbc.ini files available to the compute and cas pods.

 

NOTE: In the official documentation, we don't have an example for ODBC configuration.

 

Please let us know if you need further information.

Thanks

Fernando.

 

7 REPLIES 7
fnajera
Fluorite | Level 6
Please validate that the files sas-access.properties, and the data-mount-job.yaml files are correctly configured in the marketplace environment, because it seems like it's not working as expected.
priyhat_sas
SAS Employee
Hi, I am looking at the issue and would like more details.
Are you connecting to Denado using SAS/Access to JDBC and the libname is failing? Would you mind sharing the error details?
Thanks,
Priyha
fnajera
Fluorite | Level 6

Hi priyhat,

 

Thank you so much for your reply.

 

We tried the connection using ODBC and JDBC, and in both cases, the process fails. Below details:

 

a) ODBC

 

1.- In the NFS, we identify the following path /viya-share/access-clients. In odbc, we modify the odbc.ini and odbcinst.ini, in order to define the path where the Denodo driver is located.

 

fnajera_0-1720822090358.png

fnajera_1-1720822114337.png

odbcinst.ini

fnajera_2-1720822135777.png

odbc.ini

fnajera_3-1720822249798.png

denodo driver path

fnajera_7-1720822912891.png

 

 

2.- Then we restarted the environment using the process documented in the SAS Viya marketplace page.

 

https://documentation.sas.com/doc/en/viyaakscdc/v_001/viyaakstasks/n08xicw2ywyp24n18938pqkzf9cq.htm

 

3.- However, when we try to use the DNS from odbc.ini or the DRIVER from the odbcinst.ini. These are not found, because the files are not updated.

 

fnajera_4-1720822379524.png

fnajera_5-1720822449608.png

 

4.- As you can see, the values that we defined in the files are not being taken by the application. Even we check these files from sas studio, and we realized that the odbc.ini and the odbcinst.ini, that the compute pod is referencing, have the default values.

 

fnajera_6-1720822628303.png

b) JDBC

 

We put the jdbc driver in /viya-share/access-clients/jdbc, but when we try to use it, referencing to /data-drivers/jdbc inside the pod. It's not finding it

 

fnajera_9-1720823643133.png

 

Please let me know if you need further information.

 

Thank you 

Fernando.

 

 

fnajera
Fluorite | Level 6

Additional information about the environment variables: ODBCHOME, ODBCINI, ODBCINSTINI, THIRD_PARTY_LIB, LD_LIBRARY_PATH, PATH), where THIRD_PARTY_LIB is missing, and the path where my Denodo driver is located, does not appear in the LD_LIBRARY_PATH

 

80 %put %quote(%sysget(ODBCHOME));
/opt/sas/viya/home/lib64/accessclients
81 %put %quote(%sysget(ODBCINI));
/opt/sas/viya/home/lib64/accessclients/odbc.ini
82 %put %quote(%sysget(ODBCINST));
/opt/sas/viya/home/lib64/accessclients/odbcinst.ini
83 %put %quote(%sysget(THIRD_PARTY_LIB));

84 %put %quote(%sysget(LD_LIBRARY_PATH));
/opt/sas/viya/home/lib64:/usr/lib/jvm/java-17-openjdk-17.0.11.0.9-2.el8.x86_64/lib/server:/usr/lib/jvm/java-17-openjdk-17.0.11.0.9-2
.el8.x86_64/lib:/opt/sas/viya/home/SASFoundation/sasexe:/opt/sas/viya/home/lib64/accessclients/lib:/access-clients/oracle:/opt/sas/v
iya/home/lib64/accessclients/vertica/lib64:/opt/sas/viya/home/postgresql12/lib64:/opt/sas/viya/home/commonfiles/home_1720195961/SASF
oundation/sasexe:/opt/sas/viya/home/SASFoundation/utilities/bin
85 %put %quote(%sysget(PATH));
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/opt/sas/viya/home/lib64:/opt/sas/viya/home/lib64/accessclients/oracle/
19.22/client64/bin
86 %put %quote(%sysget(ODBCSYSINI));
/opt/sas/viya/home/lib64/accessclients/

 

In an standard viya deployment, this should be configured in sas-access.properties, maybe you can help us to take a look on this file

 

fnajera_0-1720828476350.png

 

fnajera
Fluorite | Level 6

Regarding JDBC, as you can see, the driver is on the correct path, according also to this doc:

https://communities.sas.com/t5/SAS-Communities-Library/JDBC-Drivers-Deployment-Made-Easier-SAS-Viya-...

 

fnajera_0-1720829451001.png

 

However, if you use this code, it fails, becuase it cannot find the driver in the /data-drivers/jdbc path. Maybe because it is not mounted in the pod. See the same link above.

 

options sastrace="d,,," sastraceloc=saslog nostsuffix ;

libname x JDBC driverclass="com.denodo.vdp.jdbc.Driver"
URL="jdbc:vdb://xxxxx:9996/common_sources" user=xxxxx
password="xxxxxx*";

 

80 options sastrace="d,,," sastraceloc=saslog nostsuffix ;
JDBC: attempting JDBC connection: jdbc:vdb://192.168.2.6:9996/common_sources
/opt/sas/viya/home/SASFoundation/lib/access/hadoop/
/opt/sas/viya/home/SASFoundation/lib/access/hadoop/access-hadoop-hivehelper-2.15.0.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-googledrive-23.0.8669.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-youtubeanalytics-23.0.8669.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-databricks-23.0.8806.0.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-googleanalytics-23.0.8669.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-sparksql-23.0.8705.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-apachehive-23.0.8808.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-facebook-23.0.8669.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-onedrive-23.0.8669.jar
/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-odata-23.0.8669.jar
81
82 libname x JDBC driverclass="com.denodo.vdp.jdbc.Driver"
83 URL="jdbc:vdb://xxxxx:9996/common_sources" user=xxxxxx
84 password=XXXXXXXXXXXXXXXXXXXXXXXX;
ERROR: java.lang.ClassNotFoundException: com.denodo.vdp.jdbc.Driver
ERROR: at java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:445)
ERROR: at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:592)
ERROR: at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:525)
ERROR: at java.base/java.lang.Class.forName0(Native Method)
ERROR: at java.base/java.lang.Class.forName(Class.java:375)
ERROR: at com.sas.access.hadoop.hive.JdbcHelper.getDriver(JdbcHelper.java:476)
ERROR: Error trying to establish connection: java.lang.ClassNotFoundException: com.denodo.vdp.jdbc.Driver
ERROR: Error in the LIBNAME statement.
85
86 /* region: Generated postamble */
87 /* Close ODS destinations */
88 &graphterm; ;*';*";*/;run;quit;
89 quit;run;
90 ods html5 (id=web) close;
91 ods listing close;
92 %if %sysfunc(fileref(_gsfname)) lt 0 %then %do;
93 filename _gsfname clear;
NOTE: Fileref _GSFNAME has been deassigned.
94 %end;
95 %studio_capture_custom_output;
96 /* endregion */
97

priyhat_sas
SAS Employee
Thank you for all the details, looking into this and will get back. Thanks.
fnajera
Fluorite | Level 6
Thanks Priyhat, we're looking forward to hearing from you, in order to finish this configuration.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Discussion stats
  • 7 replies
  • 1319 views
  • 0 likes
  • 2 in conversation