Example: Before and After the Metadata Engine :: SAS(R) 9.3 Language Interfaces to Metadata

Overview

This example shows how data can be accessed with the SAS/ACCESS Interface to Oracle and then, for comparison, shows how the same data can be accessed with the metadata engine. The code accesses Oracle data, lists the tables that exist in the data source, and prints the contents of one table.

Using the SAS/ACCESS Interface to Oracle Engine Directly

To use the SAS/ACCESS Interface to Oracle engine directly to access the data, you submit statements like the following, which require that you know how to use the Oracle engine and that you know the appropriate options to access the data:

libname oralib oracle user=myuser pw=mypw 
   path=ora_dbms preserve_tab_names=yes
   connection=sharedread schema=myschema; 1

proc datasets library=oralib; 2
quit;

proc print data=oralib.Sales (readbuff=1000); 3
run;

data work.temp;
   set oralib.Sales (dbindex=myindex); 4
run;

1	Identifies an Oracle library that contains the Oracle tables that you want to process.
2	Lists all of the Oracle tables that are available.
3	Displays the Oracle Sales table.
4	Attempts to use the specified index to improve performance.

Using the Metadata Engine

You can access the same data using the metadata engine. However, when using the metadata engine, you do not have to know how to use the Oracle engine, or know the appropriate options to access the data. You do not need to be aware that you are using an Oracle database.

Using SAS Management Console or SAS Data Integration Studio, an administrator creates metadata in a SAS Metadata Repository for your Oracle environment. The metadata engine interprets this metadata and locates your data. You do not have to know how to connect to the metadata server or the repository, because this information can be provided by the metadata system options.

Here is what happens when you use the metadata engine to access the Oracle data:

You submit the following LIBNAME statement for the metadata engine. LIBRARY= identifies the SASLibrary object that defines information about the Oracle library. This SASLibrary object serves as an anchor point for obtaining other metadata.
```
libname metaeng meta library=mylib;
```
The metadata server connection properties are specified by metadata system options, so they are omitted from the LIBNAME statement.
The metadata engine queries the repository. The query retrieves information from the SASLibrary object that is specified by LIBRARY=. Connection and schema information are returned by the query.
From the information returned by the metadata query, the metadata engine is able to generate the following LIBNAME statement, which is the same LIBNAME statement that is shown at the beginning of this example:
```
libname oralib oracle user=myuser pw=mypw 
   path=ora_dbms preserve_tab_names=yes 
   connection=sharedread schema=myschema;
```
With the generated LIBNAME statement, the metadata engine uses the Oracle engine anytime it needs to access the Oracle data. For example, to view the tables that exist, you would submit the following:
```
proc datasets library=metaeng;
quit;
```
The metadata engine sends a query to the repository. The query requests all members of the SASLibrary that was specified by LIBRARY=. The metadata engine returns only those members that are defined in the repository. Any Oracle table that is not defined in the metadata is not displayed. (If METAOUT=DATA, all tables are displayed, regardless of whether they are defined in metadata.)
For the following PRINT procedure, the metadata engine sends a request to the repository for the metadata that is associated with the Sales table.
```
proc print data=metaeng.Sales;
run;
```
The metadata engine returns the columns that are defined in the metadata. Therefore, if the Sales table has 20 columns, and only five columns are defined in the metadata, then you see only five columns. (If METAOUT=DATA, all columns are displayed, regardless of whether they are defined in the metadata.)
A SASLibrary metadata object also stores index information for tables. Any use of the metadata engine that uses indexes causes a query to the repository that requests index information. The index metadata must match the physical index on the table. The metadata engine uses the index information that is stored in the repository:
```
data work.temp;
   set metaeng.Sales;
run;
```