Retrieval Processing

Retrievals with a WHERE Clause or SORT Clause

Retrievals are done to view data records and also to establish a position for updates and deletions. The type of processing depends on whether you specify a WHERE clause and whether the SORT clause (if any) can be satisfied by simply traversing an index.

The CA-Datacom/DB set-at-a-time commands are used for a WHERE clause that can be translated into CA-Datacom/DB selection criteria. Those commands are also used for a SORT clause that cannot be satisfied by simply traversing an index. The SELFR command builds a Select File according to the WHERE clause, the SORT clause, or both clauses. Then the SELNR command moves left and right along the Select File, one position at a time. SELNR can also skip directly to the first or last record on the Select File.

The SELSM command skips directly to a specific record that is not adjacent to the current record and that is also not the first or last record on the Select File. Information in the internal record ID (RID) permits the SAS procedure to note any position in a file and return directly to it. The RELES command, issued before SELSM, drops the previous lock.

Retrievals with No WHERE Clause

If you do not specify any WHERE clause, the type of retrieval processing depends on the type of SORT clause (if any) and on the Default Key being used.

With no WHERE clause and a SORT clause (if any) that can be satisfied from a single index, the interface view engine uses CA-Datacom/DB record-at-a-time commands to retrieve data. If you specify a Default Key or let the Default Key default to the Native Key or request ordering that is represented by an index, the interface view engine traverses the respective index for that key.

Note: You can also manually set the Default Key to blanks. In this special situation, if you do not specify a WHERE clause or a SORT clause and the CA-Datacom/DB table is opened for retrieval only, the engine avoids accessing any index; it uses GSETP and GETPS commands to read the data area of the table sequentially in its physical sequence. This method is the fastest way to extract an entire table into a SAS file. GETPS and GSETP use look-ahead buffering by blocking records. Therefore, to avoid update contention, these commands are used only for retrieval.

When SAS procedures are doing only retrievals with no WHERE clause and the Native Key is the Default Key, the interface view engine uses GSETL and GETIT commands. These commands do look-ahead buffering by blocking records. For example, when you execute the PRINT procedure with no WHERE clause and the Default Key is the Native Key, the interface view engine completes the following steps:

opens the table's URT for sequential retrieval.
moves low values to the key value portion of the request area and issues the GSETL command for the Native Key. This command rewinds to the beginning of the table.
issues GETIT commands until it receives return code 19, which indicates end-of-file.

Note: If you set the Default Key to blanks, PROC PRINT uses GSETP and GETPS commands instead of GSETL and GETIT commands.

The next example shows how the FSEDIT procedure uses the RDUKG, RDUNX, RDUBR, and RDUKL commands to read and lock records. (These commands are the locking forms of REDKG and so on.) For the FSEDIT procedure, the interface view engine completes the following steps:

opens the table's URT for direct access with intent to update.
reads and locks the first data record by moving low values to the key value portion of the request area and then issuing an RDUKG command for the Default Key (usually the Native Key).
scrolls right with the RDUNX command (or left with the RDUBR command). These commands retrieve the next higher (or lower) entry in the index and lock the record, dropping the previous lock.

If you scroll off the beginning (left) or end (right) of the table, the interface view engine receives an end-of-file signal. In this situation, the engine retrieves the lowest or highest index entry, as indicated, and relocks the data record.

The Internal Record ID (RID)

Occasionally, the interface view engine also uses RDULE and REDLE (locking and not locking) commands, which provide direct addressability. Also, the LOCKG, LOCKL, and LOCNX commands are required when the engine must recover from a situation where another user has deleted a data record that the current user wants to view.

All retrieval commands return an internal record ID (RID). CA-Datacom/DB sends the RID to the interface view engine. A SAS procedure can request the RID from the engine even though it is internal and not a row number. The engine uses most of the request area for the RID (approximately 256 bytes), not just the internal seven-byte record-ID. Commands such as REDLE and RDULE can use the retained RID information in a rebuilt request area to reestablish the previous position in the index. Thus, after a record is retrieved, its RID can be used to retrieve the record again.

Here is how the FSVIEW procedure uses the RID. For a large table, PROC FSVIEW might need to display several screens of data. FSVIEW asks the engine for the RID for each data record that it retrieves and saves the RID. If FSVIEW needs to redisplay the window, it asks the engine to reposition using the specifically saved RID. Once the position is reestablished, FSVIEW can ask the engine to traverse forward or backward to retrieve the records that are needed to fill up the window again.

For example, suppose you are using the FSVIEW procedure to look at a CA-Datacom/DB table. If you issue the FORWARD command, the engine moves through the entire table and displays the last screen of data. For each new display, FSVIEW notes the RID of the record being displayed at the top of the screen.

If you issue the BACKWARD command to back up a screen, FSVIEW simply asks the engine to point to the RID of the previous screen, rather than reading the table backward sequentially. The engine issues a RELES command to drop the previous lock, and then issues an RDULE command and reads forward one record at a time until it has redisplayed that screen of data.

If the data record pointed to has been deleted (perhaps by another user), the REDLE/RDULE command fails. In this situation, FSVIEW asks the engine to go forward to the next undeleted data record. Since the engine has saved the RID of the deleted record, it can go forward even though the record itself was deleted.

The LOCKG command enables the interface view engine to position on the first index entry that has the proper key value. Then the engine can move forward with the LOCNX command until it receives an entry with the same key value but a higher internal record-ID or an entry with a higher value than the one requested. The LOCKL command skips backward from a deleted record if the SAS procedure requests it.