The OWNER test condition does not test whether the current record owns any member records. Rather, this condition tests if the current record participates as an owner record. If a record type is declared as the owner of a set type, an OWNER test for that record type will always be true. Therefore, referring to Figure 6-2, if EMP4 is the object of an IF RESPONSIBLE_FOR OWNER test, the result is true because EMP4 is an owner record, even though the set occurrence is empty.

To test if an owner record owns any members, use the EMPTY test condition. For example:

IF RESPONSIBLE_FOR IS EMPTY PERFORM EMPTY-ROUTINE ELSE ...

Thus, if EMP4 is the object of an IF RESPONSIBLE_FOR IS EMPTY test, the result is true because the set occurrence has no members.

6.5 Modifying Members of Sorted Sets

If the schema defines a set's order to be SORTED and you modify any data items specified in the ORDER IS clause of the schema, the record may change position within the set occurrence. If the record does change position, the set's currency changes to point to the member record's new position.

Figure 6-3 shows a set occurrence for SORT_SET where MEMBER-B's key (KEY 3) was changed to KEY 8. Before altering the record's key, the set currency pointed to MEMBER-B, and a FETCH NEXT MEMBER WITHIN SORT_SET fetched MEMBER-C. However, the modification to MEMBER-B's key repositions the record within the set occurrence. Now, a FETCH NEXT MEMBER WITHIN SORT_SET fetches the MEMBER-D record.

Figure 6-3 Modifying Members of Sorted Sets

When you change the contents of a data item specified in the ORDER IS SORTED clause and you do not want the set's currency to change, use the RETAINING clause with the MODIFY statement. Thus, MODIFY repositions the record and RETAINING keeps the currency indicator pointing at the position vacated by the record. Figure 6-4 shows how the following example retains currency for SORT_SET.

FETCH NEXT WITHIN SORT_SET. IF MEMBER_KEY = "KEY 3" MOVE "KEY 8" TO MEMBER_KEY MODIFY MEMBER_KEY RETAINING SORT_SET.

Figure 6-4 After Modifying MEMBER_B and Using RETAINING

If MEMBER_B's key was changed to KEY 4, the record's position in the set occurrence would not change, and a FETCH NEXT WITHIN SORT_SET would fetch MEMBER_C.

6.6 CONNECT and DISCONNECT

When the set membership class is MANUAL, use the CONNECT statement to link a member record to its set occurrence. You can also use CONNECT for AUTOMATIC sets, provided that the retention class is OPTIONAL and you have disconnected the record.

When you use the CONNECT statement, specify the set or sets where the record is to be connected. Executing a CONNECT statement without the set list clause connects the record to all sets in which it can be, but is not yet, a member.

Before you execute a CONNECT statement, be sure that currency for the specified set type points to the correct set occurrence. If not, the member record will participate in the wrong set occurrence. (For more information on currency, see Section 5.13 and Section 5.14.) You cannot execute a CONNECT for a record that participates as an owner of the specified set.

If the set retention class is OPTIONAL, use the DISCONNECT statement to remove a member record from a specified set. The DISCONNECT statement does not delete a record from the database.

When you use the DISCONNECT statement, specify the sets from which the record will be disconnected. Executing a DISCONNECT without the set list clause disconnects the record from all the sets in which it participates as an optional member. You cannot execute a DISCONNECT for a record that participates as an owner of the specified set or that has a set retention class of FIXED or MANDATORY. Refer to the Section 4.7 for an explanation of how set membership class affects certain DML verbs.

6.7 RECONNECT

Use the RECONNECT statement to remove a member record from one set occurrence and connect it to another occurrence of the same set type, or to a different position within the same set. To transfer a member record:

1. Use the FETCH (or FIND) statement to select a record in the set occurrence. This can be either a member or an owner of the set occurrence you want to connect to.
2. Use the FETCH (or FIND) statement with the RETAINING clause to transfer the member record you want. This keeps the currency for the targeted record.
3. Execute a RECONNECT statement using the WITHIN clause.

The RECONNECT statement is useful in applications such as production control where manufactured items move down an assembly line from one work station to another. In Figure 6-5, work stations are the owner records and assemblies are the member records.

Figure 6-5 Occurrence Diagram Prior to RECONNECT

