SAS/ACCESS Software for Relational Databases: Reference

Data Set Options: Oracle Rdb Specifics

The SAS/ACCESS data set options for Oracle Rdb are as follows:

RDBLOCK=

RDBCONST=

Multiple-User Update Access to Oracle Rdb Tables (RDBLOCK=)

In Releases 6.08 and later of the SAS System, you can use the RDBLOCK= data set option, which allows multiple users to update data in a table concurrently. This SAS option can be used only with the FSEDIT procedure.

If you omit the RDBLOCK= option, the default value, RDBLOCK=NOCONCUR, is used. A table-level lock is put on the table, which means that only one user can update the table at a time.

Specify RDBLOCK=CONCUR for multiple-user, concurrent update access when you use a view descriptor that updates Oracle Rdb data. For example:

proc fsedit data=vlib.usacust(rdblock=concur);
run;

Note: This example references a previously created view descriptor named VLIB.CUSTOMER. [cautionend]

In this example, an update or deletion of a row locks that row (or record) until the commit is issued, but the rest of the Oracle Rdb table remains accessible to other users for additional updates or deletions. This is known as record-level locking.

If you specify a view descriptor in PROC FSEDIT and you are not allowed to update a row, Oracle Rdb writes one of the following error messages to the SAS log:

ERROR: Update failed. %RDB-E-LOCK_CONFLICT, request failed due to locked resource.
REASON: Another user has locked this row in order to update it.
ACTION: You can wait until the user releases the lock, or you can type CANCEL on the FSEDIT command line to cancel the update and advance to the next row.
ERROR: Update failed. %SQL-W-NOTFOUND, No rows were found for this statement.
REASON: Another user changed the values of the row after you read the values that are displayed on your screen. The WHERE expression that the interface view engine appended to the Oracle Rdb SQL UPDATE statement (to ensure the data integrity of your update) failed to find the appropriate rows to update.
ACTION: Type CANCEL on the FSEDIT command line to cancel the update. Refresh the row by scrolling forward to the next row, then scroll backward to the observation that you attempted to update.

Note: When RDBLOCK=NOCONCUR is in effect, updates, deletions, and insertions are committed after you type the FSEDIT SAVE command and after you end the FSEDIT procedure. When RDBLOCK=CONCUR is in effect, updates, deletions, and insertions are committed after you type FSEDIT SAVE, after you end the FSEDIT procedure, and whenever you scroll backward. [cautionend]

You can control the amount of time that the system waits for update access before it provides an error message. To do so, set the logical lock timeout interval before invoking SAS. For example, the following DCL command sets the lock timeout interval to 5 seconds:

$define rdm$bind_lock_timeout_interval 5

Evaluating Constraints at Commit Time (RDBCONST=)

The RDBCONST= data set option enables you to override the Oracle Rdb default action of evaluating constraints at commit time. This option has no effect on constraints that must be evaluated at commit time.

The RDBCONST= option corresponds to the execution of the following Oracle Rdb SQL statements:

SAS Option Value Oracle Rdb SQL Statement

RDBCONST=ON SET DEFAULT CONSTRAINTS ON

RDBCONST=OFF SET DEFAULT CONSTRAINTS OFF

The default setting is RDBCONST=OFF. This causes the constraint evaluation to be deferred until commit time, as in Releases 6.06 and 6.07 of the SAS/ACCESS Interface to Oracle Rdb.

SAS Option Value	Oracle Rdb SQL Statement
RDBCONST=ON	SET DEFAULT CONSTRAINTS ON
RDBCONST=OFF	SET DEFAULT CONSTRAINTS OFF

When you use the default RDBCONST=OFF, a commit might not occur until several rows of data have been entered. Be aware that if RDBCONST=OFF and you enter a NULL value for a column that has been defined as NOT NULL, you are not notified of your error until commit time. All of the rows that are part of the current commit batch are rejected, and you must re-enter or modify all of the rows of data that you have entered since the previous commit.

Setting RDBCONST=ON causes all of the affected constraints to be evaluated after each row (or observation) is entered or updated. If your data violates a NULL constraint, you are informed immediately, and only the current record is rejected.

When RDBCONST=ON is specified, the SET DEFAULT CONSTRAINTS ON statement is executed immediately after you are connected to the database. It is re-executed after each commit because the statement remains in effect for only one transaction.

The following example shows how to specify the RDBCONST= option in a view descriptor that is based on Oracle Rdb data.

proc fsedit data=adlib.allinv(rdbconst=on);
run;

Note: This example references a previously created view descriptor named VLIB.INVOICE. [cautionend]

Chapter Contents
Previous
Next
Top of Page