Topics for Database Administrators

SYSTEM 2000 and the SAS/ACCESS Interface

Understanding how the SAS/ACCESS interface to SYSTEM 2000 works can help a Database Administrator (DBA) decide how to use it.

When an access descriptor file is created by using the ACCESS procedure, SAS calls SYSTEM 2000 to get a description of the database. When a view descriptor file is created, SAS already has the information about the database in the access descriptor, so it does not call SYSTEM 2000.

PROC ACCESS writes the descriptor files to a SAS library. Then, when a SAS procedure is used with a view descriptor whose data is in a SYSTEM 2000 database, the SAS Supervisor calls the interface view engine to access the data. The engine can access a database for reading, updating, inserting, and deleting.

The connections between the SAS procedures and SYSTEM 2000 are shown in How SAS Connects to SYSTEM 2000 Software.

How SAS Connects to SYSTEM 2000 Software

[How the SAS System Connects to SYSTEM 2000 Software]

SYSTEM 2000 Interface View Engine

The SYSTEM 2000 interface view engine is a PLEX (Programming Language Extension) applications program that retrieves and updates data in a SYSTEM 2000 database. Calls to the engine are made when you use

the ACCESS procedure to create an access descriptor
the DBLOAD procedure and specify a view descriptor by using the VIEWDESC= option
the QUEST procedure to negotiate an execution environment
a SAS DATA step or SAS procedures and specify a view descriptor by using the DATA= option

In all instances, the same PLEX commands initiate and terminate communication between the interface view engine and SYSTEM 2000. Each time a different SAS procedure requires use of SYSTEM 2000, the procedure makes an initialization call to the engine. This first call establishes communication with SYSTEM 2000. Then, the interface view engine issues:

the START S2K command for a single-user or Multi-User environment, as specified by the calling SAS procedure.
an OPEN command for the specified database and then returns control to the procedure. Additional calls to the engine perform retrieval and update operations specified by the SAS procedure until the procedure is terminated.
a CLOSE command for the database that was opened.
the STOP S2K command when the entire SAS session terminates, or when you run the QUEST procedure in the same environment (single-user or Multi-User) that the SAS programs have been running in.

Using the ACCESS Procedure

The ACCESS procedure calls the interface view engine to retrieve item and record information for a specified database. The engine sends the component number, name, type, picture, level number, and key status (with the database cycle number, and so on) back to the procedure for each item and record in the database. PROC ACCESS stores this information in the access descriptor for later use when creating view descriptors. PROC ACCESS also calls the engine to extract information into a SAS data file.

Using the DBLOAD Procedure

When you create a new database, the DBLOAD procedure always creates an access descriptor and a view descriptor.

To insert data into an existing SYSTEM 2000 database, you must specify an appropriate view descriptor by using the VIEWDESC= option in the DBLOAD procedure. The view descriptor provides a mapping between the SAS variables that contain data to be inserted and the SYSTEM 2000 components that will insert the data into the database. It also contains the database name, password, and access mode to be used when you insert data.

For each observation that is retrieved from the data file specified in the DATA= option, a corresponding call is made to the interface view engine. The engine inserts the data into the database identified by the view descriptor. The engine uses only insert mode (one at a time) for inserting new descendant records into existing logical entries. Also, if the DATA= option identifies a SYSTEM 2000 view descriptor, the interface view engine is called to read that view.

When you load new logical entries (starting with records at level 0) into a database, you can specify that you want to use an optimized loading process. SYSTEM 2000 processes the new logical entries as one batch of inserts (PLEX load mode). The optimized load mode is faster than inserting records one at a time, however, it causes SYSTEM 2000 to attach the database for exclusive use, and no other database can be open in the same execution environment. Therefore, if your job is using optimized load, your input cannot also be a SYSTEM 2000 view descriptor of a database in the same environment.

Using the QUEST Procedure

Usually, the QUEST procedure communicates directly with SYSTEM 2000. When you enter SYSTEM 2000 statements (commands), they are processed immediately, and the results are sent back to you, interactively. However, there is one exception. Each time you execute the QUEST procedure, it calls the engine to request permission to execute. If no other SAS programs are using the engine in the same environment, permission is granted; otherwise, permission to execute is refused. Similarly, when the QUEST procedure terminates, it calls the engine to signal the event so that the engine will enable other SAS programs to execute. The engine does not enable SAS jobs to execute in the same environment while the QUEST procedure is running.

Using Other SAS Procedures

SAS procedures can access records in a SYSTEM 2000 database by specifying a view descriptor in the DATA= option. SAS examines the view descriptor to determine which database management system is specified and passes control to the appropriate engine. The interface view engine uses information stored in the view descriptor (for example, access mode, password, database name, component numbers, levels, types, and so on) to process SYSTEM 2000 data records as if they were observations in a SAS data file.

Before performing retrievals, the engine processes a PLEX dynamic where-clause (if specified) to select a subset of data records that should be processed as observations. The engine constructs the dynamic where-clause from the view where-clause and the WHERE clause in SAS (if any). If no view where-clause exists, a where-clause is constructed to locate all database records.

The dynamic where-clause processing returns a Locate File that contains the addresses of database records that satisfied the selection criteria. Based on those addresses, the engine issues a combination of GET S2KCOUNT, GET1, and GETA commands to read one or more database records. Then, the engine combines data from the records (according to the view descriptor) to form a SAS observation that it passes back to the calling procedure for processing.

Based on the capabilities of the SAS procedure that you are using, the next call to the engine might be a request to update or delete the SAS observation that was just retrieved. For updates, the engine issues MODIFY, INSERT, and REMOVE commands for one or more data records, based on how many records were used to construct the observation. Then, the SAS procedure calls the engine again to retrieve another SAS observation. The engine locates another group of records, constructs another SAS observation, and returns it to the SAS procedure. This cycle continues until the SAS procedure is terminated or until the last qualified SAS observation has been constructed and returned to the SAS procedure. The interface view engine also uses other commands, such as COMMIT, ROLLBACK, and CLEAR, to control processing.

Top of Page