You use the RETAINING clause to save a currency indicator you want to refer to. You use the RETAINING clause to: (1) navigate through the database and return to your original starting point, or (2) walk through a set type. (The expression "walk through a set type" implies a procedure where you access all owner records and their respective members.) Refer to the Section 4.9.1, Section 5.14.1, and Section 6.11 for further information.

After finding all members for an owner, the current of run unit is the last accessed member record occurrence in the set. If the next statement is a FIND NEXT for an owner, you may not retrieve the next owner. This is because:

• Current of set type (in this case, the last member record occurrence) is also current of run unit.
• Without a WITHIN clause, the FIND (or FETCH) is based on current of run unit.

Because DBCS uses currency status as pointers, a FIND NEXT VENDOR WITHIN MARKET uses current of MARKET realm to find the next owner record occurrence. To make sure a FIND (or FETCH) next owner statement finds the next logical owner record, use the RETAINING clause, as shown in Example 5-4.

Example 5-4 Using the RETAINING Clause

000100 PROCEDURE DIVISION. . . . 000400 100-VENDOR-SUPPLY-WALKTHRU. 000410 FETCH NEXT VENDOR WITHIN MARKET 000420 AT END GO TO 900-ALL-DONE. . . . ************************ * VENDOR PRINT ROUTINE * ************************ . . . 000500 300-GET-VENDORS-SUPPLY. 000510 FETCH NEXT SUPPLY WITHIN VENDOR_SUPPLY 000520 RETAINING REALM 000530 AT END 000540 GO TO 100-VENDOR-SUPPLY-WALKTHRU. . . . ************************ * SUPPLY PRINT ROUTINE * ************************ . . . 000550 GO TO 300-GET-VENDORS-SUPPLY.

Statement 000410 fetches the vendors. Statement 000510 fetches the supply records owned by their respective vendors. Statement 000510 also uses the RETAINING clause to save the realm currency.

A FETCH NEXT SUPPLY (statement 000510) without the RETAINING clause makes SUPPLY current for the run unit, its record type, all sets in which it participates, and its realm. When SUPPLY record 2-WEEKS in Figure 5-29 is current of run unit, a FETCH NEXT VENDOR statement fetches the vendor whose physical location in the database follows the 2-WEEKS record. As shown in Figure 5-29, MUSICO would be the next vendor and the program would be in an infinite loop.

Figure 5-29 Physical Representation of a Realm Without a RETAINING Clause

A FETCH NEXT SUPPLY with the RETAINING clause makes SUPPLY current for the run unit and the set types but keeps the vendor record current for the realm shown in Figure 5-30. By retaining the realm currency when you fetch supply records, the last accessed vendor record remains current of realm. A FETCH NEXT VENDOR WITHIN MARKET statement uses the realm currency pointer, which points to MUSICO to fetch the next vendor, SOUND-OFF. Therefore, retaining the realm currency allows you to fetch the next logical vendor record.

Figure 5-30 Physical Representation of a Realm with a RETAINING Clause

A keeplist is a stack of database key values (see the description of KEEPLIST in LD (Keeplist Description)). The KEEP and FIND ALL statements build a stack of keys that lets you retrieve Oracle CODASYL DBMS records using the ordinal position of the stack entries. Oracle CODASYL DBMS calls the table of entries a keeplist. Each execution of the KEEP or FIND ALL statement adds a record's database key (dbkey) value to the end of a keeplist and places a retrieval lock on the record. Therefore, other users cannot change a record while its database key is in your keeplist.

You can use a keeplist to retain the database key of a record after that record is no longer current. That is, by inserting a database key into a keeplist, you can continue to reference that record by specifying the keeplist name and database key value in your DML statement. This is especially useful when you want to remember a record during a long sequence of DML commands that affect currency, or when you want to remember a list of records.

A keeplist can contain zero, one, or several database key values. To activate a keeplist, use the KEEP statement. To empty a keeplist, use the FREE statement. All keeplists are deallocated when you execute a COMMIT or ROLLBACK unless COMMIT RETAINING is used.

The following example adds database keys to a keeplist.

