03-09-2017 03:12 PM
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.
libname myoralib odbc dsn=TST user=TestUser password=TestPswd schema='XXXX_LOCK';
03-09-2017 03:32 PM
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
Serviceability (given the programmer has admin previldges)
Functionality ( xcmd/pipes,command macros/python/R/Perl?)
03-10-2017 06:36 PM - edited 03-10-2017 06:44 PM
This is our strategy for best practice for external ODBC data sources with EG:
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:
To include that string in an Oracle ODBC LIBNAME, this reference will give you the info on how you construct it:
Defining database connections in SMC is quite involved but believe me it is well worth the effort. This link should get you started:
03-13-2017 08:16 AM
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.