Information for the Database Administrator

SAS/ACCESS Interface to ADABAS

How the SAS/ACCESS Interface to ADABAS Works

When you use the ACCESS procedure to create a SAS/ACCESS access descriptor file, SAS calls ADABAS to get a description of the ADABAS data. When you create a view descriptor file, SAS has information about the ADABAS data in the access descriptor, so it does not call ADABAS.

The ACCESS procedure writes the SAS/ACCESS descriptor files to a SAS library. Then, when you issue a SAS procedure using a view descriptor whose data is in an ADABAS file, the SAS Supervisor calls the interface view engine to access the data. The engine can access ADABAS data for reading, updating, inserting, and deleting.

When you edit either an access descriptor or a view descriptor, SAS does not call ADABAS.

ADABAS data records are uniquely identified by an Internal Sequence Number (ISN). As discussed in ADABAS Essentials, multiple SAS observations are generated from a single ADABAS record when the view descriptor contains periodic group fields. Creating multiple SAS observations does not preserve the unique quality of the ISN number (that is, more than one SAS observation can refer to a single ADABAS record). As a result, ADABAS records cannot be uniquely addressed by a single number within the SAS environment.(footnote 1)

In SAS terms, this means that an ADABAS record is not addressable by an observation number. Therefore, various SAS procedures behave differently when accessing ADABAS data than they do when accessing a SAS data file. For example, consider the following PRINT procedure and FSEDIT procedure behavior with ADABAS data:

The PRINT procedure issues messages informing you that observation numbers are not available and that the procedure has generated line numbers for its output. The numbers do not come from the ADABAS file.
The FSEDIT procedure does not display an observation number in the upper right corner of the window. If you try to enter a number on the command line, an error message is displayed.

How the ADABAS Interface View Engine Works

Making Calls

The ADABAS interface view engine is an applications program that retrieves and updates ADABAS data. Calls are in one of the following categories:

calls made on behalf of the ACCESS procedure when it is creating an ACCESS descriptor
calls made by a SAS DATA step or by SAS procedures that reference a view descriptor with the DATA= option.

In all situations, the interface view engine initiates and terminates communication between SAS and ADABAS. Each time a different SAS procedure requires use of ADABAS, the program makes an initialization call to the engine. This first call establishes communication with ADABAS. Additional calls to the engine perform retrieval and update operations required by the SAS procedure.

Calls Made on Behalf of the ACCESS Procedure

For both NATURAL DDMs and ADABAS files, the ACCESS procedure calls the interface view engine to retrieve data field information. The engine sends this information (such as, name, level number, data format, and definition options) to the ACCESS procedure for each ADABAS data field.

When you specify a DDM name, the interface view engine retrieves information from two places. First, the engine uses a combination of S1 and L1 commands to search and retrieve the DDM records that have been previously cataloged into a system file. The DDM records contain information for each field included in the DDM. Along with the field information, the engine also obtains the ADABAS file number and the database identifier on which the DDM is based. The ADABAS file number and database identifier are used in conjunction with the LF command to retrieve even more information directly from the Field Definition Table (FDT). The engine then combines the information retrieved from the DDM and the FDT to give a detailed description of each field.

When dealing directly with an ADABAS file, the engine uses only the LF command for retrieving field information from the FDT. The ACCESS procedure stores this information in the access descriptor file for later use when creating view descriptors.

If you use the ACCESS procedure to extract data and store it in a SAS data file, the ACCESS procedure calls the interface view engine to retrieve the actual data.

Calls Made by Other SAS Procedures

SAS procedures can access records in an ADABAS file by referring to a view descriptor with the DATA= option. SAS examines the view descriptor to determine which database management system is referred to and passes control to the appropriate engine. The interface view engine uses information stored in the view descriptor (such as name, level number, data format, and definition options) to process ADABAS data records as if they were observations in a SAS data file.

Before doing any retrievals, the engine processes the WHERE clause (if any) to select a subset of data records to be processed as observations. The engine inspects the view WHERE clause and the SAS WHERE clause (if any) and issues the ADABAS commands that are necessary to qualify the appropriate records. If no WHERE clause exists, all data records in the file qualify.

The interface view engine forms a SAS observation (according to the view descriptor), which it passes back to the calling procedure for processing.

Based on the capabilities of the SAS procedure, 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 reads with holds followed by the appropriate update command. Adds do not require a record to be read (except in special cases when you are dealing with ADABAS files that contain periodic group fields).

The SAS procedure then calls the engine again to retrieve another SAS observation. The engine locates another data record, constructs another SAS observation, and returns it to the SAS procedure. This cycle continues until the SAS procedure terminates or until the last qualified SAS observation has been constructed and returned to the SAS procedure.

Retrieval Processing

How the ADABAS Multifetch Feature Works