Example 6-3 transfers ASSEMBLY R, a machine base, to WORK STATION 2 for electrical assembly. The order of insertion is LAST.

Example 6-3 RECONNECT Statement

. . . GET-WORK-STATION. MOVE 2 TO WORK_STATION_ID. FIND FIRST WORK_STATION USING WORK_STATION_ID. MOVE "R" TO ASSEMBLY_ID. FIND FIRST ASSEMBLY USING ASSEMBLY_ID RETAINING ASSEMBLY_SET. ********************************************************* * The RETAINING clause retains work station 2 as * * current of ASSEMBLY_SET. Otherwise, the found member * * would be current of set and the RECONNECT would fail. * ********************************************************* RECONNECT ASSEMBLY WITHIN ASSEMBLY_SET. . . .

Figure 6-6 shows the ASSEMBLY_SET after execution of the RECONNECT statement. Notice the ASSEMBLY A record replaces the R record's position in the WORK STATION 1 set occurrence. Also, execution of the RECONNECT makes the ASSEMBLY R record current for the ASSEMBLY_SET.

Figure 6-6 Occurrence Diagram After RECONNECT

The ERASE statement deletes one or more records from the database. However, it can delete more than you intended. Accidental deletes can occur because of the ERASE statement's cascading effect. The cascading effect can happen whenever the erased record is the owner of a set. Thus, if the current record is an owner of a set type, an ERASE ALL deletes:

• The current record.
• All records in sets owned by the current record.
• Any records in sets owned by those members. Note that this is a repetitive process.

This is called a cascading delete.

The occurrence diagrams in Figure 6-7 show the results of using the ERASE ALL statement.

Figure 6-7 Results of an ERASE ALL

The ERASE ALL statement is the only way to erase an owner of sets with MANDATORY members.

6.9 ERASE Record-Name

If you do not use the ERASE ALL statement but use the ERASE record-name, and the erased record is the owner of a set, the ERASE statement deletes:

• The current record.
• All FIXED members of sets owned by the current record.
• All FIXED members of sets owned by records in rule 2. Note that this is a repetitive process.

If the current record owns sets with OPTIONAL members, these records are disconnected from the set, but remain in the database.

The occurrence diagrams in Figure 6-8 show the results of using the ERASE record-name statement when affected members have an OPTIONAL set membership. In this figure, B records are FIXED members of the SET_B set and C records are OPTIONAL members of the SET_C set. Notice that records C1 and C2 are disconnected from the set, but remain in the database while B1 through B3 are erased.

Figure 6-8 Results of an ERASE Record-Name (with Both OPTIONAL and FIXED Retention Classes)

Remember, records removed from a set but not deleted from the database can still be accessed.

6.10 Freeing Currency Indicators

Use the FREE database-key-id statement to release the currency indicators for realms, records, sets, or the run unit. You use the FREE statement: (1) to establish a known currency condition before executing a program routine, and (2) to release record locks.

6.10.1 Establishing a Known Currency Condition

Establishing a known currency condition is helpful in many situations---for example, if you have a program that performs a customer analysis and prints three reports. The first report prints all customers with a credit rating greater than $1,000, the second report prints all customers with a credit rating greater than $5,000, and the third report prints all customers with a credit rating greater than $10,000. Because some customers will appear on more than one report, you want each report routine to start its customer analysis with the first customer in the database.

By using the FREE CURRENT statement at the end of a report routine, as shown in Example 6-4, you null the currency and allow the next print routine to start its analysis at the first customer.

Example 6-4 FREE CURRENT Statement

