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?
CodePudding user response：
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.
CodePudding user response：
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;