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

snug cbd discount coupon 15% off

20% Off with Coupon: NICKLITTEN

I highly recommend the SNUG CBD Tincture to help keep you in the zone when programming!

In Partnership with SNUG CBD - American readers get 20% off

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

{"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!

>