Setting Up Query Managers

Defining and Using a Public Query Manager

If you are a SAS administrator or a database administrator (DBA), you can create a Group or Master Query Manager. Group and Master Query Managers give a group of users or all users the same type of access to the same data libraries through Query and Reporting. This section shows you how to create a static, Group or Master SAS Query Manager.

Defining and Using a SAS Query Manager

The Query Manager tables for this Query Manager can be updated by submitting the table creation program from the Program Editor window or through batch processing.

To create a public Query Manager, complete the following steps:

create Query Manager tables in a public SAS data library that users can access.
create Query Manager definitions and save them in a SAS data library. Users must have access to this library. Make sure that the Group or Master profile references the SAS data library that contains the Query Manager definitions. Master and Group profiles are optional. For additional information about creating profiles, see Setting Up and Customizing Master and Group Profiles.

Defining and Using a DB2 Query Manager

A DBA must create the DB2 Query Manager because READ access to the DB2 systems catalog is required to run the table creation programs. The subsets of the DB2 Query Manager tables can be updated by using Update Query Manager from the Tools menu of the Select window. The master DB2 Query Manager tables for this Query Manager can be updated by submitting the table creation program again from the Program Editor window or through batch processing.

Depending on the number of DB2 tables and your site's policy for accessing data, choose one of the following strategies.

Let all users run their queries using the master Query Manager tables.
Set up group Query Manager tables for groups of users.
Let users have their own personal Query Manager tables.
Use a combination of the above strategies.

The strategy you choose may depend on whether users should see only the tables that they actually can query. It also depends on whether or not users create their own tables and how often. You can easily change data access by making changes to the Master or Group profiles. This section shows how to generate a set of Query Manager tables that contain only the tables to which a user has READ access.

A two-step process is used to create the DB2 Query Manager tables:

Access the DB2 systems catalog and create a master set of the Query Manager tables in SAS. By default, these master tables contain information about all the tables in a DB2 subsystem and information about table authorizations. Optionally, create a subset of the master tables.
Create Query Manager definitions and make them available to all users or to groups of users. The Query Manager definitions supply all or a subset of the master tables to the users.

The program that generates the master set of Query Manager tables is located in SASHELP.QASSIST.SYSQM.SOURCE. This program is intended to be run as a batch job by the DBA. However, it also can be run from the Program Editor window. Running this program requires READ access to the tables in the DB2 systems catalog.

All users can use the master Query Manager tables. However, it is more efficient for users or groups of users to use subsets of these tables for their own Query Managers. The following three programs are supplied with Query and Reporting to create subsets of the master DB2 Query Manager tables.

SASHELP.QASSIST.QMUSER1.SOURCE creates a subset for a specified set of creators.
SASHELP.QASSIST.QMUSER2.SOURCE creates a subset based on the tables to which the user is granted READ access.
SASHELP.QASSIST.QMUSER3.SOURCE creates a subset based on a creator table that contains all creators whose tables are contained in the subset.

The SASHELP.QASSIST.DB2DYNA.SOURCE program creates a set of dynamic DB2 Query Manager tables that read from the DB2 systems catalog directly. A dynamic DB2 Query Manager is useful if the structure of the DB2 tables changes frequently or if DB2 tables are added and deleted frequently. Using a dynamic Query Manager on a large DB2 system is considerably slower than using a static one because the DB2 catalog must be read at the time a user chooses the Select window. Individual users who use a dynamic DB2 Query Manager must be granted SELECT privileges on the DB2 catalog tables discussed in Setting Up Query Managers.

The following figure shows an example of a table setup for a DB2 Query Manager. You may want to create multiple DB2 Query Manager definitions. For example, you may have one Query Manager definition that points to your production DB2 subsystem and another Query Manager definition that points to your test subsystem. Both Query Manager definitions can refer to the same set of Query Manager tables if the two subsystems' data definitions are the same.

Example DB2 Query Manager Setup

[Example DB2 Query Manager Setup]

Creating Master DB2 Query Manager Tables

