Yes – RRN is Unique
(The answer is YES it’s unique while it’s in use… but it can be NO since 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
RGZPFM now has a Lock state (LOCK) parameter which can be *SHRUPD.
From the command help …
A shared update lock is acquired. Concurrent update, delete, and
insert access to the physical file member is allowed from another
job.
If *SHRUPD is chosen, the resulting order of the records may not
exactly match what was requested on the KEYFILE keyword. The
records will be reorganized to closely match the specified order,
but concurrent update, delete, and insert operations will cause some
records to not be reorganized. If the records must exactly match
the arrival sequence (*NONE) or a keyed file sequence, do not use
*SHRUPD.
At the end of the reorganize, an attempt is made to remove deleted
records at the end of the file and return storage to the system. If
*SHRUPD is specified, it is possible that a concurrent insert that
occurs just prior to removing the deleted records may prevent the
removal of some or all of the deleted records at the end of the
file. In this case, the FROMRCD keyword can be used on a subsequent
reorganize to attempt the removal of the deleted records without
reorganizing the the entire file again.
If *SHRUPD is specified, ALWCANCEL(*YES) must also be specified.
Interesting… so “an attempt is made to remove deleted records at the end of the file and return storage to the system” sounds like it just cleans up dead RRN rows from the end of the file, leaving some deleted RRN at the front of the file as they are — if file is in use by others *USRPRF at the time of reorg.