The SAS/ACCESS interface view engine uses the ADABAS multifetch feature when reading data records from ADABAS. When data records are requested from ADABAS, this feature buffers multiple data records and transfers the records to the engine. The data records are returned to the engine in the ADABAS record buffer with information about each record that is returned in the ISN buffer. Therefore, the number of records that are returned depends on the sizes of the record buffer and ISN buffer.

The read commands that use the multifetch feature in the engine are L1, L2, L3, and L9 commands (the engine does not use the feature when issuing L4 commands). In cases where there is a large number of periodic group occurrences selected in the view descriptor, the multifetch feature might not be used to read the data records from ADABAS.

The type of processing and the subset of ADABAS commands used by the interface view engine depends on the following conditions:

whether you specify a WHERE clause (view or SAS) and sorting criteria (view SORT clause, SAS BY statement, SAS ORDER BY clause) separately or in combination
whether the SAS procedure requires sequential or random access of the ADABAS records
whether the SAS procedure requests exclusive control of the ADABAS data, either automatically or manually (using the SAS software CNTLLEV=MEMBER data set option)

Retrievals with No WHERE Clause and No Sorting Criteria

If you do not specify a WHERE clause or any sorting criteria, the type of ADABAS commands used for retrievals is controlled by the type of access (either sequential or random) required by the SAS procedure. A SAS procedure requiring sequential access (for example, PROC PRINT) results in the engine issuing L2 commands to retrieve the records from the ADABAS file. Since there is no WHERE clause to subset the data, the engine retrieves every ADABAS record.

A SAS procedure requiring random access (for example, PROC FSEDIT) must have the ability to navigate both forward and backward. To support forward and backward navigation, an ISN list must exist. When a WHERE clause has not been entered, the engine generates a default WHERE clause. The engine searches for the first ADABAS descriptor data field in the view descriptor. Once the engine finds an ADABAS descriptor field, its format and length are used to construct a default WHERE clause. (If no ADABAS descriptors exist, the engine displays an error message.)

The ADABAS field formats and their corresponding default WHERE clause are listed below (assuming that the data field named AA is the first ADABAS descriptor field):

ADABAS Field Formats and Corresponding Default WHERE Clauses
Format	Default WHERE Clause
alphanumeric	where aa >= 'b'
binary	where (aa <= 0) or (aa > 0)
fixed point
floating point
packed decimal
unpacked decimal

The default WHERE clause results in the ADABAS interface view engine issuing S1 and S8 commands. Those commands generate an ISN list whose corresponding records are read using L1 or L4 commands. The engine uses L4 commands if the SAS procedure is capable of performing updates (that is, PROC FSEDIT). The engine uses L1 commands if the SAS procedure is not allowed to perform updates (that is, PROC FSBROWSE).

Note: A default WHERE clause can use considerable resources, depending on the number of ADABAS records. Therefore, for large amounts of ADABAS data, it is best to include either a view WHERE clause or a SAS WHERE clause. Also, the ADBDEFW systems option and ADBL3 data set option are available to alter the interface view engine's handling of the default WHERE clause. A default WHERE clause might also be issued for an ADABAS descriptor that has the NULL SUPPRESS option. That is, ADABAS records might exist that are not pointed to by the ISN list. [cautionend]

Retrievals with Only a WHERE Clause

If you specify a WHERE clause (either view or SAS), the engine typically issues S1, S8, and L1 or L4 commands to extract the appropriate ADABAS records. The only instance where this does not apply is when the L3 command is used. (This case is discussed later.)

If you specify both a view WHERE clause and a SAS WHERE clause, the two are combined using the Boolean AND operator, that is,

(SAS WHERE clause) AND (view WHERE clause)

Note: The only part of the SAS WHERE clause being logically combined is the part that ADABAS can support. See SAS WHERE Clause. [cautionend]

Combining the two WHERE clauses does not alter the set of commands used to retrieve the records. It does require the execution of an additional S8 command. The S1 and S8 commands generate an ISN list whose records are subsequently read using L1 or L4 commands.

Note: In SAS 9.1 and later, the ADABAS engine can issue an L1 command to ADABAS when an ISN is specified in a SAS WHERE clause. With this method, only one record is read instead of the complete table, resulting in a performance enhancement. [cautionend]

The L1 command is issued if an ISN is specified in a SAS WHERE clause and all of the following conditions are met:

the WHERE clause must be a SAS WHERE clause
no view descriptor SUBSET can be used
the WHERE clause can contain only a single condition
the operator must be EQ or =
sorting criteria cannot be specified
option ADBL3 must be set to NO (its default value)

To optimize WHERE clause processing, you can specify use of the L3 command with the SAS software ADBL3 data set option. The ADBL3 data set option also controls which commands are used if the L3 command cannot be used. A number of restrictions must be satisfied before the L3 command can be used.

