Setting Up Query Managers

Overview

The Query Manager is the link between a query and the data to be queried. It specifies the location of the data and the tables that describe the structure of the data. It also specifies the options and SAS statements that are activated when the Query Manager is used. A Query Manager must be set up to query data. By default, if no Query Manager is defined, a personal SAS Query Manager is created the first time you access the Query window. A user, SAS administrator, or database administrator (DBA) can set up Query Managers.

If Master and Group profiles are set up at your site, you can share Query Managers with a group of users or with the entire site. You can specify the location of the Query Managers in Master, Group, and User profiles. System administrators can restrict users from overwriting the location of the Query Managers that are specified in their User profile. For additional information on profiles, see Setting Up and Customizing Master and Group Profiles.

A Query Manager consists of definitions and tables. Query Manager definitions specify

the type of data (for SAS data or, on z/OS, DB2 data)
the name of a program that creates or updates the Query Manager tables
the location of the Query Manager tables
whether the Query Manager tables are dynamic (views) or static (tables)
whether the user is allowed to save the relations being used when tables are joined
any SAS statements that are submitted when the Query Manager is activated and deactivated
user profile options that are set when the Query Manager is activated.

Query Managers are based on SAS tables that contain information about the structure of the data tables to be accessed. These SAS tables are generated or updated by SAS programs that retrieve the information either from SAS dictionary tables or, for DB2 on z/OS, the DB2 systems catalog. The SAS programs can be modified to retrieve information from external files or external data dictionaries. Storing the data table structure information in SAS tables has the following advantages:

You can access the table structure information from SAS quickly.
Only limited interaction with the SAS dictionary tables or DB2 systems catalog is needed.
Only relevant information is displayed.
You can customize and extend the information that is available.
You can restrict READ access to DB2 catalogs.

One program is provided to create SAS Query Manager tables. Five programs are provided to create five different types of DB2 Query Manager tables. These programs are run from Query and Reporting. They also can be run from outside Query and Reporting, either from the Program Editor window or through batch processing.

The four SAS data files that are created for the SAS Query Manager are:

_SACOLS: This table contains information about each column in the available SAS tables.
_SAINDX: This table contains information about the indexes that are defined on columns in tables in the _SACOLS table.
_SARELS table: This table contains relations between tables.
_SATAB: This table contains information about each SAS table or SAS view that can be queried with the Query Manager.

The four SAS data files that are created for the DB2 Query Manager are:

_DB2COLS: This table contains information about every DB2 column in the _DB2TAB table.
_DB2INDX: This table contains information about indexes that are defined on columns in tables in the _DB2COLS table.
_DB2RELS: This table contains primary key/foreign key relations that are defined between DB2 tables.
_DB2TAB: This table contains information about every DB2 table or view that the user can query.

See Creating SAS Query Manager Tables for a detailed description of the Query Manager tables and the programs that create them. In that section, you will also find information on modifying the programs that create the Query Manager tables so that information can be retrieved from other sources.

Top of Page