To create the master DB2 Query Manager tables, you must be granted READ access to the DB2 systems catalog. Allocate a SAS data library to store the master Query Manager tables. Give the library a libref of SYSQM, for example:

libname sysqm 'userid.dbquery.tables';

Use the program SASHELP.QASSIST.SYSQM.SOURCE to create the tables. You can run this program in batch or from the Program Editor window as follows:

In batch, modify the program by using the JCL statements that are necessary to run the program in batch at your site. Include a DDNAME called SYSQM that references the SAS data library you created to store the Query Manager tables. Submit the job.
Copy the program SASHELP.QASSIST.SYSQM.SOURCE into the Program Editor window, as shown in the following display.

Editing Source Statements to Create Master DB2 Query Manager Tables

At the beginning of the program, add a LIBNAME statement that references the library you created to store the Query Manager tables. Use the required libref SYSQM, for example,
```
libname sysqm 'userid.dbquery.tables';
```
In the first %LET statement, edit the DB2 Subsystem ID to the appropriate SSID. Submit the job for processing.

For DB2 Query Managers, three programs are supplied with Query and Reporting that create subsets of the master Query Manager tables:

SASHELP.QASSIST.QMUSER1.SOURCE
SASHELP.QASSIST.QMUSER2.SOURCE
SASHELP.QASSIST.QMUSER3.SOURCE

See Creating DB2 Query Manager Tables for more information about these programs.

To create a subset based on the tables to which the user is granted READ access, copy SASHELP.QASSIST.QMUSER2.SOURCE into the Program Editor window. Save the program to a library to which users have READ access. You can edit the program using Generation Program from the Tools menu of the Query Manager Administration window as described in Create the Public Query Manager Definition.

Creating Public SAS Query Manager Tables

To create Query Manager tables for a public SAS Query Manager, submit a LIBNAME statement from the Program Editor window that references the SAS data library to contain the Query Manager tables. This library must not contain Query Manager tables for any other Query Manager definition.

Submit a LIBNAME statement for each SAS data library that you want to make available to other users, as in the following examples. When you create the Query Manager tables, these SAS libraries are included in the tables. Make sure that these libraries are allocated each time the Query Manager is used. You can allocate the libraries in an AUTOEXEC.SAS file, by using the Initiation and Termination programs, or by using a SAS/ASSIST start program. For additional information, see Using Initiation and Termination Programs of this document and "Using Logon/Logoff Exits and an Alternate Main Menu" in Chapter 12, "Doing More with Setup," in Doing More with SAS/ASSIST.

Copy the SASHELP.QASSIST.SASQM.SOURCE program into the Program Editor window. This program creates the public Query Manager tables. Edit selected program statements for your site's use or for a group's use as follows:

Uncomment the first %LET statement. Change the assignment of the macro variable QMLIB (currently set to WORK) to the libref that points to the SAS data library to contain the public Query Manager tables. For example, change the statement to %let qmlib=mgr;
Uncomment and change the assignment of the macro variable EXCLLIB so that all the SAS data libraries you want to exclude from the public Query Manager, if any, are listed.

The following display shows the edited SASQM.SOURCE program. The libref MGR is assigned to the macro variable QMLIB. The macro variable EXCLLIB points to the libraries SASHELP and MAPS by default. This example adds SASUSER, WORK, MGR, and LIBRARY to the libraries to be excluded from the Query Manager.

Editing Source Statements to Create Public Query Manager Tables

[Editing Source Statements to Create Public Query Manager Tables]

Submit the program to generate the Query Manager tables.

Note: Save this program and re-submit it periodically to update the Query Manager tables. You can use the Program Editor window or batch processing to re-submit this program. For example, you may want to batch submit this job every night to make sure that users have updated table information in the morning. [cautionend]

To help users join SAS tables, a SAS administrator can do one of the following:

