I have an iSeries table with thousands of rows, and a new numeric/integer column was just added as the table Key. This will basically be a integer id column (1,2,3...).
Let's say that I can't make this column an auto-incrementing column. Is there an simple way to assign unique, incremental values to each row without having to loop through each record assigning a value? Perhaps some kind of UPDATE query?
You can use the RRN scalar function to assign the relative record number:
update table set id=rrn(table)
You could use a Sequence object. For example
CREATE SEQUENCE MySeq
as {numeric-datatype}
The data type can be SMALLINT, INTEGER, BIGINT, DECIMAL, or NUMERIC, with scale of zero (ie. no decimal places). A data area is created to store the value.
You can then use a NEXT VALUE FOR expression to retrieve and increment the Sequence, as in
UPDATE MyTable SET id = NEXT VALUE FOR MySeq
If you wish to know the last value assigned you can use the expression Previous value for mySeq
This method would not be as fast as RRN(), but may be useful in some cases, such as when you wish to have numbers that are unique across different tables.
Probably the easiest [and quickest] is, when adding the new column [that can not be an auto-increment column], to add the column as GENERATED ALWAYS AS IDENTITY [thus is an auto-increment column], and then ALTER TABLE to DROP IDENTITY attribute of that column, such that the column is no longer an auto-increment column. No need to create a separate SEQUENCE either. Of course, be sure to test first, with a duplicate of the TABLE that will be altered, and best to use a small sample of the data too in that copy [so the testing actually has to deal with data as well], and finally review the column attributes for things like DEFAULT and NULL preferences; i.e. the ADD COLUMN and/or ALTER COLUMN requirements may be different than the following given as merely a simplistic example [verified with DB2 for i5/OS on v5r3; presumed to be just as successful up through DB2 for IBM i 7.3]:
alter table has_no_id /* err, will have ID column, afterward */
add column id_added integer generated always as identity
;
alter table has_no_id /* err, will since, have no ID column */
alter column id_added drop identity
;
