Yes – RRN is Unique
No – RRN is not unique
(The answer is YES it’s unique while it’s in use… but also a RRN can be RE-USED for a new table Row)
IBM i Tables (aka AS400 files) have a unique number assigned to every row in the table. This number is called a Relative Record Number, or RRN. When a row is deleted the RRN is marked as deleted – that RRN may or may-not be used again depending on a file setting.Me. Waffling about file gubbins in a meeting ten minutes ago.
When a row is deleted – the data is kept on disk and the RRN is marked as deleted.
Reading through the table high level languages (like RPGLE, SQL, Cobol, etc) do not see rows marked with deleted RRN. Programs only see the LIVE rows. If we want to recover, or undelete these rows, we can run utilities to UNDELETE these rows by RRN.
So, in summary:
- RRN (relative record number) does not have max value and does not run out
- RRN is assigned to row when it is added to a table.
- RRN identifies that particular row in the table.
- Within any table the RRN is unique (you cannot have two row#1 in the same table)
RRN and DEFRAG/RGZPFM
RRN is regenerated at anytime if the table is reorganized (like a defrag). This will compress out any deletes and reset all RRN in the table from 1 upwards.
If we never reorg a file it can show a large size because of large number of *deleted rows*.
Table Reorgs need exclusive lock – so an alternative technique to free up this disk storage is to use the table level ReUseDlt setting:
- If a table REUSEDLT(*YES) is specified — RRN will be reassigned when a row is deleted and a new row is written – the new one will go into a vacant RRN of a previously deleted row. With REUSEDLT(*YES) we can never rely on undeleting rows.
- If REUSEDLT(*NO) is specified — New rows will be added to the end of the file with new RRN. Old RRN of deleted rows will forever be allocated on disk, only released if table is reorganized (which will release the storage and renumber all table RRN’s as mentioned above)
So – to answer the question of “Is RRN Unique?” we really have 2 answers:
YES RRN CAN BE UNIQUE – if we do not ever reorg our files, we do not care that tons of disk may be used to store these deleted records, we do not care about system performance from massive indexes including all these deleted rows
NO RRN IS NOT UNIQUE – if we reorg regularly or have the REUSEDLT(*YES) setting on our files.
Why am I waffling about the Relly Recno?
Because I’m in a seemingly unending series of meetings, with SQL Data Warehouse people who keep asking the same questions. Now I can just say google it 😉
RRN and SQL on IBM i
You can read the DB2 table RRN using the RRN(fieldname) function.
Here is an example of a flat file named BOB, with one column called BOB:
SELECT rrn(bob), bob FROM bob