About JBailey

JBailey · ‎11-25-2020

Hi @kiranragam , Are you able to connect using a LIBNAME statement that specifies USER= and PASSWORD=? Best wishes, Jeff

JBailey · ‎11-23-2020

Hi @mcook Don't worry about it and welcome to the club. Many of us are members. I join this club at least once a day. Best wishes, Jeff

JBailey · ‎11-19-2020

Hi @mcook Can you show the actual SAS code you used to connect and run the CREATE TABLE statement? The following options will show the exact SQL that SAS is sending to the database: OPTIONS SASTRACE=',,,d' SASTRACELOC=saslog NOSTSUFFIX; Rerun the job and include the resultant SAS log in this thread. If the table does exist, your SAS log will have an error stating that. Good luck, Jeff

JBailey · ‎11-13-2020

Hi @sivaranjani Hopefully, you are using a supported version of Hadoop (Cloudera, AWS EMR, Hortonworks, etc.). For more information on supported Hadoop distributions check out: An Insider's Guide to SAS and Database Support Information The first thing to try is setting the SAS_HADOOP_HIVE1815_WORKAROUND=YES environment variable. You can set environment variables in the !SASROOT/bin/sasenv_local file, in the shell before you invoke SAS, or on the invocation by: $> sas -set SAS_HADOOP_HIVE1815_WORKAROUND YES jobname.sas Your SAS Administrator will likely need to do this, system wide. Best wishes, Jeff

JBailey · ‎11-02-2020