Using SAS/FSP software, edit the _SARELS table in the library that contains the Query Manager tables. Add all the relations so that joins can be made automatically. For further information, see Creating SAS Query Manager Tables of this document and "Using Logon/Logoff Exits and an Alternate Main Menu" in Chapter 12, "Doing More with Setup," in Doing More with SAS/ASSIST.
Create a public Query Manager that references the Query Manager tables you have just created. From Query and Reporting, save any joins you want done automatically. See Chapter 10, "Query and Reporting," in Doing More with SAS/ASSIST for details on saving joins.

Creating a Query Manager Definition

To create a Query Manager definition, follow these steps:

create a storage location for the Query Manager definition
point to the Query Manager definition with profiles
create the Query Manager definition
save the Query Manager definition.

Create a Storage Location for the Query Manager Definition

Create a SAS data library to store the Master and Group Query Manager definition. Submit a LIBNAME statement that references the SAS data library, as shown in the following example:

libname public 'userid.system.public';

All users must have READ access to this library.

Point to the Query Manager Definition with Profiles

After you create a place to store the public Query Manager definition, point to it using the Master and Group profiles. Follow this path to open the Master or Group Profile window shown in the following display:

Tasks Setup Profiles Master/group...

Master/Group Profile Window

[Master/Group Profile Window]

By default, all the options for the Master profile are shown. You can edit the fields in this window only if you have WRITE access to the SAS data library that contains the Master profile. If your site has no Master profile, this window is empty.

In the Type field, type Query Manager and press ENTER. You can also type a ? and press ENTER to display a list of Type options. Select Query Manager from the list. The Query Manager options appear. The following display shows the Query Manager profile options.

Query Manager Profile Options Window

[Query Manager Profile Options Window]

You can create public Query Managers for all users (Master Query Manager) or for a group of users (Group Query Manager).

If you are creating a public Query Manager for all users, in the Value field for Master - path type the external file name of the SAS data library for the master Query Manager definition. In the previous display, the path for the master Query Manager definition is userid.system.public . A libname to the specified path is assigned automatically.

To ensure that the profile change is available to all users, type R or U in the Status field, to set the value to RESTRICT or UPDATE, respectively. Then select Save from the File menu. See Assigning and Changing the Status of Profile Options for more information about profile option status.
If you are creating a public Query Manager for a group of users, open the appropriate Group profile and point the Group profile to the location of the public Query Manager definitions.

To open a Group profile, select Open from the File menu. The Open Profile window appears.

To select a profile, type S in the Cmd field and press ENTER. The Master/Group Profile window appears again with the profile information for the chosen group as shown in the following display. The Group profile reflects the changes made to options with a status of U in the Master profile. The following display shows the Group profile for the Accounting Group.

Accounting Group Profile Window

In the Value field for Group - path, type the external file name of the SAS data library for the public Query Manager definitions. In the previous display, the path for the group Query Manager definitions is userid.system.public . A libname to the specified path is assigned automatically.

To ensure that the profile change is available to all users, type R or U in the Status field of the window, to set the value to RESTRICT or UPDATE, respectively. See Assigning and Changing the Status of Profile Options for more information about profile option status.

Select Save from the File menu to save your changes. Select Close from the File menu to return to the WorkPlace menu.

Create the Public Query Manager Definition

After you have set up your Master and Group profiles, you can create the public Query Manager definition. From the Query and Reporting Setup window, choose Administration. The Query Manager Administration window appears.

You can change the default settings by typing new values where typing is permitted or by choosing a different selection where two alternatives are listed.

Database

specifies the type of database for which you want to set up a Query Manager. Type the name of the database you want to use or type a ? in the field and press ENTER to select a database from a list of the database systems that you can access.

Program to generate Query Manager tables

specifies the name of the program that generates and updates the Query Manager tables.

If you select Yes, the fields SAS entry and External file appear. If the program is stored in a catalog entry, select SAS entry and type the appropriate four-level SAS name. If the program is stored in an external file, select External file and type the physical file name for the program. The external file must already exist.

For DB2 Query Managers, three programs are supplied with Query and Reporting that create subsets of the master Query Manager tables: SASHELP.QASSIST.QMUSER1.SOURCE, SASHELP.QASSIST.QMUSER2.SOURCE, SASHELP.QASSIST.QMUSER3.SOURCE.

