HP OpenVMS Systems Documentation

HP COBOL
DBMS Database Programming Manual

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

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.

    .
    .
    .
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.

    .
    .
    .
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.