. . . MAIN-ROUTINE. READY TEST_REALM CONCURRENT RETRIEVAL. PERFORM FIRST-REPORT-HEADINGS. PERFORM PRINT-FIRST-REPORT THRU PFR-EXIT UNTIL AT-END = "Y". MOVE "N" TO AT-END. PERFORM SECOND-REPORT-HEADINGS. PERFORM PRINT-SECOND-REPORT THRU PSR-EXIT UNTIL AT-END = "Y". MOVE "N" TO AT-END. PERFORM THIRD-REPORT-HEADINGS. PERFORM PRINT-THIRD-REPORT THRU PTR-EXIT UNTIL AT-END = "Y". MOVE "N" TO AT-END. . . . STOP RUN. PRINT-FIRST-REPORT. FETCH NEXT CUSTOMER_MASTER AT END FREE CURRENT MOVE "Y" TO AT-END. IF AT-END = "N" AND CUSTOMER_CREDIT_RATING IS GREATER THAN 1000 PERFORM PRINT-ROUTINE. PFR-EXIT. EXIT. PRINT-SECOND-REPORT. FETCH NEXT CUSTOMER_MASTER AT END FREE CURRENT MOVE "Y" TO AT-END. IF AT-END = "N" AND CUSTOMER_CREDIT_RATING IS GREATER THAN 5000 PERFORM PRINT-ROUTINE. PSR-EXIT. EXIT. PRINT-THIRD-REPORT. FETCH NEXT CUSTOMER_MASTER AT END MOVE "Y" TO AT-END. IF AT-END = "N" AND CUSTOMER_CREDIT_RATING IS GREATER THAN 10000 PERFORM PRINT-ROUTINE. PTR-EXIT. EXIT.

The FREE CURRENT statement in the PRINT-FIRST-REPORT paragraph nulls the default run-unit currency, thereby providing a starting point for the PRINT-SECOND-REPORT paragraph. The FREE CURRENT statement in the PRINT-SECOND-REPORT paragraph does the same for the PRINT-THIRD-REPORT paragraph. Thus, by nullifying the default run-unit currency, the FREE CURRENT statements allow the first execution of the FETCH NEXT CUSTOMER_MASTER statement to fetch the first customer master in TEST_REALM.

6.10.2 Releasing Record Locks

Regardless of the READY mode used, you always have a record lock on the current of run unit. Even the READY CONCURRENT RETRIEVAL mode locks the current record and puts it in a read-only condition. Furthermore, if you are traversing the database, the current record for each record type you touch with a DML statement is locked and placed in a read-only condition. Record locking prevents other users from updating any records locked by your run unit.

A locked record can prevent accessing of other records. Figure 6-9 shows PART A locked by run unit A. Assume PART A has been locked by a FETCH statement. If run unit B is in READY UPDATE mode and tries to: (1) update PART A, and (2) find all of PART A's member records and their vendor owners, then run unit B is locked out and placed in a wait state. A wait state occurs when a run unit cannot continue processing until another run unit completes its database transaction. Because run unit B uses PART A as an entry point for an update, the lock on PART A also prevents access to PART A's member records and the vendor owners of these member records.

Figure 6-9 Record Locking

If a record is not locked by a STORE or a MODIFY statement, or the database key for the record is not in a keeplist, you can unlock it by using the FREE CURRENT statement. By using the FREE CURRENT statement, you reduce lockout and optimize processing for other run units.

6.11 FIND and FETCH Statements

The FIND and FETCH statements locate a record in the database and make that record the current record of the run unit. The FETCH statement also copies the record to the user work area (UWA), thus giving you access to the record's data. The FIND does not place a record in the UWA. However, if your only requirement is to make a record current of run unit, use the more efficient FIND statement. For example, use the FIND statement if you want to connect, disconnect, or reconnect without examining a record's contents.

6.12 FIND ALL Option

The FIND ALL statement puts the database key values of one or more records into a keeplist. (See the description of FIND ALL in Section 4.9 for syntax details.)

The following example locates all PART records with a PART_STATUS of J and puts their dbkey values in keeplist TWO.

FIND ALL TWO PART USING PART_STATUS PART_STATUS X(l) = J

6.13 FIND NEXT and FETCH NEXT Loops

If you have a FIND NEXT or FETCH NEXT loop in your program, the first execution of the loop is the same as executing a FIND FIRST or FETCH FIRST. Unless you properly initialize them, currency indicators can affect selection of the specified record. For example, if ITEM B in Figure 6-10 is current for INV_ITEMS, a FIND NEXT INV_ITEMS makes ITEM C the current record for the run unit. You can null a currency by executing a FREE CURRENT statement.

Figure 6-10 Using FIND NEXT and FETCH NEXT Loops

Example 6-5 makes the INV_ITEMS currency null prior to executing a FETCH NEXT loop.