You can copy and edit these programs to create subsets of the master DB2 Query Manager tables.

For this example, use the program in PUBPROGS.QMPROGS.QMUSER2.SOURCE. This is the program that you copied and saved in Defining and Using a Public Query Manager. This program generates the subset of the DB2 Query Manager tables.

Select Generation Program from the Tools menu. The QMUSER2.SOURCE program appears as shown in the following display.

QMUSER2.SOURCE

[QMUSER2.SOURCE]

Edit the program following the commented instructions in the program. Choose Save from the File menu to save the program. Choose Close from the File menu to return to the Query Manager administration window.

If you supply a program name, you can update the Query Manager tables using Update Query Manager from the Tools menu of the Select window. See Defining and Using a Public Query Manager for further information.

If you select No, the Query Manager tables cannot be updated using Update Query Manager from the Tools menu of the Select window. The tables must be updated by batch submitting the program to update the tables or submitting the program from the Program Editor window.

Location of Query Manager tables

specifies the location of the Query Manager tables. You store the Query Manager tables in a SAS data library.

Select SAS library name and type the libref or select Physical file name and type the external file name of the SAS library. In the previous display, the location of the subsetted DB2 Query Manager tables is SASUSER because this DB2 Query Manager uses the QMUSER2.SOURCE program. This program generates a set of Query Manager tables containing only the tables to which the user has READ access.

If you use a SAS library name, use Initiation Program and Termination Program to assign a libref to the SAS library when the Query Manager is used. See Using Initiation and Termination Programs for more information. Because SASUSER is automatically assigned to your SAS session, you do not need to use the initiation and termination programs to assign the libref.

Query Manager type

For this example, select Static. A static Query Manager is based on SAS data files and provides faster access to the Query Manager tables. However you must update the static Query Manager tables when the structure of the tables changes, for example when new tables (SAS data files) are added to your SAS data libraries or when new columns are added to the tables. See Defining and Using a Public Query Manager for more information.

A dynamic Query Manager is based on PROC SQL views to SAS dictionary tables or, for DB2 on z/OS, system tables. The PROC SQL views supply current information to a dynamic Query Manager each time the Query Manager is referenced. The SASHELP.QASSIST.DB2DYNA.SOURCE program creates a set of dynamic DB2 Query Manager tables that read from the DB2 systems catalog directly. See Creating Dynamic SAS Query Manager Views and Creating DB2 Query Manager Tables for more information.

Allow Update of relations

For this example, select No so that users can only browse the Query Manager tables from the Join window.

Select Yes to allow users to add and delete relations in the Query Manager tables by following this path from the Join Manager window:

View Relations Current Tables | All Tables

Note: Relations can be updated only when SAS library name is selected for Location of Query Manager tables. When Physical file name is selected for Location of Query Manager tables, the library is open for READ access only. [cautionend]

If you select Yes, you can save any joins you make in the Join Manager window for future automatic joining of tables. See Chapter 10, "Query and Reporting," in Doing More with SAS/ASSIST for more information.

Using Initiation and Termination Programs

Use Initiation Program and Termination Program to assign librefs to the SAS libraries that you want users to access when they use the public Query Manager. The SAS libraries are then automatically available to users. You also can use these selections to assign a libref to the location of the Query Manager tables when it is specified with a SAS library name.

Select Initiation Program from the Tools menu of the Query Manager Administration window. The Initiation Program window appears. The Initiation Program window is an editor in which you can type SAS programming statements that are executed when the Query Manager is activated. For this example, type the LIBNAME statements for each SAS data library you want to make available to other users. Remember to use the same LIBNAME statements that you used when you generated the Query Manager tables. The following display shows the libname statements for the FINANCE and PRODUCTS data libraries.

Initiation Program Window

[Initiation Program Window]

Select Save from the File menu to save the initiation program. Select Close from the File menu to return to the Query Manager Administration window.

