Using SAS Data Sets to Store System Tables Information

For access modes other than SAS, the individual database management system (DBMS) tables are queried for the Available Tables and Available Columns lists. For DB2, DB2/2, DB2/6000, SYBASE, and ODBC, the system tables information that fills the Available Tables and Available Columns can now be stored in SAS data sets. One data set contains Tables information. The other data set contains Columns information. When you use a remote session for querying, these SAS data sets can be stored locally for enhanced performance.

If you have read authority to the system tables, then you can assign the DB2, DB2/2, or DB2/6000 system tables to be read to SAS tables that are mirror images of the DB2 tables. If you are executing a remote session, then you can specify whether these SAS DB2 system tables are to be read locally or remotely.

You can create these mirror image tables by querying the DB2 system tables in an SQL Query Window session and creating SAS tables of the queries built. They can also be created with a PROC SQL program that queries the DB2 system tables. The PROC SQL statements can be saved and the SAS program can be run in batch whenever you need to update the mirror image tables.

SAS tables (data sets) that are created by automatic joins can also be created for any of the access modes that provide system tables/dictionaries with a PROC SQL program.

For ODBC, you can generate SAS data sets that contain system tables:

1. Select
   Profile – Set Preferences

2. In the Preference Settings for Profile window, set Access Mode to ODBC. Enter the data source, user name , and password, and select OK .

3. In the Preference Settings for Profile window, select the right arrow next to Access Mode Options .

4. In the Preference Settings for ODBC Access Mode window, enter the SAS data set names for system tables information.

5. If SAS data sets have not been created, then select Create Table and Create Column .

For SYBASE, the system table information can be read from a SAS data set:

1. Select
   Profile – Set Preferences

2. In the Preference Settings for Profile window, set Access Mode to SYBASE.

3. In the SYBASE Access Mode Options window, enter the parameters for the SYBASE access mode.

4. Select SAS Data Sets.

5. In the Available Tables and Columns for SYBASE window, enter the SAS library name and SAS data set name for SYBASE system tables. If the SYBASE system tables do not already exist, then select Create Table and Create Column .