Best practices in connecting SAS EG to external Oracle databases

[SAS_User5]

We will be connecting up to 60+ Oracle schemas using EG 7.1.

What is the best practice for managing the libraries, db connections and passwords etc..?

We have used DSN connections in the past but looking for a better solution. 

DSN Connection:

libname myoralib odbc dsn=TST user=TestUser password=TestPswd schema='XXXX_LOCK';

[rogerjdeangelis]

If your users are mostly programmers and you have de-identified data non live transactional data, you might ask them which schemas t each programmer would need. If the schemas are less than say a terabyte you might want to transfer the schema data to a $30 terabyte drive as SAS datsets and give them out to you programmers with power SAS workstations.

Also you might want to ask your programmers if they would rather access the data from the local workstation or through the server.

Benchmarks comparing peak throughput, comparing a power workstation to the server might also be useful for your programmers.

Server for bulllet proof data storage or small meta data downloads

Workstation for better

Reliability

Availability

Serviceability (given the programmer has admin previldges)

Performance

Functionality ( xcmd/pipes,command macros/python/R/Perl?)

[SASKiwi]

This is our strategy for best practice for external ODBC data sources with EG:

• Define database connections completely using connection strings. This avoids DSN definitions entirely.
• Define all common database connections in SAS metadata via SAS Management Console so they are available to users automatically so no additional LIBNAMEs are required.

Defining complete ODBC connection strings can be tricky and are database specific. I know how to do it for SQL Server but I don't have Oracle available to test. What Oracle ODBC driver are you using? If you are using a Microsoft one then this reference may be helpful:

https://www.connectionstrings.com/microsoft-odbc-for-oracle/

To include that string in an Oracle ODBC LIBNAME, this reference will give you the info on how you construct it:

https://communities.sas.com/t5/SAS-Communities-Library/A-quick-guide-to-connecting-to-Oracle-from-SA...

Defining database connections in SMC is quite involved but believe me it is well worth the effort. This link should get you started:

https://communities.sas.com/t5/SAS-Communities-Library/Best-practices-in-connecting-external-databas...

[fortytwo]

we use metadata programming to add oracle-library definitions to the metadata server. I suppose for this approach it is not relevant, whether you access the Oracle schema with SAS Access to Oracle (in our case) or through ODBC. There are serveral advantages using sas-programms to add the metadata:

1. you can do it on a regular basis with batch programs

2. you can deploy the program for your different environments or installations

3. you can use it in a migration of your SAS platform

4. you can write the programs in a way, that changes in the underlying oracle structures (addition, deletion, change of oracle schema) are propagated into the metadata

The most valuable interface for metadata programming is the OMI xml request/response framework you can use with proc metadata. This allows to extract the hiarchical structure of the metadata into relational tables which are easier to read. Conversely you can set up sas tables which are transformed into metadata-xml-requests. This approach is easier to understand, than the user of data step metadata functions, which often result in lengthy SAS programs.

For password management I recommend the SetPassword utility in the SASPlattformObjectFramework. Again you can use it in batch mode with a text file as input.