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

Delete Records from a UDB

The &FILE DEL statement is used to delete records from a UDB. Two techniques can be used:

• Deletion by specific key-the full key of the record to be deleted is supplied. Only the record with this key is deleted.
• Deletion by generic key-a partial key is supplied. Any record that matches this partial key (KEQALL) or is equal to or greater than this partial key (KGEALL) is deleted.

The KEY= or KEYVAR= operand can be used to specify the full or partial key to be used for the delete.

Deletion of a single record can be achieved as shown in the following example:

&FILE OPEN ID=MYFILE FORMAT=DELIMITED
&FILE DEL ID=MYFILE KEY='RECORD1'

If a partial key is provided for a single record delete, NCL deletes the first record on the file that matches the partial key.

If a group of records is to be deleted, a partial key that identifies the first record in the group must be specified on the KEY= or KEYVAR= operand. Then either of the following two values must be set for the OPT= operand.

KEQALL

Delete this record and all following records that have keys equal to the partial key provided.

KGEALL

Delete this record and all following records that have keys equal to or greater than the partial key provided.

The success of the &FILE DEL statement can be tested using the &FILERC system variable. If the &FILE DEL statement was not successful, the &VSAMFDBK system variable will contain a standard VSAM completion code indicating the type of error that occurred. For example:

&FILE OPEN ID=MYFILE FORMAT=DELIMITED
&FILE DEL ID=MYFILE KEY='RECORD' OPT=KEQALL
&IF &FILERC NE 0 &WRITE DATA=ERROR CODE=&VSAMFDBK

The number of records deleted by an &FILE DEL statement is returned in the system variable &FILERCNT, which can then be used, for example, as feedback information for display on a panel. That is:

&FILE OPEN ID=MYFILE FORMAT=DELIMITED
&FILE DEL ID=MYFILE KEY='RECORD' OPT=KGEALL
&WRITE &FILERCNT RECORDS DELETED

&FILERCNT remains until the next &FILE DEL, or the file is closed, or processing changes to a different file. In the last case, &FILERCNT changes to reflect the number of records deleted on the file that is now being processed.

Use of generic delete functions is of value when large numbers of records are to be deleted from a UDB and gives better performance than the use of multiple single record deletes.