The SAS procedure must have exclusive control of the view descriptor and therefore exclusive control of the underlying ADABAS data. This control is accomplished automatically by some procedures and manually by using the SAS software CNTLLEV=MEMBER data set option.
The SAS procedure must request sequential access.
Sorting criteria cannot be specified.
A WHERE clause can contain only a single condition.
The field referenced in the single condition must be an ADABAS descriptor field. (Phonetic descriptors and descriptors contained within or derived from a field within a periodic group cannot be used.)
The operator used in the single condition must be LT, LE, GT, GE, or SPANS.

The L3 command reads data records in logical sequential order based on the sequence of values for a given ADABAS descriptor field. The inverted list associated with the descriptor field controls the order in which the records are read. Unlike the S1 command that creates an ISN list, the L3 command uses an existing inverted list resulting in more optimal retrievals. The L3 command produces the most dramatic results for very large ADABAS files, or in ADABAS environments where ISN list buffer sizes are set comparatively low, or in system environments where disk space is a problem.

If the L3 command cannot be used, the ADBL3 data set option lets you specify the use of either the S1 or the S2 command to retrieve data records in its place. If the S2 command cannot be used, the engine returns an error.

Retrievals with Sorting Criteria

To sort data records, you can use only ADABAS descriptor fields since both ADABAS commands used for sorting rely on ADABAS descriptors. The S9 command requires an ISN list as input, and the L3 command uses an inverted list. This means that all ADABAS data fields referenced in a view descriptor SORT clause, a SAS BY statement, or a SAS ORDER BY clause must be associated with ADABAS descriptor fields.

As with the WHERE clause, certain sorting criteria can be optimized with the L3 command. However, the following conditions must apply before the L3 command can be used for sorting:

The SAS procedure must request sequential access.
Only one sort field is requested.
A WHERE clause cannot be specified.
The sorting sequence must be ascending.

You invoke the L3 command with the SAS software ADBL3 data set option. The L3 command reads data records in logical sequential order using the inverted list associated with the ADABAS descriptor field. The inverted list is maintained in ascending logical order.

If the ADBL3 data set option is not set, or it specifies use of the L3 command only and one of the above conditions is not met, the S9 command is used to satisfy the sorting criteria. The S9 command also imposes some limitations: a maximum of three descriptor fields can be used for sorting, and the ordering sequence (either ascending or descending) applies to every sort field. In all cases, the S9 command requires an ISN list as input. Since the ISN list is generated by WHERE clause processing, a default WHERE clause must be used if a WHERE clause is not specified. The S9 command generates a final ISN list in sorted order. L1 or L4 commands are used to read the ADABAS records represented in the final ISN list.

The S9 command can also sort the input ISN list in ascending ISN sequence. This is accomplished by supplying only the ordering verb ASCENDISN (no sort fields) in the view descriptor SORT clause.

Update Processing

Update processing involves updating, deleting, and adding data records. You must retrieve the data record before updating or deleting it.

Updating, deleting, and adding records is a straightforward process if there are no periodic group fields in the view descriptor or in the ADABAS data on which the view descriptor is based. In this case, the A1, E1, and N1 ADABAS commands are used for updating, deleting, and adding records, respectively.

If periodic groups do exist, adding new records and deleting existing records is more complicated. This is due to multiple SAS observations being generated from a single ADABAS record containing periodic group fields. The complexities of adding records containing periodic group fields is discussed in Adding an ADABAS Observation. Deleting records when the view descriptor or ADABAS data contains periodic group fields is discussed in Deleting an ADABAS Observation.

Competitive Updating and Logical Transaction Recovery

The interface view engine is an ET logic user application program. The ET (End Transaction) command and the record HOLD facility manage disaster recovery and multi-user concurrency issues.

SAS procedures capable of performing updates use the L4 command (read data record with hold) to read and hold data records. The held record is released with an ET command just before the next record is read. This means that any system or program failure recovers updates up to, but not necessarily including, the last ADABAS record read. When processing ADABAS data with periodic groups, remember that many SAS observations can represent one ADABAS record. Therefore, it is possible to have updated several SAS observations without issuing an ET command.

If an update procedure requests a record that another update procedure has locked, the read fails. The interface view engine recognizes this condition and re-issues the read without the HOLD option. The record is displayed with a message indicating that the record was unable to be locked and cannot be updated.

SAS procedures that do not have update authorization use the L1 command when reading records. The L1 command does not place the record in hold status, and subsequent ET commands are unnecessary.

FOOTNOTE 1: In combination, the SAS variables that contain the ISN and periodic group occurrence number uniquely identify an observation. The periodic group occurrence number variable is a fabricated SAS variable that does not have a corresponding field in the ADABAS file. It can be selected when creating a view descriptor and is valued with the occurrence number of each periodic group accessed.

Top of Page