Select Termination program from the Tools menu. The Termination Program window appears. The Termination Program window is an editor in which you can type SAS programming statements that are executed when the Query Manager is deactivated. For this example, use the Termination Program window to clear the librefs you allocated in the Initiation Program window. The following display shows the LIBNAME statements used to clear the FINANCE and PRODUCTS librefs.

Termination Program Window

[Termination Program Window]

Select Save from the File menu to save the termination program. Select Close from the File menu to return to the Query Manager Administration window.

Using the Query Manager Profile

This section is applicable to DB2 Query Managers only.

Use the Query Manager Profile to specify the location of the master DB2 Query Manager tables.

To open the Query Manager Profile window, select Query Manager Profile from the Tools menu in the Query Manager Administration window. The following display shows the Query Manager Profile window. Make sure that the value for Type is ACCESS TO DB2 DATA.

Edited Query Manager Profile Window

[Edited Query Manager Profile Window]

In the Master Query Manager field, type the physical name of the SAS data library that contains the master DB2 Query Manager tables, for example, USERID.DB2QUERY.TABLES as shown in the previous display.

In the Value field for Subsystem id, type the SSID, for example PROD as shown in the previous display. In Query and Reporting, the DB2 subsystem value can be defined using one of the following methods:

in the DB2 Query Manager profile as described in this section
using the DB2SSID= option statement outside SAS/ASSIST software. DB2SSID=subsystem-name specifies the DB2 subsystem name. DB2SSID= is valid in the OPTIONS statement, as part of the configuration file, and when you invoke SAS
in the Master, Group, or User profile.

When you save the DB2 Query Manager definition from the Administration window, all profile options are defined specifically for this Query Manager, including the DB2 subsystem. You can set up a DB2 Query Manager for each subsystem. Then, when users need to use a different subsystem, they can select another DB2 Query Manager.

Verify that the User tables and date and time constants have the appropriate values in the Query Manager profile. Change these values and any other values as necessary.

To return to the Query Manager Administration window, select Close from the File menu.

Save the Query Manager Definition

To save the Query Manager definition, select Save As... from the File menu. The Save As window appears.

Type the name, description, and the catalog where the Query Manager definitions are stored. The above display shows the ACCOUNT group Query Manager stored in the PUBLIC.MANAGER catalog.

Select OK to save the Query Manager definition. To return to the WorkPlace menu, select Close from the File menu, then Goback .

Exit SAS/ASSIST software and clear the libref to the SAS data library where you stored the Master and Group Query Manager definitions. If you have a libref allocated to the physical file name of the Query Manager definitions, Query and Reporting cannot allocate them. For example, submit:

libname public clear;

Selecting a Query Manager

To select a Query Manager, choose Select Query Manager from the Tools menu in the SQL Editor window.

You may get a message that the list of Query Managers is being updated. A list of Query Managers is displayed, including any Query Managers that you have created.

Select your newly created Query Manager from the list. The list of Query Managers shows the Query Manager name, description, and type. The type of Query Manager can be M for Master, G for Group, or U for User. See Setting Up and Customizing Master and Group Profiles for more information on Master, Group, and User profiles.

Updating a Query Manager

You need to update static Query Managers periodically. For example, to update your master DB2 Query Manager tables, batch submit the SYSQM.SOURCE program that you edited in Creating Master DB2 Query Manager Tables. You can submit this program again from the Program Editor window or you can batch submit this program to update the master Query Manager tables.

To update the tables for your Query Manager, click on Select in the Query window. Next, select Update Query Manager from the Tools menu.

You can also update the tables for your Query Manager in the SQL Editor window. Select Query Manager from the Tools menu in the SQL Editor window. The Select DBMS window appears. Select Update Query Manager from the Tools menu.

The Create/Update Query Manager Tables window appears. The window indicates which program is used to update the Query Manager tables.

Note: SAS Query Managers are updated in a similar way. Follow the same steps whether you are updating a DB2 or a SAS Query Manager. [cautionend]

Select Create to update the tables. A message indicates that the tables have been created and that processing has ended successfully. Select Goback to return to the previous window.

Top of Page