DB2/400
DB2, DB2/400, DB2/400 RRN, Relative Record Number, Reorganize Physical File Member, RGZPFM, RRN, RUNQRY
Hello Guys,
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
November 2, 2008 11:37 AM
UPDATED:
July 22, 2009 1:02 PM
Answer Wiki:
Hi,
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.
Regards,
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
<a href="http://www.brucevining.com/">Bruce Vining Services</a>
To see all answers submitted to the Answer Wiki: View Answer History.
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..
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.
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
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
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.