SAS/ACCESS Software for Relational Databases: Reference

Information for the Database Administrator

This section includes information about how the DB2 engine works, how SAS connects to DB2, and how the DB2 engine accesses DB2 system catalogs.

How the DB2 Engine Works

The DB2 engine uses the Call Attachment Facility (CAF) or the Recoverable Resource Management Services Attachment Facility (RRSAF) as the Application Programming Interface (API) in order to communicate to the local DB2. Both attachment facilities enable programs to connect to and use DB2 for SQL statements, or commands. The DB2 engine uses the attachment facilities to establish and control its connections to the local DB2 subsystem. DB2 allows only one connection for each task control block (TCB), or task. The SAS System and SAS executives run under one TCB, or task.

The design of the new, dynamic DB2 LIBNAME engine gives SAS users the ability to connect to DB2 more than once. Because the CAF and RRSAF allow only one connection per TCB, the DB2 engine attaches a subtask for each subsequent connection that is initiated. The DB2 engine uses the ATTACH, DETACH, POST, and WAIT assembler macros to create and communicate with the subtasks. The DB2 engine does not limit the number of connections/subtasks that a single SAS user can initiate. Design of the DB2 LIBNAME Engine illustrates how the DB2 engine works.

Design of the DB2 LIBNAME Engine

[IMAGE]

How and When Connections Are Made

The DB2 engine always makes an explicit connection to the local DB2 subsystem (SSID). When a connection executes successfully, a thread to DB2 is established. For each thread's or task's connection, DB2 establishes authorization identifiers (AUTHIDs).

The DB2 engine determines when to make a connection to DB2 based on the type of open mode that the SAS application requests for the DB2 tables. There are three distinct types of open modes that a SAS application can request: read, update, and output. The default behavior for the DB2 engine is to share the connection for all open modes of read for each DB2 LIBNAME statement. For every update and output open mode, the DB2 engine acquires a separate connection to DB2 for that open instance. The default connection behavior can be changed by using the CONNECTION= LIBNAME option.

Several SAS applications require the DB2 engine to query the DB2 system catalogs. When this type of query is required, the DB2 engine acquires a separate connection to DB2 in order to avoid contention with other applications that are accessing the DB2 system catalogs. Refer to Accessing the DB2 System Catalogs for more information about accessing system catalogs.

Connections Using the Distributed Data Facility

Distributed Data Facility (DDF) is an optional product that allows OS/390 DB2 applications to access data on other 0S/390 DB2 subsystems. The DB2 engine supports DDF. To connect to a DDF remote server or location, the DB2 engine must use system-directed access. System-directed access allows one OS/390 DB2 subsystem to execute SQL statements on another OS/390 DB2 subsytem. System-directed access uses a DB2-only private protocol. The DB2 engine cannot explicitly request a connection, but instead, it performs an implicit connection when a distributed request is initiated by the SAS application. To initiate an implicit connection, the SAS option LOCATION= must be specified. When the LOCATION= option is specified, the three-level table name (location.authid.table) is used in the SQL statement that is generated by the DB2 engine. When the SQL statement that contains the three-level table name is executed, an implicit connection is made to the remote DB2 subsystem. The primary authorization ID of the initiating process must be authorized to connect to the remote location. The DB2 engine always first connects locally, then DB2 connects implicitly to a remote subsystem based that is on the location.

Connections Using the Distributed Relational Database Architecture (DRDA)

Distributed Relational Database Architecture (DRDA) is a set of protocols that enables a user to access distributed data. This enables the DB2 engine to access multiple remote tables at various locations. The tables can be distributed among multiple platforms, and both like and unlike platforms can communicate with one another. In a DRDA environment, DB2 acts as the client and/or the server. The SAS application must use the client DB2 to communicate to the server.

To connect to a DRDA remote server or location, the DB2 engine uses an explicit connection. To establish an explicit connection, the DB2 engine first connects to the local DB2 subsystem via the attachment facility (CAF or RRSAF). Then the DB2 engine issues an SQL CONNECT statement to connect from the local DB2 subsystem to the remote DRDA server prior to accessing data. The CONNECT statement is passed to the remote location after the connection is made. To initiate a connection to a DRDA remote server, you must specify the SERVER= LIBNAME option. More than one connection to a remote location is allowed, although only one connection can be active at any one time. To connect to more than one remote DRDA location, the SAS application must use one LIBNAME statement with the SERVER= option for each remote location.