000100 PROCEDURE DIVISION. . . . 000140 100-KEEPLIST-EXAMPLE. 000150 FETCH FIRST VENDOR. 000160 KEEP CURRENT USING KEEPLIST-1. 000170 FETCH FIRST SUPPLY WITHIN VENDOR_SUPPLY. 000180 FETCH OWNER WITHIN PART_INFO. 000190 IF PART_STATUS = "M" 000200 KEEP CURRENT WITHIN VENDOR_SUPPLY USING KEEPLIST-1.

Statement 000160 adds the vendor record's dbkey value (the current of run unit) to KEEPLIST-1. Figure 5-31 shows the contents of KEEPLIST-1 after execution of statement 000160. Adding a record's database key to a keeplist also prevents record updating by other concurrent users. Statements 000190 and 000200 add a supply record's database key to KEEPLIST-1 whenever its PART_INFO owner has a status of M. Figure 5-32 shows the contents of KEEPLIST-1 after the execution of statements 000190 and 000200.

Figure 5-31 State of KEEPLIST-1 After Executing Line 000160

Figure 5-32 State of KEEPLIST-1 After Executing Lines 000190 and 000200

You can use database key values as search arguments to locate database records. For example:

FIND 2 WITHIN KEEPLIST-1

This statement:

• Uses the value of the number 2 to locate the ordinal position of a database key value
• Uses the database key value to find a record

The KEEP statement can also transfer database key values from one keeplist to another. For example:

KEEP OFFSET 2 WITHIN KEEPLIST-1 USING KEEPLIST-2

This statement copies the second-positioned database key value in KEEPLIST-1 to the end of KEEPLIST-2.

The FREE statement removes database key value entries from a keeplist. For example:

FREE ALL FROM KEEPLIST-1

This statement removes all the entries from KEEPLIST-1.

You can remove keeplist entries by identifying their ordinal position within the keeplist. For example:

FREE 5 FROM KEEPLIST-2

This statement removes the fifth-positioned database key value from KEEPLIST-2. Removing a keeplist entry changes the position of all the following entries. For example, after freeing entry 5, entry 6 becomes the fifth-positioned entry, entry 7 becomes the sixth-positioned entry, and so forth. The FREE statement changes the ordinal position of a database key value in the keeplist, not its contents.

5.14.3 Transactions and Quiet Points

You generally segment your run unit into transactions, bounded instances of run-unit activity. A transaction begins with the first DML statement in the run unit or with a READY statement that follows a COMMIT or ROLLBACK statement; continues through a series of DML data access statements; and ends with either a COMMIT statement, a ROLLBACK statement, or the termination of the run unit. Before the initial READY statement is issued, after the COMMIT or ROLLBACK, and before the next READY, the run unit is at a quiet point. A quiet point is the time that exists between the last executed COMMIT or ROLLBACK statement and the next READY statement, or the time prior to the first executed READY statement.

The Quiet Point---Transaction---Quiet Point continuum provides the DBCS with a structure that allows it to control access to and ensure the integrity of your data. To implement this control, the DBCS uses currency indicators and locking. Figure 5-33 shows the segmentation of a run unit into transactions and quiet points.

Figure 5-33 Transactions and Quiet Points

We've gathered some tips and techniques you can use to improve program performance and reduce development and debugging time. These include special use of modes, indicators, conditions and statements, as well as debugging techniques.

6.1 The Ready Modes

Proper use of the READY usage modes can improve system performance.

You inform the DBCS of your record-locking requirements when you issue the READY command. The command takes the form:

READY <allow-mode> <access-mode>

or

READY <access-mode> <allow-mode>

The allow- and the access-mode arguments pass your requirements to the DBCS.

The allow-mode object of the READY command indicates what you will allow other run units to do while your run unit works with storage areas within the realm you readied. There are four different allow modes as follows:

CONCURRENT	Permits other run units to ready the same realm or realms that contain the same storage areas as the realms your run unit readied. CONCURRENT also allows other run units to perform any DML function on those storage areas, including updates.
PROTECTED	Permits other run units to use the same storage areas as your run unit, but does not allow those run units to update records in the storage areas.
EXCLUSIVE	Prohibits other run units from even reading records from the restricted storage areas.
BATCH	Allows concurrent run units to update the realm. BATCH also allows you to access or update any data in the realm while preventing concurrent run units from accessing or updating the realm.

