.st0{fill:#FFFFFF;}

Is IBM i RRN Unique? 

 February 1, 2021

By  NickLitten

No – RRN is not unique

(unless we force it to be and don’t care if disk is wasted)

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 these deleted RRN *but* we can run utilities to UNDELETE these rows by RRN.

  • 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 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*.

RRN on IBM i AS400 and iSeries is the same

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 šŸ˜‰

NickLitten


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

  • 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.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Get In Touch

    Iā€™m always looking for awesome input, feedback and critique!

    >

    Snug CBD

     20% Discount

    I have partnered with SNUG CBD givING you Organic CBD
    20% discount code "NL20"