Example 6-5 FETCH NEXT Loop

. . . 000100 GET-WAREHOUSE. 000110 MOVE "A" TO WHSE-ID. 000120 FIND FIRST WHSE_REC USING WHSE-ID. 000130 UPDATE-ITEM. 000140 MOVE "B" TO ITEM-ID. 000150 FETCH FIRST WITHIN WAREHOUSE_SET 000160 USING ITEM-ID. **************************** * INVENTORY UPDATE ROUTINE * **************************** . . . ***************************************************** * The next statement nulls the run unit currency. * * Therefore, the first execution of the FETCH NEXT * * gets the first INV_ITEMS record. * ***************************************************** 000170 FREE CURRENT. 000180 ANALYZE-INVENTORY. 000190 FETCH NEXT INV_ITEMS 000200 AT END GO TO END-OF-PROGRAM. 000210 GO TO ANALYZE-INVENTORY. . . .

You can also use FETCH NEXT and FIND NEXT loops to walk through a set type. Assume you have to walk through the WAREHOUSE_SET and reduce the reorder point quantity by 10 percent for all items with a cost greater than $500. Furthermore, you also want to check the supplier's credit terms for each of these items. You could perform the task as shown in Example 6-6.

Example 6-6 Using a FETCH NEXT Loop to Walk Through a Set Type

. . . 000100 FETCH-WAREHOUSE. 000110 FETCH NEXT WHSE_REC 000120 AT END PERFORM END-OF-WAREHOUSE 000130 PERFORM WRAP-UP. 000140 ITEM-LOOP. 000150 FETCH NEXT INV_ITEM WITHIN WAREHOUSE_SET 000160 AT END 000170 FIND OWNER WITHIN WAREHOUSE_SET 000180 PERFORM FETCH-WAREHOUSE. 000190 IF INV_ITEM_COST IS GREATER THAN 500 000200 PERFORM SUPPLIER-ANALYSIS. 000210* Reduce reorder point quantity by 10%. 000220 MODIFY INV_ITEM. 000230 GO TO ITEM-LOOP. 000240 SUPPLIER-ANALYSIS. 000250 IF NOT SUPPLIER_SET MEMBER 000260 DISPLAY "NO SUPPLIER FOR THIS ITEM" 000270 EXIT. 000280 FETCH OWNER WITHIN SUPPLIER_SET. 000290* Check credit terms. . . .

Notice the FIND OWNER WITHIN WAREHOUSE_SET statement on line 000170. At the end of a WAREHOUSE_SET collection, statement 000170 sets the WAREHOUSE_SET type currency to the owner of the current occurrence. This allows the next execution of FETCH NEXT WHSE_REC to use current of record type WHSE_REC to find the next occurrence of WHSE_REC. Without statement 000170, a FETCH NEXT WHSE_REC would use the current of run unit, which is an INV_ITEM record type.

6.14 Qualifying FIND and FETCH

You can locate records by using the contents of data items as search arguments. You can use more than one qualifier as a search argument. For example, assume you want to print a report of all employees in department 5 with a pay rate of $7.50 per hour. You could use the department number as a search argument and use a conditional test to find all employees with a pay rate of $7.50. Or you could use both the department number and pay rate as search arguments, as follows:

. . . 000500 SETUP-QUALIFIES. 000510 MOVE 5 TO DEPARTMENT-NUMBER. 000520 MOVE 7.50 TO EMPLOYEE-RATE. 000530 FREE CURRENT. 000540 FETCH-EMPLOYEES. 000550 FETCH NEXT EMPLOYEE 000560 USING DEPARTMENT-NUMBER EMPLOYEE-RATE 000570 AT END GO TO EXIT-ROUTINE. 000580 PERFORM EMPLOYEE-PRINT. 000590 GO TO FETCH-EMPLOYEES. . . .

You can also locate records by using a WHERE clause to designate a conditional expression as a search argument. The following example fetches the first SUPPLY record whose SUP_LAG_TIME is 2 days or less.

000450 FETCH-SUPPLY. 000460 FETCH FIRST SUPPLY 000470 WITHIN PART_INFO 000480 WHERE SUP_LAG_TIME LESS THAN 2 000490 AT END GO TO EXIT-ROUTINE.