While the allow mode says what your run unit will allow other run units to do, the access mode says that your run unit will either read or write records (RETRIEVAL or UPDATE).

Because the UPDATE access mode can lock out other users, use it only for applications that perform database updates. If an application accesses the database for inquiries only, use the RETRIEVAL access mode. The RETRIEVAL mode also prevents a run unit from accidentally updating the database.

The combination of the allow mode and the access mode is called the usage mode. There are eight READY usage modes as follows:

• CONCURRENT RETRIEVAL
• CONCURRENT UPDATE
• PROTECTED RETRIEVAL (the system default)
• PROTECTED UPDATE
• EXCLUSIVE RETRIEVAL
• EXCLUSIVE UPDATE
• BATCH RETRIEVAL
• BATCH UPDATE

Use the CONCURRENT usage modes for applications requiring separate run units to simultaneously access the database. They allow other run units to perform a READY statement on your realm, and possibly change or delete the database records in that realm.

Use the PROTECTED usage modes only when unrestricted access might produce incorrect or incomplete results. Protected access prevents other run units from making changes to the data in your realm. However, run units in RETRIEVAL mode can still access (read-only) your realm.

Use the EXCLUSIVE usage modes only when you want to lock out all other users. The EXCLUSIVE mode speeds processing for your run unit and prevents other run units from executing a READY statement on your realm. When you specify EXCLUSIVE access, use only the realms you need. Eliminating the use of unnecessary realms minimizes lockout. Use the EXCLUSIVE allow mode to get the best performance from a single run-unit application. Care must be taken, however, because other run units are locked out and must wait for the exclusive run unit to finish before it can begin operations.

Use the BATCH RETRIEVAL usage mode for concurrent run units to update the realm. Use the BATCH UPDATE usage mode to access or update any data in the realm while preventing concurrent run units from accessing or updating the realm.

For more information on READY usage mode conflicts, see the READY statement in the READY. It summarizes the effects of usage mode options on run units readying the same realms.

6.1.1 Record Locking

Concurrent run units can reference realms that map to the same storage area; the same records can be requested by more than one transaction at the same time. If two different transactions were allowed to modify the same data, that data would be rendered invalid. Each modification to the original data would be made in ignorance of other modifications, and with unpredictable results. Oracle CODASYL DBMS preserves the integrity of data shared by multiple transactions. It also provides levels and degrees of record locking. You can control access to, or lock:

• All records in a realm you intend to access
• Individual records as they are retrieved by DML statements

You can also lock records totally or allow some retrieval functions.

Record locking begins with the execution of the first READY statement in the run unit. At that time the DBCS is told of your storage area locking requirements. If you specify EXCLUSIVE allow mode, no other run unit is allowed to access records in the specified realms. This is all the locking that the DBCS need do. If you specify CONCURRENT or PROTECTED modes, the DBCS initiates locking at the record level.

Individual records are locked as they are retrieved by the run unit. The degree of locking depends on the specific DML command used. For example, if your run unit executes a FETCH or FIND statement, the DBCS sets a read-only record lock, allowing other run units to read, but not update, the records. This lock is also set if your run unit assigns the database key associated with the record to a keeplist with the KEEP verb. (Note if you use FETCH or FIND FOR UPDATE, a no-read lock is placed on the specified record.)

As a record is retrieved, the lock is held at this level until there are no more currency indicators pointing to the record. If the program assigns a record to a keeplist, the lock is held by your run unit until it frees the record from the keeplist with a FREE statement. However, if a currency indicator points to a record whose database key is also in a keeplist, then a FREE statement to that keeplist entry still leaves the read-only lock active for that record. Similarly, if the same database key is in several keeplists, then freeing it from one keeplist does not release the other read-only locks.

However, the DBCS grants a no-read access lock if your run unit specifies a DML update verb, such as STORE, CONNECT, or MODIFY. Your run unit retains the lock on this record until the change is committed to the database by the DML COMMIT verb or the change is terminated or canceled by ROLLBACK.

The Run-Time System notifies the DBCS each time a run unit requests a locked record, thus keeping track of which records are locked and who is waiting for which records. This logging helps the DBCS determine whether a conflict exists, such as multiple run units requesting, but not being allowed, to access or change the same record. For more information on record locking, refer to the Oracle CODASYL DBMS documentation on database design and programming.

