Unique identifier for AS400 Database if there is no primary key / unique key / composite key defined?

Question

Question: How to uniquely identify a record in AS400 database

Detailed Question:

I have AS400 database and some tables within it. Tables don't have primary key / unique key / composite key defined.

Like oracle has concept of ROWID, in same way do we have something for AS400 database. or can we create something like ROWID?

Answer 1

What you refer to as ROWID is called RRN (Relative Record Number) in DB2. I don't know about Oracle, but the RRN is only a pseudo-static value. Depending on the creation of the table, the value of existing records can change when records are deleted or inserted. The setting in question is the allowance to reuse deleted record space for new records.

Since you don't state how you access records (SQL, or Read/Write API Calls), you might want to search existing IBM documentation how to deal with the RRN value in your particular use case.

Answer 2

For LUW, you can modify a derived table, but you need something that uniquely determines each row if you want it to be deterministic. On the other hand, if the rows are identical, it probably does not matter which one you modify. Whether this work for i-series I don't know, but you can try the following:

create table t (a int not null, b int not null);
insert into t (a,b) values (1,1),(1,1),(1,1),(2,2),(3,3),(3,3);
select t.*, row_number() over (partition by a,b) as rn from t;
update (
  select t.*, row_number() over (partition by a,b) as rn from t
) set b = 9 where rn = 1;
delete from (
  select t.*, row_number() over (partition by a,b) as rn from t
) where rn > 1;
select t.*, row_number() over (partition by a,b) as rn from t;

Fiddle

Answer 3

First of all, are you sure there's no unique key? On the original AS/400, it was thought by some to be a "best practice" to leave your physical files (tables) unkeyed and to have the unique key defined in a logical file (aka view & index).

The Schemas component of the Access Client Solutions tool will allow you to see any indexes (and keyed logical files) associated with a table; including rather or not the key is unique.

If there's truly nothing unique, then the correct answer is to add a new PK column. This can be done quite easily and without requiring any native RPG/COBOL programs to be recompiled. The process basically involves moving the the data to a new table. And recreating the existing PF/table as a LF/view without the new column. The process is detailed in the IBM Redbook "Modernizing IBM i Applications from the Database up to the User Interface and Everything in Between"

The new column added could be a simple identity column, or an actual ROWID column. Db2 for IBM i does actually support a ROWID column, but it's relatively new and unlike Oracle's doesn't exist in a table unless you've added it or included it when you create the table.

The Relative Record Number (RRN), a simple integer showing physical row number, mentioned in the other answers might be a valid solution for your requirement to "fetching all the records and i want to modify say 1 specific record".

select rrn(a) as rowNbr, a.*
from mytable A;

update mytable A
set somefld = :newvalue
where rrn(a) = :rowNbr;

However, you mention in your other comment that you're "publishing the records in kafka pipeline". RRN is not a good solution there as it can change and/or be re-used.