Recoverable Resource Management Services Attachment Facility (RRSAF)

By default, the SAS/ACCESS engine for DB2 uses the Call Attachment Facility (CAF) to make its connections to DB2.(footnote 1) By setting the SAS system option DB2RRS, the DB2 engine instead uses the Recoverable Resource Manager Services Attachment Facility (RRSAF). Only one attachment facility can be used at a time, so the DB2RRS or NODB2RRS system option can only be specified when a SAS session is invoked. RRSAF is a new feature in DB2 Version 5, Release 1, and the support for it by the DB2 engine is new for Version 8 of SAS software.

The RRSAF is intended to be used by SAS servers, such as the ones used by SAS/SHARE software. RRSAF supports the ability to associate an OS/390 authorization identifier with each connection at sign on. This authorization identifier is not the same as the authorization ID that is specified in the AUTHID= data set option or SAS/ACCESS LIBNAME option. DB2 uses the RRSAF-supported authorization identifier to validate a given connection's authorization to use both DB2 and system resources, when those connections are made using the System Authorization Facility and other security products like RACF. Basically, this authorization identifier will be the userid with which you are are logged onto OS/390.

Beginning in Version 7, SAS supports multiple CAF connections for a SAS session. Thus, for a SAS server, each client can have their own connections to DB2; that is, multiple clients no longer have to share one connection. Because CAF does not support signon, however, each connection that the SAS server makes to DB2 has the OS/390 authorization identifier of the server, and not the authorization identifier of the client for which the connection is made.

With RRSAF, the SAS server makes the connections for each client and the connections have the client's OS/390 authorization identifier associated them. This is only true for clients that were authenticated by the SAS server, which occurred when the client specified a userid and password. Servers authenticate their clients when the clients provide their userids and passwords. Generally, this is the default way that servers are run. If a client connects to a SAS server without providing his userid and password, then the identifier associated with his connections will be that of the server-- just like when using CAF--and not the identifier of the client.

Other than specifying DB2RRS at SAS startup, there is nothing else that needs to be done. The DB2 engine automatically signs on each connection that it makes to DB2 with either the identifier of the authenticated client or the identifier of the SAS server for non-authenticated clients. The authenticated clients have the same authorities to DB2 as they have when they run their own SAS session from their own ID and access DB2.

Accessing the DB2 System Catalogs

For various types of SAS procedures, the DB2 engine must access the DB2 system catalogs for information. This information is limited to a list of all the tables for a specific authorization identifier. There are several factors that determine what SQL query is generated to get information from the system catalogs.

If the LIBNAME statement that references DB2 includes the AUTHID= option, or if AUTHID= was entered as a data set option, the following SQL code is generated:
```
SELECT NAME FROM SYSIBM.SYSTABLES 
  WHERE (CREATOR = 'authid');
```

If no AUTHID= option was entered, the following SQL code is generated:

SELECT NAME FROM SYSIBM.SYSTABLES 
  WHERE (CREATOR = "OS/390-userid");

The SAS procedures or applications that request the list of DB2 tables includes, but is not limited to, PROC DATASETS and PROC CONTENTS, or any application that needs a member list. If the SAS user does not have the necessary authorization to read the DB2 system catalogs, the procedure or application will fail.

Because querying the DB2 system catalogs can cause some locking contentions, the DB2 engine will initiate a separate connection for the query to the DB2 system catalogs. Once the query has completed a COMMIT WORK is executed.

For additional information about

DB2-specific LIBNAME options, refer to SAS/ACCESS LIBNAME Options.
DB2-specific data set options, refer to SAS/ACCESS Data Set Options: DB2 Specifics
SAS/ACCESS LIBNAME options, refer to SAS/ACCESS LIBNAME Statement
SAS/ACCESS data set options, refer to SAS/ACCESS Data Set Options

FOOTNOTE 1: Henceforth, the SAS/ACCESS engine for DB2 will be referred to as the "DB2 engine" for brevity.

Chapter Contents
Previous
Next
Top of Page