6.2 COMMIT and ROLLBACK

When you are in CONCURRENT UPDATE mode, any changes made to a record lock the record and prevent its access by other run units. For example, if a program updates 200 customer records in one transaction, the 200 customer records are unavailable to other run units. To minimize lockout, use the COMMIT statement as often as possible.

The COMMIT statement makes permanent all changes made to the database, frees all locks, and nulls all currencies. It also establishes a quiet point for your run unit.

The RETAINING clause can be used with the COMMIT statement. COMMIT RETAINING does not empty keeplists; retains all currency indicators; does not release realm locks; demotes no-read locks to read-only locks; then releases locks for all records except those in currency indicators or keeplists and makes visible any changes made to the database.

To use COMMIT properly, you need to know about application systems. For example, you might want to execute a COMMIT each time you accomplish a logical unit of work. Or, if you were updating groups of interdependent records like those in Figure 6-1, you would execute a COMMIT only after updating a record group.

Figure 6-1 Using the COMMIT Statement

The ROLLBACK statement cancels all changes made to the database since the last executed READY statement and returns the database to its condition at the last quiet point. The DBCS performs an automatic ROLLBACK if your run unit ends without executing a COMMIT or if it ends abnormally.

In Example 6-1 an order-processing application totals all items ordered by a customer. If the order amount exceeds the credit limit, the program executes a ROLLBACK and cancels the transaction updates. Notice that the credit limit is tested for each ordered item, thus avoiding printing of an entire invoice prior to cancelling the order.

Example 6-1 ROLLBACK Statement

. . . READY-UPDATE. READY TEST_REALM CONCURRENT UPDATE. ************************** * FETCH CUSTOMER ROUTINE * ************************** . . . ******************************* * FETCH ORDERED ITEMS ROUTINE * ******************************* . . . CREDIT-LIMIT-CHECK. MULTIPLY ORDERED-QUANTITY BY UNIT-PRICE GIVING ORDER-AMOUNT. ADD ORDER-AMOUNT TO TOTAL-AMT. IF TOTAL-AMT IS GREATER THAN CUST-CREDIT-LIMIT ROLLBACK PERFORM CREDIT-LIMIT-EXCEEDED ELSE PERFORM PRINT-INVOICE-LINE.

6.3 The Owner and Member Test Condition

The FIND OWNER statement finds the owner of the current of set type, which may not be the same as the current of run unit. Thus, executing a FIND OWNER WITHIN set-name when the current of run unit record is not connected to the specified set returns the owner of the member that is current of set type.

Figure 6-2 shows occurrences of the RESPONSIBLE_FOR set type where employees are responsible for the design of certain parts.

Figure 6-2 Occurrences of the RESPONSIBLE_FOR Set Type

Example 6-2 uses the data in Figure 6-2 to perform an analysis of PART D, PART L, and the work of the engineer responsible for each part. The set retention class is optional.

Example 6-2 Owner and Member Test Condition

. . . 000130 MAIL-LINE ROUTINE. 000140 MOVE "PART D" TO PART_DESC. 000150 PERFORM FIND-PARTS. 000160 MOVE "PART L" TO PART_DESC. 000170 PERFORM FIND-PARTS. 000180 GO TO ALL-FINISHED. 000190 FIND-PARTS. 000200 FIND FIRST PART USING PART_DESC. 000210 IF PART-IS-MISSING 000220 PERFORM PART-MISSING. 000230 PERFORM PARTS-ANALYSIS. 000240 FIND OWNER WITHIN RESPONSIBLE_FOR. 000250 PERFORM WORKLOAD-ANALYSIS. 000250 DONE-ANALYSIS. 000260 EXIT. . . .

When PART L becomes current of run unit, a FIND OWNER (statement 000240) finds PART D's owner, thus producing incorrect results. This is because a FIND OWNER WITHIN set-name uses the current of set type and PART L is not a member of any RESPONSIBLE_FOR set type occurrence. To prevent this error, statement 000240 should read:

IF RESPONSIBLE_FOR MEMBER FIND OWNER WITHIN RESPONSIBLE_FOR ELSE PERFORM PART-HAS-NO-OWNER.