NCL File Processing › Work with UDBs › Retrieve Records from a UDB

Retrieve Records from a UDB

The &FILE GET statement is used to retrieve records from a UDB. A number of techniques can be used:

• Retrieval by specific key-the full key of the required record is supplied. Only a record with this key will be returned.
• Retrieval by generic key-a partial key is supplied. Several values can then be used on the OPT= operand to determine the first record retrieved and the direction in which processing will continue: KEQ, KGE and KGT return the first record with a key equal to, equal to or greater than, or greater than the partial key (respectively), and set the retrieval direction to forwards, while KEL, KLE and KLT return the highest record with a key equal to, equal to or less than, or less than the partial key (respectively), and set the direction to backwards.
• Sequential retrieval-no key is supplied. Records can be returned in ascending or descending key order, commencing with the lowest key or highest key in the file respectively, and continuing forwards or backwards through the file until sequential retrieval is terminated.

Before a record can be retrieved (except for sequential retrieval) a key or partial key must be identified. This is done using the KEY= or KEYVAR= operand on the &FILE GET statement. It is possible to identify the key prior to the &FILE GET (for example, for sequential retrieval) by specifying the KEY= or KEYVAR= operand on a &FILE SET statement.

The success of the &FILE GET statement can be tested using the &FILERC system variable. If the &FILE GET statement was not successful the &VSAMFDBK system variable contains a standard VSAM completion code indicating the type of error that occurred. &FILERC signals end-of-file conditions as well as error conditions.

If generic retrieval or sequential retrieval is performed, the full key of the retrieved record is placed in the &FILEKEY system variable. For example:

&FILE OPEN ID=MYFILE FORMAT=DELIMITED
&FILE SET KEY='REC'
.NEXTREC
   &FILE GET OPT=KEQ VARS=A*
   &IF &FILERC EQ 4 &THEN &ENDAFTER &WRITE DATA=END-OF-FILE
   &ELSE &IF &FILERC NE 0 &THEN &ENDAFTER &WRITE +
      DATA=ERROR=&VSAMFDBK
   &WRITE DATA=THE RECORD KEY RETURNED = &FILEKEY
&GOTO .NEXTREC

Continuation of a generic retrieval depends on the next &FILE statement. Any statement other than another &FILE GET, destroys the current file position, and ends the generic retrieval. If an &FILE GET statement is issued with a different key from that issued on the first generic &FILE GET, the generic retrieval ends, otherwise the key used on the statement is ignored, and retrieval is determined by the current file position.

When a generic read is performed (for example, when using the OPT=KEQ or OPT=KGE operand on &FILE GET), NCL maintains a generic environment until the NCL procedure specifically stops using generic retrieval, for example by issuing &FILE GET OPT=END. The maintenance of this generic environment holds the current position within the UDB, but in doing so necessarily uses VSAM buffer space. In procedures such as EASINET where there can be much concurrent UDB activity, it is important to keep the length of time that generic environments are open to a minimum and to avoid the use of generic processing where it is not necessary-for example, if &FILEKEY has been set to the full key of a record, do not use &FILE GET OPT=KEQ to read the record.