Deleting database records
The delete operation allows you to delete an existing database record. The DELET statement in the RPG language and the DELETE statement in the COBOL language are examples of this operation. To delete a database record, first read and lock the record. You lock the record by specifying the update option on any of the read operations listed under Reading database records using an arrival sequence access path or Reading database records using a keyed sequence access path. The rules for locking records for deletion and identifying which record to delete are the same as for update operations.
Some high-level languages do not require that you read the record first. These languages allow you to specify which record you want deleted on the delete statement. For example, the RPG language allows you to delete a record without first reading it.
When a database record is deleted, the physical record is marked as deleted. This is true even if the delete operation is done through a logical file. A deleted record cannot be read. The record is removed from all keyed sequence access paths that contain the record. The relative record number of the deleted record remains the same. All other relative record numbers within the physical file member do not change. The space used by the deleted record remains in the file, but it is not reused until:
- The Reorganize Physical File Member (RGZPFM) command is run to compress and free these spaces in the file member.
- Your program writes a record to the file by relative record number and the relative record number used is the same as that of the deleted record.
The system tries to reuse deleted record space automatically if the file has the reuse deleted record space attribute specified.
The system does not allow you to retrieve the data for a deleted record. You can, however, write a new record to the position (relative record number) associated with a deleted record. The write operation replaces the deleted record with a new record. See your high-level language topic collection for more details about how to write a record to a specific position (relative record number) in the file.
To write a record to the relative record number of a deleted record, that relative record number must exist in the physical file member. You can delete a record in the file using the delete operation in your high-level language. You can also delete records in your file using the Initialize Physical File Member (INZPFM) command. The INZPFM command can initialize the entire physical file member to deleted records.
If the file from which you are deleting has a delete trigger associated with it, the trigger program is called before or after deleting the record.
If the file is part of a referential constraint relationship, record deletion might be affected.
Parent topic:
Basic database file operations in programs
Related concepts
Reorganizing a physical file member
Reusing deleted records
Initializing data in a physical file member
Triggering automatic events in your database
Ensuring data integrity with referential constraints