Examples for the Metadata Engine

Example: Before and After the Metadata Engine

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;

Identifies an Oracle library that contains the Oracle tables that you want to process.
Lists all of the Oracle tables that are available.
Displays the Oracle Sales table.
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
   liboptset=myopts;
```
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=, and from other objects that are associated with the SASLibrary object, such as DatabaseSchema, SASClientConnection, Login, or Property objects.

The following table lists objects that are returned from the query. The table does not show the SASClientConnection objects and PropertySet objects.

SASLibrary Object Query Results
SASLibrary	DatabaseSchema	Login	Property	Property	Property
Libref: oralib	SchemaName: myschema	User: myuser	PropertyName: preserve_tab_ names	PropertyName: path	PropertyName: Connection
Engine: oracle		Password: mypw	DefaultValue: yes	DefaultValue: ora_dbms	DefaultValue: sharedread
isDBMSLibname: TRUE			Delimiter: =	Delimiter: =	Delimiter: =
			UseValueOnly: False	UseValueOnly: False	UseValueOnly: False

Note: The relational DBMS model in Diagrams of the SAS Metadata Model can help you determine how to associate these objects with one another. [cautionend]

From the information embedded in the objects, and from information that is implied in the relational DBMS model, 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 (optset=myopts);
run;
```
The metadata includes PhysicalTable, Column, and Property object information. The OPTSET= data set option, a metadata engine option, tells the metadata engine to return Property objects that are associated with the MYOPTS PropertySet object. The Property objects contain the values of data set options that have been customized for this table. These data set options are used by the Oracle engine while processing the data.

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.)
The relational DBMS model allows you to store index information for tables. Each Index object is associated with a table and with the columns that make up the index. 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. In the following statements, the OPTSET= data set option specifies to use indexes to process the table. The metadata engine uses the index information that is stored in the repository:
```
data work.temp;
   set metaeng.Sales (optset=index_opts);
run;
```

Top of Page