If you are reading this article, you probably have an ORA-3113 error. You have my sympathy because this is one of the toughest Oracle errors to diagnose and solve. ORA-3113 is described as a catch-all error meaning the Oracle developers don't know the actual cause of the problem. You - Dear SAS User - will be told that it is a SAS problem; contact your SAS Admin. This is most unfortunate because you probably are the SAS Administrator. The full message is Oracle ORA-3113: end-of-file on communication channel. The problem isn't that SAS cannot connect to Oracle; the problem is that something (hint - it's not SAS) is causing the connection to drop. The connection is usually dropped after a period of inactivity. Newer versions of Oracle may report a ORA-3135 error; it's the same thing. So, how do you - the SAS User - fix this? You don't. That would be too easy. What you have to do is convince your Oracle DBAs that SAS isn't the problem. It is most likely something with the network or the Oracle instance. This is a difficult thing to do. A SAS communities user recently encountered this issue. Naturally, the DBAs pointed the finger at SAS. This is a difficult situation because the DBAs want absolve themselves of responsibility. Understandable, but not helpful. Fortunately for the Communities Member, a second application reported an ORA-3113 error. So lucky! In this case, the problem was an overworked Oracle server. The CPUs were maxed-out. Oracle was starved for cycles. I have seen this problem many times at SAS. It was almost always a change in a network configuration. It isn't a problem with the connection information because the connection actually works. <-- Point this out to the DBAs;) My favorite ORA-3113 incident happened at a customer site. SAS was reporting an ORA-3113 during a bulk load process. We could move the data using INSERT statements with no problem other than performance. Bulk load died every time. Running the load via SQL*Loader worked. Naturally, fingers were pointed at SAS. It took an embarrassingly long time, but we figured it out. This company had written a utility that would cancel idle Oracle connections. When SAS bulk loads it passes control to SQL*Loader. The SAS connection is idle; the utility killed it. When control returned to SAS - ORA-3113. We added SAS to the utility's exclude list and never encountered the error again. The key take-away is that this is not a SAS issue. Push back on the DBAs, Networking, or Sys Admin folks. Helpful Links https://communities.sas.com/t5/SAS-Programming/Error-ORA-03113-end-of-file-on-communication-channel/td-p/343181 http://www.dba-oracle.com/m_ora_03113_end_of_file_on_communications_channel.htm https://www.tekstream.com/resource-center/ora-03113-end-of-file-on-communication-channel/

JBailey · ‎10-21-2020

Hi @avinashginjupal I think you need to create a new post for this one. It doesn't make sense to piggy-back on a solved post. Include my user id in the post so that I see it. Best wishes, Jeff

JBailey · ‎09-30-2020

Hi @SASUser_86 I think it is Athena (aka Presto) that doesn't like the hyphens. SAS is passing the name to Athena; Athena is returning the error message. In Athena a schema=database. The following text comes from this page: https://docs.aws.amazon.com/athena/latest/ug/glue-best-practices.html (I have highlighted the relevant section. Database, Table, and Column Names When you create schema in AWS Glue to query in Athena, consider the following: A database name cannot be longer than 252 characters. A table name cannot be longer than 255 characters. A column name cannot be longer than 128 characters. The only acceptable characters for database names, table names, and column names are lowercase letters, numbers, and the underscore character. You can use the AWS Glue Catalog Manager to rename columns, but at this time table names and database names cannot be changed using the AWS Glue console. To correct database names, you need to create a new database and copy tables to it (in other words, copy the metadata to a new entity). You can follow a similar process for tables. You can use the AWS Glue SDK or AWS CLI to do this. Best wishes, Jeff

JBailey · ‎09-29-2020

You have two options available: DSN-less ODBC connections - you can read about it here. This is the approach that I used in the post you referenced. The CONOPTS= option. The trick is to look at the documentation for the ODBC driver and find the supported options. These options can be passed via either of these options. The following code uses a DSN-less connection to connect to Athena. The information contained in the COMPLETE= option overrides the odbc.ini information. libname athena odbc complete="DRIVER={Simba Athena ODBC Driver}; AwsRegion=us-east-1; S3OutputLocation=s3://bogus-athena-results/; AuthenticationType=IAM Profile; AwsProfile=617292732428-jbsandbox;" schema=somedb; Here is an example of conopts=. The example shows how to pass the ODBC driver options to the driver. Like the DNS-less connection, CONOPTS= overrides the information in the odbc.ini file. libname athena odbc datasrc=athena conopts="AwsRegion=us-east-1" schema=sas; Check the documentation for the ODBC driver; there may be a way to use environment variables for some of this stuff. Best wishes, Jeff

JBailey · ‎09-25-2020

Hi @yadwindersp89 The problem is that the Linux VM knows nothing about a Windows C:\blah\blah directory. There will be a Linux file system that links to a directory on your Windows laptop. Check out this page: https://support.sas.com/software/products/university-edition/faq/SAS_libname.htm Best wishes, Jeff

JBailey · ‎09-23-2020

Hi @SASUser_86 SAS 9.4M6 willl work fine. SAS/ACCESS Interface to ODBC has been around a long time. No problems there. You didn't mention this, but I will bring it up again because it is common. I know because this has hit me more times than I care to admit. I feel like an idiot EVERY SINGLE TIME. Make sure there are no stray white space characters in your code. I usually remove all white space and try it. Something like this... /* simplified example */ libname athena odbc complete="DRIVER={Simba Athena ODBC Driver};AwsRegion=us-east-1;S3OutputLocation=s3://bogus-athena-results/;AuthenticationType=IAM Profile;AwsProfile=617292732428-jbsandbox;" schema=somedb; I assume that your SAS server is running on Linux. There may be an issue with the ODBC configuration. I know if you have SAS/ACCESS Interface to MS SQL installed, someone must play tricks with the ODBC Driver Manager. It is important to note, everything specified in the CONNECT= option (between the "") comes from the ODBC driver documentation (see link below). The example I used is overly complicated for purposes. I assume that you don't need to use a proxy server. Then again, that could actually be the problem - but, I doubt it. Here is an example that matches what you are trying to do. /* simplified example */ libname athena odbc complete="DRIVER={Simba Athena ODBC Driver}; AwsRegion=us-east-1; S3OutputLocation=s3://bogus-athena-results/; AuthenticationType=IAM Profile; AwsProfile=617292732428-jbsandbox;" schema=somedb; Simba ODBC Driver Documentation When I have issues like this, I usually: Try to connect using a simple ODBC query tool - dbsquirrel, Excel, etc. Put an error (say a bad S3 bucket) in the code to see if the remote source is seeing the connection request. You will know because Athena will complain about the bucket not existing. SAS can run on cloud environments. We have lots of customers doing it. Check out our partnership with Microsoft. Cool stuff. I haven't announced it, but I moved from Product Management to our SAS Cloud group a couple of months ago. The cloud is a lot of fun. I highly recommend spending some quality time with it. This SAS Communities post may help - https://communities.sas.com/t5/Administration-and-Deployment/Connect-SAS-to-AWS-Athena-via-ODBC/td-p/619351 Let me know how it goes, Jeff

JBailey · ‎09-23-2020

Hi @Peter_Wine If Hive (or Impala) can read the file then SAS can read it. SAS/ACCESS Interface to Hadoop has an optimization that can bypass the Hive JDBC driver and read a file directly from HDFS. AVRO is not supported by this bypass feature because SAS cannot directly read this file type. Therefore, you will want to set READ_METHOD=JDBC on your LIBNAME statement. Here is the relevant doc: https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=acreldb&docsetTarget=n1wovp3swlye3nn1xiio7nlkzw3c.htm&locale=en Best wishes, Jeff

JBailey · ‎09-23-2020

Hi @SASUser_86, 20 minutes for a connection is crazy. It should take seconds (actual time depends on networking speeds, etc.). Are there any notes or messages displayed in the SAS log? Are you running SAS in AWS or on-premises? COMPLETE= is very sensitive to white space characters. It may be a good idea to make sure that there are no tabs or spaces in the argument. I posted the following code snippet in another Communities post. It may help you. It is more complicated than what you are trying to do, but it is a good example. /* this is the skeleton that I posted on the communities */ libname athena odbc complete="DRIVER={Simba Athena ODBC Driver}; AwsRegion=us-east-1; S3OutputLocation=s3://bogus-athena-results/; AuthenticationType=IAM Profile; AwsProfile=617292732428-jbsandbox; UseProxy=1; ProxyScheme=HTTP; ProxyHost=111.111.111.111; ProxyPort=80; ProxyUID=ProxyUserID; ProxyPWD=ProxyPassword;" schema=somedb; It has been a while since I played with Athena, but I vaguely remember having an issue if I don't place the trailing "/" on the bucket name. You may want to add it. That being said, if that were the problem the code would return and error quickly. Post the relevant section of your log or let us know that there aren't messages going into the log. Best wishes, Jeff

JBailey · ‎09-17-2020

Hi @shikoulitz You can find this information in the SAS Systems Requirements. I have created a SAS Communities article to help guide people through the process. https://communities.sas.com/t5/SAS-Communities-Library/An-Insider-s-Guide-to-SAS-and-Database-Support-Information/ta-p/597707 Here is the Systems Requirements doc for ZOS. https://support.sas.com/documentation/installcenter/en/ikfdtnmvssr/66198/PDF/default/sreq.pdf The answer to your question is "yes" it is still supported. Not sure about the recommended portion of your question, but if you need to access Oracle from the mainframe I would recommend it. Best wishes, Jeff

JBailey · ‎09-08-2020

Hi @Jazzman I think SAS is complaining about the Character Set because it doesn't match the database. Character Set issues tend to be complicated; it is probably a good idea to contact SAS Tech Support. They may have helped others with this type of thing. Best wishes, Jeff

JBailey · ‎09-08-2020

Hi @NN, We have customers running this on AIX. You don't mention the version of PostgreSQL you are using or the maintenance level of SAS (M<and some number>). This information is important in diagnosing the issue. Make sure that you are using a supported version of PostgreSQL. Here is a screenshot from the SAS Systems Requirements for SAS 9.4 on AIX. Your best bet may be to contact tech support. UPDATE: SAS 9.4M6 requires a Hot Fix (H3J001) to be installed to get access to use BULKUNLOAD=. This SAS Communities article may be of assistance - An Insider's Guide to SAS and Database Support Information. Best wishes, Jeff "Who used to be the product manager for SAS/ACCESS"

Online Status	Offline
Date Last Visited	‎10-29-2025 01:39 PM