DB2 under z/OS Information for the Database Administrator

SAS/ACCESS Interface to DB2 under z/OS uses either the Call Attachment Facility (CAF) or the Recoverable Resource Management Services Attachment Facility (RRSAF) to communicate with the local DB2 subsystem. Both attachment facilities enable programs to connect to DB2 and to use DB2 for SQL statements and commands. SAS/ACCESS Interface to DB2 under z/OS 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. SAS and SAS executables run under one TCB, or task.

The DB2 LIBNAME statement lets SAS users connect to DB2 more than once. Because the CAF and RRSAF allow only one connection per TCB, SAS/ACCESS Interface to DB2 under z/OS attaches a subtask for each subsequent connection that is initiated. It uses the ATTACH, DETACH, POST, and WAIT assembler macros to create and communicate with the subtasks. It does not limit the number of connections or subtasks that a single SAS user can initiate. This image illustrates how the DB2 engine works.

SAS/ACCESS Interface to DB2 under z/OS 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 interface determines when to make a connection to DB2 based on the type of open mode (Read, Update, or output) that a SAS application requests for the DB2 tables. Here is the default behavior. You can change this default behavior by using the CONNECTION= option.

Several SAS applications require SAS/ACCESS Interface to DB2 under z/OS to query the DB2 system catalogs. When this type of query is required, the DB2 interface acquires a separate connection to DB2 to avoid contention with other applications that are accessing the DB2 system catalogs. See Accessing DB2 System Catalogs for more information.

The DEFER= LIBNAME option also controls when a connection is established. UTILCONN_TRANSIENT= also allows control of the utility connection—namely, whether it must stay open.

DB2 Distributed Data Facility (DDF) Communication Database (CDB) enables DB2 z/OS applications to access data on other systems. Database administrators are responsible for customizing CDB. SAS/ACCESS Interface to DB2 under z/OS supports both types of DDF: system-directed access (private protocol) and Distributed Relational Database Architecture.

System-directed access enables one DB2 z/OS subsystem to execute SQL statements on another DB2 z/OS subsystem. System-directed access uses a private protocol only for DB2. It is known as a private protocol because you can use only it between DB2 databases. IBM recommends that users use DRDA. Although SAS/ACCESS Interface to DB2 under z/OS cannot explicitly request a connection, it can instead perform an implicit connection when SAS initiates a distributed request. To initiate an implicit connection, you must specify the LOCATION= option. When you specify this option, the three-level table name (location.authid.table) is used in the SQL statement that SAS/ACCESS Interface to DB2 under z/OS generates. 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.

Distributed Relational Database Architecture (DRDA) is a set of protocols that lets a user access distributed data. This lets SAS/ACCESS Interface to DB2 under z/OS 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, server, or both.

To connect to a DRDA remote server or location, SAS/ACCESS Interface to DB2 under z/OS uses an explicit connection. To establish an explicit connection, SAS/ACCESS Interface to DB2 under z/OS first connects to the local DB2 subsystem through an attachment facility (CAF or RRSAF). It then issues an SQL CONNECT statement to connect from the local DB2 subsystem to the remote DRDA server before it accesses data. To initiate a connection to a DRDA remote server, you must specify the SERVER= connection option. When you specify this option, SAS uses a separate connection for each remote DRDA location.

By default, SAS/ACCESS Interface to DB2 under z/OS uses the Call Attachment Facility (CAF) to make its connections to DB2. SAS supports multiple CAF connections for a SAS session. Therefore, for a SAS server, all clients can have their own connections to DB2; multiple clients no longer have to share one connection. However, because CAF does not support sign-on, each connection that the SAS server makes to DB2 has the z/OS authorization identifier of the server. It does not have the authorization identifier of the client for which the connection is made.

If you specify the DB2ERRS system option, SAS/ACCESS Interface to DB2 under z/OS engine 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 be specified only when a SAS session is started. SAS supports multiple RRSAF connections for a SAS session. RRSAF is a new feature in DB2 Version 5, Release 1, and its support in SAS/ACCESS Interface to DB2 under z/OS was new in SAS 8.

The RRSAF is intended for use by SAS servers, such as the ones that SAS/SHARE software use. RRSAF supports the ability to associate a z/OS authorization identifier with each connection at sign on. This authorization identifier is not the same as the authorization ID that you specify in the AUTHID= data set or 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 such as RACF. This authorization identifier is basically the user ID with which you are logged on to z/OS.

With RRSAF, the SAS server makes the connections for each client and the connections have the client z/OS authorization identifier associated with them. This is true only for clients that the SAS server authenticated, which occurred when the client specified a user ID and password. Servers authenticate their clients when the clients provide their user IDs and passwords. Generally, this is the default way that servers are run. If a client connects to a SAS server without providing a user ID and password, the identifier associated with its connections is that of the server (as with CAF) and not the client’s identifier.

Other than specifying DB2RRS at SAS start-up, you do not need to do anything else to use RSSAF. the DB2 interface automatically signs on each connection that it makes to DB2 with the identifier of either the authenticated client or of the SAS server for non-authenticated clients. 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.

For many types of SAS procedures, the DB2 interface must access DB2 system catalogs for information. This information is limited to a list of all tables for a specific authorization identifier. The interface generates this SQL query to obtain information from system catalogs:

SELECT NAME FROM SYSIBM.SYSTABLES
  WHERE (CREATOR = 'authid');

Unless you specify the AUTHID= option, the authorization ID is the z/OS user ID that is associated with the job step.

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 fails.

Because querying the DB2 system catalogs can cause some locking contentions, SAS/ACCESS Interface to DB2 under z/OS initiates a separate connection for the query to the DB2 system catalogs. After the query completes, a COMMIT WORK command is executed.

Under certain circumstances, you can access a catalog file by overriding the default value for the DB2CATALOG= System Option.