2

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?

Seriously
  • 155
  • 5
  • With duplicates, do you mean identical rows? Is your goal to remove all but one of the "duplicates"? – Lennart - Slava Ukraini Nov 28 '22 at 10:38
  • if table dosen't have any keys, i'm fetching all the records and i want to modify say 1 specific record, so i need some unique identifier like rowid(ROWID pseudocolumn returns the address of the row) in oracle. – Seriously Nov 28 '22 at 11:54
  • Use row_number() over (partition by ... order by ...) as mynum – Lennart - Slava Ukraini Nov 28 '22 at 11:55
  • row_number() won't always work unless what you put in the order by makes it unique. And if there are columns already in the record that uniquely identify a row, it would be much better to just use them vs. row_number() – jmarkmurphy Dec 05 '22 at 14:33

3 Answers3

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.

PoC
  • 521
  • 3
  • 13
  • I'm connecting to AS400 db using jt400.jar or db2jcc4.jar from java project. and I found one link "https://www.ibm.com/docs/sr/db2-for-zos/11?topic=types-row-id-values". AS400 has integrated the DB2 database management system(https://www.go4as400.com/chapterload.aspx?cid=1). so if db2 has rowid then as400 also has rowid concept – Seriously Nov 28 '22 at 11:45
  • as mentioned use RRN, it is a function. select *, rrn(myTable) as theRRN from myTable. Use theRRN value to do the update. – Scott Mildenberger Nov 28 '22 at 13:58
  • 1
    @Seriously RRN represents physical row id, which may change over time. It definitely changes, when a table is reorganized, and may be reused depending on a table option `REUSEDLT (*YES)`. So, you shouldn't rely on RNN as "primary key" in the most of cases. You should come to Db2 DBA and say, that you can't replicate table data without some unique row identifier, unless your replication mode is full refresh during each replication cycle. – Mark Barinstein Nov 28 '22 at 17:48
0

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

Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32
  • This is not guaranteed to work. In fact it will work sometimes, or maybe even most of the time, but when something in the data changes, and the optimizer decides a different access is better, row_number() could return the rows in a different order unless a,b uniquely identifies the record. This intermittent failure will be extremely hard to debug. – jmarkmurphy Dec 05 '22 at 14:36
  • I think I clearly indicated that with "...but you need something that uniquely determines each row if you want it to be deterministic". – Lennart - Slava Ukraini Dec 05 '22 at 14:42
0

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.

Charles
  • 21,637
  • 1
  • 20
  • 44
  • I got your point, but I don't have write access in db(I cannot modify or add new columns) as this is a production DB, I can only read the data. – Seriously Nov 30 '22 at 06:44
  • @Seriously find someone who does. Or abandon the effort as not possible. Unless you want to be blamed when the "publish to kafka" breaks. – Charles Nov 30 '22 at 14:34