How to find indicator and relative record number fields in DB2/400

130 pts.
Physical File
Relative Record Number
In DB2/400, even after we delete record from file, the record exists until we are done with RGZPFM. The deleted record can be identified by a indication field with value "Y" or "N". How to see this indicator field? How to see the DB2/400 RRN (Relative record number) field? I tried with RUNQRY command, but I am not able to find both the fields?

Answer Wiki

Thanks. We'll let you know when a new response is added.


You can access the Record Number in SQL by referring to RRN. As far as I know the RRN is not supported in Query.

Deleted records are not accessible through SQL or Query. Although the space used by deleted records is not released until RGZPFM is done or the file has REUSEDLT(*YES) specified, the records are no longer existant in the file. They can only be recovered from a database journal.


Martin Gilbert.


It is true that you cannot access deleted records using SQL or Query. The record is however still in the database even after you have deleted it. There is a hidden field (not visible to users) which indicates if the record is logically deleted. The value however is not Y or N.

You can deduce the RRN of a deleted record by sequentially reading the file (not by key), looking to see what RRN each record represents, and noticing gaps (RRN 5 follows RRN 3, RRN 4 has been deleted).

Not all data associated with the deleted record may exist. If, for instance, the record contained a variable length field and data had overflowed the base allocation size then the overflowed data may indeed be gone.

Bruce Vining

Discuss This Question: 6  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Srinis
    Hey guys... Karthik here.. adding to the above topic, how to retrieve deleted records using RRN value in SQL and not by using any other tools? My manager told, its possible to retrieve deleted records using SQL and he gave a clue that with RRN we can do... does anyone know how to retrieve deleted records?? using sql?? kindly share ur knowledge guys!! Thanks in advance..
    30 pointsBadges:
  • bvining
    Your manager is mistaken. It is possible to retrieve some parts of a deleted record, but SQL wouldn't have a clue as to the underlying data layout as we are not talking externally described files/tables that you access. You need to parse through an object save of the underlying database file, a task I would normally not associate with SQL processing.
    7,070 pointsBadges:
  • Yorkshireman
    Way back when.. when magers were boys. It was common to indicate a 'deleted' record by setting a field within the record to some value, and logically excluding records with that value from being used. I suggest you invute your manager to update himself or herself. If you think records may be deleted in error, get better users, or build in journal recovery, or anything else. As they say in the shops - when it's gone, it's gone. We're not talking Mickey mouse^^^^^^ soft 'are you sure' stuff - You either delete a record or you don't
    6,085 pointsBadges:
  • Sloopy
    There are utilities to recover system-deleted records. I do have one, which saves the physical file in a SAVE FILE (SAVF). A program then reads the save file and retrieves the records from the physical file. The deleted records are identified by a byte which is normally invisible, but which now is accessible. The internals of a save file can change between operating system releases, which means that my version of this utility now no longer works (I've just checked!) - I'll have to check on the net to see if I can find a new version, and I suggest that you should look there too. There ARE other methods, but those can be stymied by the system authority level. Sloopy
    2,195 pointsBadges:
  • graybeard52
    Its not SQL, but there is a utility called RDR that is part of the DBU suite. I think the WRKDBF shareware program also had a recover deleted records. In any case, there are limits to what you can recover. Generally the sooner you try, the better chance of recovery. Other than journals, the other possibility would be using SQL against a backup copy and inserting missing records.
    3,115 pointsBadges:
  • ToddN2000
    I found a utility that was able to recover deleted records from a database file. The utility was UNDEL2. It will work as long as you do not have REUSEDLT(*YES). Other than that, you cannot see or access the deleted records by normal means.
    135,525 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: