parquest
Member
Is there a way to get the row id in AS400 DB2 as in Oracle?
Dave Kahn...
Active Member
From V5R2 there is a ROWID data type that causes DB2 to generate a 40-byte unique identifier when a row is inserted.
If the table already exists and does not have a column with the ROWID data type you can retrieve the RRN (relative record number) of any table and use that.
E.g. select rrn (a) as rowid from MYTABLE a
You have to be a little careful with this method as it is possible for the RRN of a particular row to change if the table is reorganised.
If the table already exists and does not have a column with the ROWID data type you can retrieve the RRN (relative record number) of any table and use that.
E.g. select rrn (a) as rowid from MYTABLE a
You have to be a little careful with this method as it is possible for the RRN of a particular row to change if the table is reorganised.
G_Smith
Well Known Member
Dave,
It will always return the current Relative Record Number of the row (record) but it is as you state the table (file) may have been reorganized and the row (record) has changed.
Then the higher level programs (RPG/xxx and COBOL) can access the Information File Data Structure where after a Read or Chain the file relative record number is posted. Some JD Edwards programs will have a INFDS(xxxxx) keyword coded on the File Spec and then code a data structure sub-field at 397-400 Binary to hold the relative record number. Then that field can then be used as a direct access back to the record of a non-keyed file. Example would be to read a record in the F4211LA (LF) in key sequence and then use the relative record number to update the F4211 (PF) directly. I suppose the same thing could be done in SQL.
It will always return the current Relative Record Number of the row (record) but it is as you state the table (file) may have been reorganized and the row (record) has changed.
Then the higher level programs (RPG/xxx and COBOL) can access the Information File Data Structure where after a Read or Chain the file relative record number is posted. Some JD Edwards programs will have a INFDS(xxxxx) keyword coded on the File Spec and then code a data structure sub-field at 397-400 Binary to hold the relative record number. Then that field can then be used as a direct access back to the record of a non-keyed file. Example would be to read a record in the F4211LA (LF) in key sequence and then use the relative record number to update the F4211 (PF) directly. I suppose the same thing could be done in SQL.
Ian Simmons
Member
Probably too late for this thread, but "SELECT RRN(YOURTABLE) FROM YOURLIB/YOURTABLE WHERE..." will return the relative record number. This does, as a previous respondent has stated, change if the table is reorganised.
