Postgresql connection unix

[ANLYNG]

We bought SAS/ACCESS for sql and for oracle on unix in order to read data from SQL and oracle databases..

However now we want to connect to a Poistgresql database. Is there a easy way of doing that via these without spending more money on SAS/access modules?  we want to connect via out UNIX server from PC EG. Thanks in advance.

[JuanS_OCS]

Hello @ANLYNG,

my best suggestion would be to change your SAS/ACCESS license to ODBC. THen you can connect to as many databases as they have ODBC drivers. Of course, this would have an impact on your library definitions and you won't have in-database process support, but if you don;t use in-database processes, you won;t lose much.

In any case, please bear in mind that these communities are mostly based on experiences, and most of us are not part of SAS, therefore question related to the licenses are better supported by the SAS team. I would strongly recommend you to contact SAS Technical Support or your account manager at SAS, they will be happy to support your question.

Hope it helps.

Best regards,

Juan

[kcpython]

I'm currently an admin juggling multiple SAS/ACCESS interfaces.  Like JuanS_OCS said, you might want look into SAS/ACCESS for ODBC.  Keep in mind that the driver manager for SAS/ACCESS ODBC can be different than the other SAS/ACCESS interfaces.  For example, I know that SAS/ACCESS for Greenplum and MSSQL use a DataDirect driver manager.  This required special configuration on our part and help from SAS to get these interfaces to work well with our ODBC connections. I don't know for sure what your "sql" and "oracle" use. Probably DataDirect too.  Our SAS/ACCESS for ODBC used unixODBC for the driver manager, but now we have it configured to use DataDirect.

I do see that SAS offers a SAS/ACCESS interface for PostgreSQL. Might want to look into that. But, if you don't need to do in database stuff, then look at that ODBC interface like Juan said. 

[Bob_Deployment]

As a configuration developer for SAS, I've seen less and less ODBC and more SAS/ACCESS. These days, our 9.4 based solutions use SAS/ACCESS to PostgreSQL extensively. It is an excellent product and I personally recommend it. However, if price is your driver, ODBC is more generic.

[Yaswantha001]

Hi @Bob_Deployment: Please request your help on setting up PostgreSQL connection via SAS/ACCESS

 We do have license and Do we need install PostgreSQL client software on our SAS servers?

 If so, What are the configuration files that we need to change in order to setup connection in SAS servers

Just for info - We are on Windows/SAS 9.4M2

[Bob_Deployment]

@Yaswantha001, Since you have the Web Infrastructure platform installed, you should also have SASWebInfrastructureDataBaseJDBCDrivers and SASWebInfrastructurePlatformDataServer. I believe they contain all that you need. If manually standing up a PostgreSQL server is not your cup of tea, then you can use the client software, especially pgAdmin3.exe to add a database and login. Once done, you can use SAS Management Console to setup your metadata. You need to add a SAS Client Connection for your database to the existing server and then create SAS Library metadata to read and write through. Of course you must have installed SAS/ACCESS to Postgres. SAS MC has some very good help pages. A couple of tips for the Library: a) in the advanced options Output tab, set "Preserve column names as in the DBMS to 'No'; b) on the Input/Output tab set  Preserve DBMS table names to 'No'; c) the Database Schema Name is generally 'public'.

To setup access to the database, the easy path is to create a database user group in SAS MC. Add a login Account) to that group using the same Authentication Domain as in your SAS Client Connection. Add the name of the login you created for the database and the password. Make the SAS Administrators group as a member of  your group for metadata access. Add any other external users as defined in metadata as member if they need to have access to the data.

One last bit is that you can right click on your library in the Data Library Manager plug in for SAS MC and have it display the libname statement you need to connect to your database. Quite handy.

This is absolutely the short course. Check with SAS Technical Support for any documentation you can access that may help you with this.

[Yaswantha001]

@Bob_Deployment Thank you very much useful info. But just to highlight you that - We would like to set-up connection to a Third party PostgreSQL Database from our SAS Windows servers by using SAS/ACESS and agree with you on all options from SAS side.

I believe and my assumption -  PostgreSQL which comes along with SAS\SWebInfrastructurePlatform is only for SAS VA purpose only and this is not useful to connect to external PostgreSQL DB's. Please correct me if I am wrong.

By considering above cases - As you suggested, we may install PostgreSQL client software on SAS servers and then set-up the connection. My question here, What is configuration file that we need to modify to update the Database details and port etc ( For example; tnsnames.ora that we do for Oracle or SQL server etc)  as I am not clear on the config file for PostgreSQL 

Thank you. 

[Bob_Deployment]

Postgres is not Oracle. I recently did just what you are suggesting for a product wanting a later version of Postgres than we distribute. In general, there was no real difference between setting up for the external server than for an "internal" server. We had to decide to use the same JDBC jar as we use for the internal installations in order to prevent conflicts since tcServer puts each referenced jar in the same directory for actual use. The additional metadata from what I've already called out is to setup a Server Component in the Server Manager plugin to hook your SAS Client Connection to. In fact, I believe you wind up creating both at once. One caveat on client software, we didn't really use any during configurations or migrations because we expect the user to see to managing third party databases. You may want to copy the client scripts from your Postgres server machine to the SAS machine.

[ANLYNG]

Thanks for the answers. It is a bit surprising that SAS not out-of-the-box reads postgresql as we have enablet APM

and as I know it reads information from an internal postgresql database ?

[Bob_Deployment]

I don't know which products you have licensed. If SAS  Web Infrastructure Platform is the only product using an instance of SAS Web Infrastructure Platform Data Server, as you describe, it may not come with SAS/ACCESS to PostgreSQL. I believe that product accesses its data via direct JDBC. I would have to check the packaging overview for any specific product to see if it comes with ACCESS to PostgreSQL The SAS Solutions that I've been working with do to support DBMS access using SAS Libraries. The SAS/ACCESSS license may have a usage restriction for use with that product only. Tech Support can probably help you interpret your licensing structure.